Solved

Row numbers on duplicate records

Posted on 2007-11-19
2
289 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Results to Excel File 18 74
SQL Syntax 24 53
Stored Proc - Rewrite 42 72
question about results where i dont have a match 3 36
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…

763 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