If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Value missing from either table
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. |
#2
|
|||
|
|||
Value missing from either table
Go to the unmatched query wizard under new queries. It will show you the ones
that don't have a match. "grdngurl" wrote: 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. |
#3
|
|||
|
|||
Value missing from either table
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; -- KARL DEWEY Build a little - Test a little "grdngurl" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|