By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,704 Members | 1,861 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,704 IT Pros & Developers. It's quick & easy.

Hiding duplicate records

P: 5
Hi. I'm a relatively new Access 2000 user, and I've got a problem thats got me stumped. I have a table of street addresses and property numbers that looks something like this:

Name, Location, PropertyNum
Ralph, 100 Anywhere St, 400010008
Bob, 102 Anywhere St, 400010009
Jessica, 103 Anywhere St, 400010010
Joe, 103 Anywhere St, 400010010
Marie, 103 Anywhere St, 400010010
Andrew, 110 Anywhere St, 400010011

Duplicate values exist in the PropertyNum and Location field, but not in the Name field. What I need to do is query the table to display the duplicate property numbers only once, in order to get a count, without permanently deleting them. So ideally, I would get a return of:

100 Anywhere St, 400010008
102 Anywhere St, 400010009
103 Anywhere St, 400010010
110 Anywhere St, 400010011

Since not all the duplicate records are exactly identical, I can't use the DISTINCT clause. The Find Duplicates wizard returns all the duplicates, but I need to remove all but one of them, as well as display the rest of the table. Is there any way of doing this?

Thanks for you time.
Aug 14 '07 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 296
Hi. I'm a relatively new Access 2000 user, and I've got a problem thats got me stumped. I have a table of street addresses and property numbers that looks something like this:

Name, Location, PropertyNum
Ralph, 100 Anywhere St, 400010008
Bob, 102 Anywhere St, 400010009
Jessica, 103 Anywhere St, 400010010
Joe, 103 Anywhere St, 400010010
Marie, 103 Anywhere St, 400010010
Andrew, 110 Anywhere St, 400010011

Duplicate values exist in the PropertyNum and Location field, but not in the Name field. What I need to do is query the table to display the duplicate property numbers only once, in order to get a count, without permanently deleting them. So ideally, I would get a return of:

100 Anywhere St, 400010008
102 Anywhere St, 400010009
103 Anywhere St, 400010010
110 Anywhere St, 400010011

Since not all the duplicate records are exactly identical, I can't use the DISTINCT clause. The Find Duplicates wizard returns all the duplicates, but I need to remove all but one of them, as well as display the rest of the table. Is there any way of doing this?

Thanks for you time.
use the max() function. In your SELECT clause try
max(Location) AS MaxofLocation
Aug 14 '07 #2

P: 5
Thanks a million! That worked perfectly.
Aug 14 '07 #3

Expert 100+
P: 296
Thanks a million! That worked perfectly.
I'm glad it worked for you!
Aug 14 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.