Row numbers on duplicate records

Hi
I am using MS ACCESS 2003 on XP Pro and was hoping you could help me.

I have a query that displays duplicate records based on two fields. I would like to row number each record in each duplicate set. For example (field names first row):
F1, F2, F3
A, 2, Church
A, 2, Library
B, 1, Station
B, 1, <Null>
B, 1, Post Office
C, 5, Hall

I would like to add a field as follows:

F1, F2, F3, F4
A, 2, Church, 1
A, 2, Library, 2
B, 1, Station, 1
B, 1, <Null>, 2
B, 1, Post Office, 3
C, 5, Hall, 1

Ideally I would like the solution in SQL but if its best to use vba then so be it.

Thanks
pxj05Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
digital_thoughtsConnect With a Mentor Commented:
Getting a row number isn't possible in Access, but if you have a Auto Number field, you could use that. First get the duplicate data, then join back to your table on that data and that will get you all the duplicate records, where you can then reference the Auto Number field.
0
 
pxj05Author Commented:
I like it ... and then I could subtract the current autonumber value to give the row allocation 1, 2, 3, etc
thank you very much
0
All Courses

From novice to tech pro — start learning today.