Query on unique

[Guest]

I am working with a table with 15000 records that has a lot of repeat SSNs in
it. I want to get the unique SSNs so I did a query with only the SSN as an
output field and set the Unique Value to Yes. This shows me the all the
unique SSNs. The problem is I also want the other field values that go with
each unique SSNs and if it was a repeated SSN then just give me whatever
(like the field values on the first record for the repeated SSNs)

Note: I cannot do a Unique Value on the full table because the person put
for example records in the table:
Fields: SSN / LastName / Firstname
Record 1: 111-11-1111 / Smith / David
Record 2: 111-11-1111 / Smith / Dave

and on and on for about 10000 unique SSNs that ended up with a table of
15000 records.

So in this case I want to return only one of these records. Either name
would be ok. But I need SSN / LastName / Firstname

Thanks for your help.
Steven

 

[Guest]

Hi Steven,

Try something like this. From you Unique SSN query.

SELECT DISTINCT SSN, DLookup("LastName", "TableName", "SSN='" & [SSN] & "'"),
DLookup("Firstname", "TableName", "SSN='" & [SSN] & "'")
From TableName;

Hope this helps.

 

[Guest]

JL you did it. Of course you already know that. I was trying to do the
DLookup because it is the only way I could think to logically do it but I
kept getting an error and then my mind started wandering to maybe there is a
different method.

The answer was you have to concatenate it properly.

Thank you very much for your help.

Steven

 

[John Spencer (MVP)]

A more efficient way would be to use an aggregate (or totals) query.

SELECT SSN,
First(LastName) as LName,
First(FirstName) as FName
FROM YourTableName
GROUP BY SSN

And if you wanted to know the number of duplicated SSN. You could add
COUNT(SSN) as Repeats
to you SELECT statement.