UNION query and keeping one version of duplicates

[Bamh1]

Hello,
I have two employee tables with similar structure imported in Ms Access. Table A contains permanent employee data, and Table B contractors. Some names appear in both tables. I want to make a union between these two tables such that if employee name appears in both tables, it will only keep the one in Table A. That is, if a record of permanent employee also exist as a contractor, the contractor record for that employee will be ignored in the union.

General query form:
Select * FROM TableA
UNION
Select * FROM TableB

Could someone please assist adding a condition, if statement or VBA to achieve the above.

Thank you,
Shawn

 

[Micron]

Try removing status field, then you should get unique records as long as you're not using UNION ALL.
Then create a new query, add the union query and permanent table to this new query and add status field. Hopefully that will produce the result you want.
If you need further help, posting data is better than pictures. With the amount of time I spend on various forums, I usually lack the desire to manually type out data for someone when they could post it.

 

[Bamh1]

Try removing status field, then you should get unique records as long as you're not using UNION ALL.
Then create a new query, add the union query and permanent table to this new query and add status field. Hopefully that will produce the result you want.
If you need further help, posting data is better than pictures. With the amount of time I spend on various forums, I usually lack the desire to manually type out data for someone when they could post it.

The

Hello,
I have two employee tables with similar structure imported in Ms Access. Table A contains permanent employee data, and Table B contractors. Some names appear in both tables. I want to make a union between these two tables such that if employee name appears in both tables, it will only keep the one in Table A. That is, if a record of permanent employee also exist as a contractor, the contractor record for that employee will be ignored in the union.

General query form:
Select * FROM TableA
UNION
Select * FROM TableB

Could someone please assist adding a condition, if statement or VBA to achieve the above.

Thank you,
Shawn

Try removing status field, then you should get unique records as long as you're not using UNION ALL.
Then create a new query, add the union query and permanent table to this new query and add status field. Hopefully that will produce the result you want.
If you need further help, posting data is better than pictures. With the amount of time I spend on various forums, I usually lack the desire to manually type out data for someone when they could post it.

The status field can't be removed; it's a required field. I am trying to upload data files, but for some reason this thread only allows me to upload image.

 

[Bamh1]

The union result should look like the third image

 

[Micron]

Copy a range and paste. It's that simple.

 

[Micron]

The status field can't be removed; it's a required field

Then create a new query, add the union query and permanent table to this new query and add status field

Union sql (name is a reserved word)
SELECT EmplID, fullname from tableA
UNION
SELECT EmplID, fullname from tableB;

Final query
SELECT qryUnion.*, IIf(IsNull([Status]),"Contractor",[Status]) AS EmplStatus
FROM qryUnion LEFT JOIN TableA ON qryUnion.fullname = TableA.FullName;

 

[Bamh1]

The union result should look like the third image

Union sql (name is a reserved word)
SELECT EmplID, fullname from tableA
UNION
SELECT EmplID, fullname from tableB;

Final query
SELECT qryUnion.*, IIf(IsNull([Status]),"Contractor",[Status]) AS EmplStatus
FROM qryUnion LEFT JOIN TableA ON qryUnion.fullname = TableA.FullName;

Micron,

Thank you for taking the time to review this request; however, the query that you wrote doesn't seem to work; it's still including both versions of Mary (active and terminated) in the result. I want only the active one kept.

 

[Micron]

Now it's active vs terminated?

if a record of permanent employee also exist as a contractor,

What I posted seemed to work for me. If each table has 4 records

TableA

EmplID	FullName	Status
1	John Doe	Permanent
2	Jane Doe	Permanent
3	Susan Doe	Permanent
4	Mary Doe	Permanent

TableB

EmplID	FullName	Status
1	Rob Doe	Contractor
2	Jane Doe	Contractor
3	Cathy Doe	Contractor
4	Lisa Doe	Contractor

and only one person appears in both, the the result should be 7 records, which is what I got - and the person who's in both only shows up once.

Query1

EmplID	fullname	EmplStatus
1	John Doe	Permanent
1	Rob Doe	Contractor
2	Jane Doe	Permanent
3	Cathy Doe	Contractor
3	Susan Doe	Permanent
4	Lisa Doe	Contractor
4	Mary Doe	Permanent

I guess I don't understand the end goal.