Query for Unique Items in Field

[DeLaMartre]

I am an "Excel Guy". so please excuse this question if it seems elementary.

I have a table of data that I want to export to Excel, but first I want to query it so that it only extracts records which are unique (based on the values in one field only). More than that, however, I want to extract the first-record-only of those records which have duplicates.

For instance, if I have a record which has 3 dupes in this one field, I want to extract the first record only, (as well as any other records which do not have dupes).

Any help will be greatly appreciated!

 

[Jon K]

You will need a field in your table that can uniquely identify each record within each group of dupes. A convenient way is to add a RecordID field (an AutoNumber field) in your table if it does not have one.

Then create and run this query (type/paste in the SQL View of a new query, replacing with the correct table name and field names):-

SELECT *
FROM TableName AS a
WHERE RecordID = (Select Min(RecordID) from TableName where UniqueValuesField = a.UniqueValuesField);


Note that the RecordID field (the AutoNumber) is used here only as a convenient tool. If your table already has a field
1) that can uniquely identify the records within each group of dupes; and
2) the minimum value of which can be extracted using the Min() function

such as a Date field, that field can be used in the query in place of the RecordID field (the query will extract the earliest Dates).

Hope it helps.

 

[DeLaMartre]

Thanks very, very much for your reply, Jon! This has been most helpful!

Your time is certainly appreciated.



(Surf's Up!)