I would like to compare two columns in two tables

Question

I have two tables Main & Training and I full join them on the Main.ID = Training.EMP_ID; BUT I have a big problem in my data.

I have to use FULL JOIN to get all data from both tables; however I have some ID's & Emp_Names in my Training table different from my Main table.

I need to compare both columns and return all EMP_ID's from both tables and compare EMP_Names from both tables to make sure they are not recorded more than once!

I would appreciate any help!

I created UNION for my full JOIN and I get This

ID       DataA        DataB 1 
1        DataA1  
2        DataA2  
3        DataA3 
4        DataA4  
5        DataA5  
6        DataA6      DataB6 
7        DataA7      DataB7 
8        DataA8      DataB8  
9        DataA9      DataB9 
10       DataA10     DataB10  
11                   DataB11  
12                   DataB12
13                   DataB13 
14                   DataB14 
15                   DataB15

Now, How can I join these two columns as one? and eliminate duplicates & put in one column?

Something like this might help [http://stackoverflow.com/questions/19615177/how-do-i-write-a-full-outer-join-query-in-access] — Roland, Jun 18 '14 at 13:25
In cases like this, I usually create a Union query to get all the IDs from each tables into one place, then use outer joins from the Union query to each of the source tables in the next query. Then I can easily identify common records (the ID exists in both tables) or records in either source table but not the other (one of the 2 IDs is null in the source tables). — Don George, Jun 18 '14 at 14:05
Add a field something like "Data:iif(dataA<>null,dataA,dataB)" - depending on what you want the result to be when they overlap (like rows 6-10). You can add the field in the Union query or in a second query built on the Union, which might be easier and is what I would do. — Don George, Jun 18 '14 at 18:04
@DonGeorge CAN YOU give me an example of How you write union to get all IDs from two tables into one place and Finally you said you use outer joins from union to each of the source tables in the next query... — SamR, Jun 18 '14 at 22:32

score 0 · Answer 1 · answered Jun 19 '14 at 13:30

I set up 2 tables, MAIN and TRAINING, each with an ID field and an Emp_Names field.

The Union query (ID Master List) to get all of the IDs is

Select ID from Main
Union select ID from Training;

And the 2nd query to get the results you need is

SELECT [ID Master list].ID, 
Main.Emp_Names AS [Main name], 
Training.Emp_Names AS [Training name], 
IIf([main].[emp_names] Is Not Null,[main].[emp_names],[training].[emp_names]) AS Name

FROM ([ID Master list] 

LEFT JOIN Main ON [ID Master list].ID = Main.ID) 
LEFT JOIN Training ON [ID Master list].ID = Training.ID;

You can modify the logic in the iif statement to change what you do when both names are present but may be different.

score 0 · Answer 2 · answered Jun 20 '14 at 15:13

It sounds like you are trying to find the duplicates so you can get rid of them.

Access has a Query Wizard that allows you to Find Duplicates between two tables.

I would suggest trying this to find the ones that are duplicated as opposed to using Unions...

I would like to compare two columns in two tables

2 Answers2