Find Unmatched query

[angloman]

Hi all,

I need help constructing a find unmatched query. I don't really know what I'm doing with SQL and I think that I can only take things so far with query design view.

I have two tables. T_Productions and ProPro (see attached). In Access query design, I can only specify one matching criteria. I need a query that will show me records from T_Productions that don't have matching Production_Code (PpNum in ProPro), Avantage_Product_Code (PpPrNum in ProPro) and Step (PpEtape in ProPro) in ProPro.

What I have now only gives me the records where Avantage_Product_Code don't match PpPrNum.

SELECT T_Productions.ID, T_Productions.Production_Code, T_Productions.Avantage_Product_Code, T_Productions.Step, T_Productions.Quantity_Required
FROM T_Productions LEFT JOIN ProPro ON T_Productions.[Avantage_Product_Code] = ProPro.[PpPrNum]
WHERE (((ProPro.PpPrNum) Is Null));

Thanks for your help.

 

[plog]

In Access query design, I can only specify one matching criteria

Who says? See that line between the 2 tables? You need 2 more--one each for the relationships you described. Those new JOINS need to be just like the one you already have (with the arrow pointing to the ProPro table)

 

[angloman]

I tried that first. It gives me an error that I don't know how to get around.

 

[angloman]

Er, nevermind. You're right I'm an idiot. Thanks for your help!

 

[plog]

80% there, you just need to make the arrows appear on those new ones like you have on that first link you had. To do that you need to right click on the lines, select 'Join Properties' and then check the option that gives you all records from T_Productions in the dialog that pops up.