I am trying to create a query that will allow me to compare 2 tables. I want the query only to show me data that doesn’t appear in both tables. Any ideas on doing this? All I can see in the Join properties is option to shows data that does appear in both tables…
I would do the following - use the “Join” properties to set up the combination as if you wanted the comparison to show the data that does appear in both tables. Then, from the toolbar, select SQL view for the query. In the end of the thing, there will be something like:
ON Table1.Something = Table2.SomethingElse;
Modify that to:
ON NOT (Table1.Something = Table2.SomethingElse);
This should return all the results except for those where the data is equal. Do make sure you backup your DB before you meddle directly with the SQL though. It’s safer…
Try the UNION operator instead as by default it removes duplicates from the result set.
An example of UNION operator:
SELECT [CompanyName], [City]
FROM [Suppliers] <— no semi-colon!
UNION SELECT [CompanyName], [City]
ORDER BY [City];