Value missing from either table

  • Thread starter Thread starter grdngurl
  • Start date Start date
G

grdngurl

I have a relationship set up to match part numbers from two inventory system
tables. I noticed when a part number is missing from one table it does not
appear in the query results. Is there any way around this omission of data?
I'd like to use the query results to review the value of the data between
systems and also to find cases where a number is missing from one system or
the other.
 
Go to the unmatched query wizard under new queries. It will show you the ones
that don't have a match.
 
Normally you use an unmatch query but in your case you may have an unmatch
both ways.
So create a union query named Part_List --
SELECT [Part_Number]
FROM TableA
UNION SELECT [Part_Number]
FROM TableB

Then run the unmatched --
SELECT [Part_List].[Part_Number], IIF([TableA].[Part_Number] Is Null, "X",)
AS MissingTableA, IIF([TableB].[Part_Number] Is Null, "X",) AS MissingTableB
FROM ([Part_List] LEFT JOIN TableA on [Part_List].[Part_Number] =
[TableA].[Part_Number]) LEFT JOIN ([Part_List] LEFT JOIN TableB on
[Part_List].[Part_Number] = [TableB].[Part_Number])
WHERE [TableA].[Part_Number] Is Null OR [TableB].[Part_Number] Is Null;
 
Back
Top