How can I return only unique records

[sumdumgai]

An Access database contains 1000's of records. One field is indexed, allowing duplicates. How can I set up a query within Excel to return only records with unique values in this indexed field. In other words, if there are many records with the same index, and there are many records with different indices, I only want to return one record for each different index that occurs. Thanks.

 

[mdlueck]

Add the DISTINCT keyword to your SELECT query.

http://office.microsoft.com/en-us/access-help/access-sql-select-clause-HA010276899.aspx

 

[sumdumgai]

Thanks very much.

 

[mdlueck]

You are welcome, sumdumgai.

 

[MSAccessRookie]

An Access database contains 1000's of records. One field is indexed, allowing duplicates. How can I set up a query within Excel to return only records with unique values in this indexed field. In other words, if there are many records with the same index, and there are many records with different indices, I only want to return one record for each different index that occurs. Thanks.

Did you mean all Unique values, or ONLY the records that do not have duplicates? Using DISTINCT will return one row for each value including any values that are duplicated.

If you use GROUP BY (theField) HAVING Count(*) = 1, you will get only the values that have single entries.

 

[XelaIrodavlas]

GROUP BY (theField) HAVING Count(*)

MSAccessRookie, could you please explain where you would put that statement in the SQL? I have a very similar problem to the above I tried just putting it at the end but got a message saying the LEVEL clause includes a reserved word/argument that is missing/mispelled or punctuated incorrectly... Gotta love those error statements, so helpful...

And yes, i did substitute 'theField' with the field i want to order it by, and the * with a 1

 

[MSAccessRookie]

MSAccessRookie, could you please explain where you would put that statement in the SQL? I have a very similar problem to the above I tried just putting it at the end but got a message saying the LEVEL clause includes a reserved word/argument that is missing/mispelled or punctuated incorrectly... Gotta love those error statements, so helpful...

And yes, i did substitute 'theField' with the field i want to order it by, and the * with a 1

I do not know the structure of your Query, but perhaps a look at the Query Structure for a GROUP BY Query would help.

SELECT Itemsyouwant, Count(*)
FROM YourTable
WHERE {Your Filter goes here}
GROUP BY Itemsyouwant
HAVING Count(*)=1