How to eliminate duplicate records in a query?

[elainenguyen]

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!

[ADezii]

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...

[elainenguyen]

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!

[NeoPa]

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?

[NeoPa]

BTW Both experts (ADezii & Blyxx86) came up with the same answer but executed in different ways. They were both right. (Other Thread)

[blyxx86]

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. :)

[NeoPa]

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.

[MMcCarthy]

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.

SELECT Account#, First(Field1), First(Field2), ....etc.
FROM TableName
GROUP BY Account#;

This will return the first found value for the other fields.