Unique based on Field

[gguy]

I have a location table (address, city, state, zip and phone #) that has a couple thousand records and lots of duplication. The table was originally entered (without much thought) in approach so I am converting it to Access. I want to break this table down to unique records with no duplication.

When I create a query and turn on unique values it removes most of the dup records but not all. Specifically, with unique values on I get 1187 records and if I make my query to only look at the phone number field (which has no blank cells) I come up with 952 records. I think the 952 is right.

The problem is that when I make the query look at phone # then the result is only the phone # field. I would like to query out all the fields base on only on the phone # field as being unique.

figure it is something simple. Thanks in advance for any help. Greg

 

[Kiwiman]

Howzit

You can group the query by the pnone number and get the "First" or "Last" (there are other options in there) of the other fields depending on what you think will get the best result of the rest of the fields.

 

[gguy]

Not sure what you are getting at there but found some SQL code that did the trick.

The code is:

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

Replaced the code in a make table query with this code and it worked.

Thanks for the help. GG