How do I merge two tables in Access while removing duplicates?

I have read through about every possible solution online, and I get a different result every time.

I have two tables: Clients and Patrons. They both have the same structure: LastName, FirstName, Address, City, State, and Zip. Clients has 108,000 entries while Patrons has only 42,000 entries. And some of those entries are duplicated between the two as I don't have 150,000 clients.

I need one coherent list. The problem I am running into is that some of my clients reside at the same address, so I can't simply remove duplicate addresses as that will remove a legitimate client. And I have some clients with very common names, say Jane Doe, where there are a couple of them at different addresses, so I can't just filter out duplicate last or first names.

I am using Microsoft Access 2010.

Simply turning unique values to YES isn't helping.

I have scoured the Microsoft help files, and I have gotten results of 2 to 168,000 and most everything in between.

How can I get a single list without duplicates without having to alphabetize it and go line by line for 150,000 entries??

Solution

A UNION query returns only distinct rows. (There is also UNION ALL, but that would include duplicate rows, so you don't want it here.)

Try this query. If it doesn't return what you want, please explain why if falls short.

SELECT LastName, FirstName, Address, City, State, Zip
FROM Clients
UNION
SELECT LastName, FirstName, Address, City, State, Zip
FROM Patrons
ORDER BY LastName, FirstName;

You probably want another field or fields in the ORDER BY. I just offered something to start with.