# 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

Micron said:


> 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


Bamh1 said:


> 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 said:


> 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

Bamh1 said:


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





Micron said:


> 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

Bamh1 said:


> The union result should look like the third image





Micron said:


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


Bamh1 said:


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

EmplIDFullNameStatus1John DoePermanent2*Jane Doe*Permanent3Susan DoePermanent4Mary DoePermanent

*TableB*

EmplIDFullNameStatus1Rob DoeContractor2*Jane Doe*Contractor3Cathy DoeContractor4Lisa DoeContractor

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*

EmplIDfullnameEmplStatus1John DoePermanent1Rob DoeContractor2*Jane Doe*Permanent3Cathy DoeContractor3Susan DoePermanent4Lisa DoeContractor4Mary DoePermanent
I guess I don't understand the end goal.


----------

