Help creating a crosstab query from a list

**bloocie** · 04-19-2012, 02:24 PM

Hi,

I have some data which is in 2 columns in the first column is a list of ID numbers and in the second column is the corresponding appointment date. If an individual had more than one appointment their ID number will appear more than once in the list with their subsequent appointment date. For example:

ID Appointment
1 1/1/12
2 1/2/12
3 12/1/12
4 14/1/12
1 3/1/12
3 2/2/12
5 5/1/12
6 3/3/12

6 4/3/12
7 31/1/12

I would like to convert this list into a crosstab so that I have the unique ID numbers as the row headers and column headers with the number of appointments: appointment 1, appointment 2 etc with the date of the appointment in the value field e.g.

ID Appointment 1 Appointment 2
1 1/1/12 3/1/12
2 1/2/12
3 12/1/12 2/2/12
4 14/1/12
5 5/1/12
6 3/3/12 4/3/12
7 31/1/12

Is there a way I can do this in Access? The data file is too large for excel. Thanks