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

Question

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??

Answer 1

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.

Answer 2

One way to do this is to do a FULL OUTER JOIN and COALESCE the values. This would allow you to know if its in the client table, the patron table or both

Unfortunately AFAIK Access doesn't have FULL OUTER so you'll need to simulate it instead.

SELECT a.LastName, a.FirstName, a.Address, a.City, a.State, a.Zip , "Both" as type
FROM Clients a INNER JOIN Patrons b 
ON a.LastName = b.LastName 
   AND a.Address = b.Address
   AND a.City = b.City
   AND a.State = b.State
   AND a.Zip = b.Zip
UNION ALL
SELECT a.LastName, a.FirstName, a.Address, a.City, a.State, a.Zip , "Client" as type
FROM Clients a LEFT JOIN Patrons b 
ON a.LastName = b.LastName 
   AND a.Address = b.Address
   AND a.City = b.City
   AND a.State = b.State
   AND a.Zip = b.Zip
WHERE 
    b.PatronID is null (Or whatever the PK is)
UNION ALL
SELECT b.LastName, b.FirstName, b.Address, b.City, b.State, b.Zip , "Patron" as type
FROM Clients a RIGHT JOIN Patrons b 
ON a.LastName = b.LastName 
   AND a.Address = b.Address
   AND a.City = b.City
   AND a.State = b.State
   AND a.Zip = b.Zip
WHERE 
    a.ClientID is null (Or whatever the PK is)

If you just need a list though you should just use HansUp's answer