Solved

Row numbers on duplicate records

Posted on 2007-11-19
2
286 Views
Last Modified: 2010-04-21
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
Comment
Question by:pxj05
2 Comments
 
LVL 8

Accepted Solution

by:
digital_thoughts earned 500 total points
ID: 20313331
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
 

Author Closing Comment

by:pxj05
ID: 31409929
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Error in WHERE Clause 5 49
Oracle SQL - Query help 7 55
SQL NULL vs Blank 26 36
SELECT query on two levels (detail and summary) 13 50
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question