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!!
MRG_ALAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:


try this one

TRANSFORM Max(Table1.date) AS MaxOfdate
SELECT Table1.[student id]
FROM Table1
GROUP BY Table1.[student id], Table1.date
ORDER BY Table1.date
PIVOT Table1.event;
0
 
Patrick MatthewsCommented:
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

0
 
Patrick MatthewsCommented:
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

0
 
MRG_ALAuthor Commented:
That worked perfect. Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.