Comparing two excel tables and find the gaps between them;

[lubna]

[FONT=&quot]Hi All,[/FONT]
[FONT=&quot]I have 2 table and I want to find the gap between them and join them in one table to better comparison and presentation of gaps
as described in the attached screenshot
[/FONT]

[FONT=&quot][/FONT]
[FONT=&quot]Can I do this comparison using Microsoft access?[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Thank you,[/FONT]

[FONT=&quot]Lubna[/FONT]​

​

 

[Minty]

Yes - can do this if you build a Union query , https://support.office.com/en-us/ar...e-result-1f772ec0-cc73-474d-ab10-ad0a75541c6e

The easy way to build them in access is to create the both queries, then copy and paste the sql into a new union query.

 

[plog]

Actually, what you want is called a FULL OUTER JOIN (google that)...and its not supported in Access, so you have to do it yourself. Part of achieving it is using a UNION query. Here's how:

1. In design view, make a query based on Table1 and Table2. Link them apporpriately (C1 to C2) and then change that join line to the option that says something like 'Show All From Table1'. Bring down all the fields from both tables. Save this query as 'sub1'.

2. Make a copy of sub1 and name it sub2. Go into design view and change the join line to the option that says something like 'Show All From Table2'. Save the query.

3. Make a UNION query based on those 2 sub queries. This would be that SQL:

SELECT * FROM sub1 UNION SELECT * FROM sub2

That query will give you the results you want.