CODEWITHSUNDEEP
sqlms-access
Steb
Steb

Reputation: 1

Multiple rows, some have identical IDs, to separate columns

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

ID  | Name  | Major   | Email
----+-------+---------+-----------------------
005 | Bobby | English | [email protected]
006 | Sarah | Math    | [email protected]
006 | Sarah | Science | [email protected]
007 | James | Art     | [email protected]

and I would like for it to end up like this

ID  | Name  | Major 1 | Major 2 | Email
----+-------+---------+---------+----------------------
005 | Bobby | English | (blank) | [email protected]
006 | Sarah | Math    | Science | [email protected]
007 | James | Art     | (blank) | [email protected]

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 :(

Upvotes: 0

Views: 26

Answers (3)

Gustav
Gustav

Reputation: 55906

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

Upvotes: 0

stew
stew

Reputation: 31

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)

Upvotes: 0

shawnt00
shawnt00

Reputation: 17925

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

Upvotes: 2

Related Questions