Multiple rows, some have identical IDs, to separate columns

Question

Working in Access 2013. I have an Excel file laid out like this:

ID  | Name  | Major   | Email
----+-------+---------+-----------------------
005 | Bobby | English | coolEmail@email.com
006 | Sarah | Math    | email@email.com
006 | Sarah | Science | email@email.com
007 | James | Art     | badEmail@email.com

and I would like for it to end up like this

ID  | Name  | Major 1 | Major 2 | Email
----+-------+---------+---------+----------------------
005 | Bobby | English | (blank) | coolEmail@email.com
006 | Sarah | Math    | Science | email@email.com
007 | James | Art     | (blank) | badEmail@email.com

Anyone know how I would do that?

None of my searches on here have given me anything that works. I have tried this to no avail. I have no clue what I'm doing :(

For a database your first table is closer to how I'd expect it to be stored. It needs normalising, but it will be easier to extract data from that table. `SELECT COUNT(Major) WHERE Major = 'Science'` against `SELECT SUM(IIF(Major1='Science',1,0), IIF(Major2='Science',1,0))`. Then someone decides they want a third major - you'll need to change the structure of the whole database. — Darren Bartrup-Cook, Jun 28 '18 at 08:59

score 2 · Answer 1 · answered Jun 27 '18 at 23:21

Might be some minor Access syntax issues in there but I think this should work. You would basically need a "staging" source table or the ability to query Excel directly.

insert into <destination> (ID, Name "Major 1", "Major 2", Email)
select
    ID, min(Name), min(Major), iif(max(Major) <> min(Major), max(Major), null), min(Email)
from <source>
group by ID

score 0 · Answer 2 · answered Jun 28 '18 at 00:06

Just tested this query and it looks like it does what you are looking for. Replace TABLENAME with the name of the table you will be referencing in MS Access.

SELECT
    m1.ID
    ,m1.Name
    ,m1.major AS [Major 1]
    ,m2.major AS [Major 2]
    ,m1.Email

FROM
    TABLENAME AS m1
LEFT JOIN
    (
        SELECT
            *
        FROM
            (
                SELECT 
                    *
                    ,(
                        SELECT COUNT(*) 
                        FROM TABLENAME AS x 
                        WHERE x.major > y.major AND x.id = y.id
                    ) + 1 as rank_nbr 
                FROM 
                    TABLENAME AS y
            )
        WHERE
            rank_nbr = 2
    ) AS m2 ON m2.id = m1.id

WHERE
    m2.major IS NULL OR (m1.major IS NOT NULL AND m2.major <> m1.major)

Misunderstood initial question. shawnt00 has best answer. – stew Jun 28 '18 at 00:12 — stew, Jun 28 '18 at 00:12

score 0 · Answer 3 · answered Jun 28 '18 at 08:23

Assuming that you linked the Excel data as a linked table, try this:

Select
    ID,
    [Name],
    Min([Major]) As [Major 1],
    Max([Major]) As [Major 2],
    Email
From
    YourLinkedTable
Group By
    ID,
    [Name],
    Email

For displaying blanks, expand like this:

Select
    ID,
    [Name],
    Min([Major]) As [Major 1],
    IIf(Max([Major]) = Min([Major]), Null, Max([Major])) As [Major 2],
    Email
From
    YourLinkedTable
Group By
    ID,
    [Name],
    Email

Multiple rows, some have identical IDs, to separate columns

3 Answers3