Return unique entries in access

[wevans]

Somehow I need to only return 1 record if there's two or more in the tables. I'm using this to create a mailing list labels so I don't want to print two or more labels for the same person. I want to find duplicate First and Last Name. Like Tim Wolf appears twice. I just need one label for him. But I need to print a label for Tina Wolf, and so on.

Here's my code so far, only reason I have code is cause access did it for me. I couldn't type this out myself. LOL

SELECT [Customer List].[Last Name], [Customer List].[First Name], [Customer List].Address, [Customer List].City, [Customer List].State, [Customer List].Zip, [Customer List].[Home #], [Customer List].[Work or Cell #], [Customer List].[PMA Cust], [Offsite Service Records].[PMA Cust]
FROM [Customer List] LEFT JOIN [Offsite Service Records] ON [Customer List].ID=[Offsite Service Records].ID
WHERE ((([Customer List].[PMA Cust]) Is Not Null)) OR ((([Offsite Service Records].[PMA Cust]) Is Not Null))
ORDER BY [Customer List].[Last Name];

I tried to type in "GROUP BY [Customer List].[Last Name]" but that didn't work. Then I tried to use SELECT DISTINCT and that didn't work either. Any help would be greatly appreciated.

[wevans]

I ended up creating another query based on that previous query. Then based the labels report on the second query. This did exactly what I wanted to do. If you still got a solution to do it with one query, I would still like to know how to do that. Thank you

[NeoPa]

You can do it one of two ways :
One way is to link your table with an INNER JOIN to a subquery which shows just the two fields (1st & last names) and uses the DISTINCT predicate.
The other way is to GROUP the table by those two fields (not just the one as you have it) but this means that you need to use Min() or some other aggregate function to select which value to return for each of the other fields.

There is actually a better way, a way the database systems were designed to use, and that would be to normalise your database. That way querying is much easier and this situation would never even arise.

I'll post a couple of links to reference and you let me know how you'd like to proceed.
Subqueries in SQL.
Normalisation and Table structures