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

How to eliminate duplicate records in a query?

P: 51
I am trying to generate a query from a table which has multiple dublicate records, but In my query, I only want the duplicate records to show once.
Any idea?
Thanks!
Dec 5 '06 #1
Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,669
I am trying to generate a query from a table which has multiple dublicate records, but In my query, I only want the duplicate records to show once.
Any idea?
Thanks!
'In the upper pane of the Query Design Window, right click, then select Properties. Change the Unique Values setting to Yes. A further explanation is given below. NOTE: A Query with Unique Values = Yes is not Updateable!

You can use the UniqueValues property when you want to omit records that contain duplicate data in the fields displayed in Datasheet view. For example, if a query's output includes more than one field, the combination of values from all fields must be unique for a given record to be included in the results.

Hope this helps...
Dec 5 '06 #2

P: 51
Thanks!
I've tried that but it didnt' work.
My problem is that:
I have a table contain 100 records. The primary key is an autonumber, so there are duplicate records with the same Account# which is what I want.
However, I am trying to generate a querry which only shows one record per Account# , but I have no idea. I've tried to do Delete duplicate record method but it deleted records in my table as well, and I don't want that.
Thanks!
Dec 5 '06 #3

NeoPa
Expert Mod 15k+
P: 31,660
Elaine,

There are two 'unique' options :
1. Unique Records
2. Unique Values
They are equivalent to the SQL Predicates DISTINCTROW & DISTINCT repectively.
What you probably need (as suggested by ADezii) is Unique Values.
For this to work you must only select fields to show which are duplicated.

Is that clear or do you need more explanation?
Dec 5 '06 #4

NeoPa
Expert Mod 15k+
P: 31,660
BTW Both experts (ADezii & Blyxx86) came up with the same answer but executed in different ways. They were both right. (Other Thread)
Dec 5 '06 #5

blyxx86
100+
P: 256
BTW Both experts (ADezii & Blyxx86) came up with the same answer but executed in different ways. They were both right. (Other Thread)
I got called an expert. :)
Dec 6 '06 #6

NeoPa
Expert Mod 15k+
P: 31,660
If it looks like one, and sounds like one, then I'm happy to call it one.
Answering members' questions (correctly ;)) puts you in that category.
Dec 6 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks!
I've tried that but it didnt' work.
My problem is that:
I have a table contain 100 records. The primary key is an autonumber, so there are duplicate records with the same Account# which is what I want.
However, I am trying to generate a querry which only shows one record per Account# , but I have no idea. I've tried to do Delete duplicate record method but it deleted records in my table as well, and I don't want that.
Thanks!
You can group your records by Account# but you will have to aggregate any other fields.

e.g.
Expand|Select|Wrap|Line Numbers
  1. SELECT Account#, First(Field1), First(Field2), ....etc.
  2. FROM TableName
  3. GROUP BY Account#;
  4.  
This will return the first found value for the other fields.
Dec 7 '06 #8

Post your reply

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