Create a sequence number for each record associated to a foreign key

[jpkeller55]

Hi,


I am hoping somebody can help me with this. I have data in a table that looks like the first table and I would like to write a query to update the sequence so it looks like the second table. Any ideas?

PersonID	Color	Sequence
1	Red
1	Blue
1	Green
2	Blue
3	Red
3	Blue

PersonID	Color	Sequence
1	Red	1
1	Blue	2
1	Green	3
2	Blue	1
3	Red	1
3	Blue	2

[Robeen]

Is there a specific reason you want the sequence in the table?
Access has built-in functionality that will allow you to do running counts over groups in a Report.

I don't know if it is possible to do running counts over groups in a query in Access.
You COULD write those values to the table using a recordset in VBA code.

[jpkeller55]

Thanks for the reply. I was actually needing the sequence so I could run a cross tab query with the sequence number as a column heading. I decided to take a different approach and just export the table into excel and use the function =if(A1=A2, B1+1, 1) and then imported the table back into access. That was faster than trying to figure out the correct VBA. Thanks again.