Solved

Row numbers on duplicate records

Posted on 2007-11-19
2
292 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Linux Academy Android App Now Supports Chromecast

We have some fantastic news for our Android fans. We’re so excited to announce that the Linux Academy Android app is now available with Chromecast support. That’s right – simply download the latest update of the Linux Academy App and start casting your favorite course videos!

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

627 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