asked on

Modifying Table data in Access

I am trying to take data that is the following format in an access table:
Student ID Event Date
123 JumpRope 1/1/2010
234 JumpRope 12/2/2010
234 JumpRope 12/2/2010
123 Swing 3/2/2010
234 Run 2/6/2010
567 Run 8/7/2010

And switch it to this format:

MemberID JumpRope Swing Run
123 1/1/2010
234 12/2/2010
123 3/2/2010
234 2/6/2010
567 8/7/2010

Any suggestions? I have a union query that does the exact opposite but am stuck on how to write the sql for the query to create the separate columns with data from the same field.

Thank you!!

Patrick Matthews

You can use a crosstab query for that:

TRANSFORM Max(SomeTable.Date) AS MaxOfDate
SELECT SomeTable.StudentID, Max(SomeTable.Date) AS [Total Of Date]
FROM SomeTable
GROUP BY SomeTable.StudentID
PIVOT SomeTable.Event;

Open in new window

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick Matthews

Small tweak to the crosstab above:

TRANSFORM Max(SomeTable.Date) AS MaxOfDate
SELECT SomeTable.StudentID
FROM SomeTable
GROUP BY SomeTable.StudentID
PIVOT SomeTable.Event;

Open in new window

That returns:

StudentID    JumpRope     Run         Swing   
----------------------------------------------
123          1/1/2010                 3/2/2010
234          12/2/2010    2/6/2010            
567                       8/7/2010

Open in new window

MRG_AL

ASKER

That worked perfect. Thank you