I want to perform a UNION of two tables, but occasionally the primary key field in each will have the same value while the rest of the row contains different data. In this case I want only the row from table1:

table1

col1 col2
Frank blue
Debbie red
table2

col1 col2
George brown
Debbie orange
The result I want:

col1 col2
Frank blue
Debbie red
George brown

Something like:

SELECT * table 1
UNION
SELECT * table 2

Debbie what happens to (Debbie, orange) and (Debbie, red)? Do you want distinct or all?

When a name appears in both tables, I only want to return the row from table1.

thanks

Ok, that setup should work. It will select it's first record from table1.

No, it returns both (Debbie, red) and (Debbie, orange). UNION only removes duplicates that are identical in all fields.

I think I'm going to have to do a join for one side of the union.

Ok, that setup should work. It will select it's first record from table1.

Just create a sub query for the second part of the UNION query that excludes all records from table2 where there is a value in table1. You can use the Unmatched query wizard to set it up.