Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

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
0
pxj05
Asked:
pxj05
1 Solution
 
digital_thoughtsCommented:
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now