Unmatched query

[Ursela]

Hi,

I want to return all records in a table that do not have
matching records in another query. I have used the
wizard but that only works if you are using one criterion
and I have 3. Here's the SQL I am trying to use. It
returns no results and there should be about 280. Thanks
in advance.

SELECT DISTINCTROW tblFringePR.PR_NUM, tblFringePR.FRG,
tblFringePR.PRN
FROM tblFringePR LEFT JOIN qryFringebyRate ON
(tblFringePR.PRN = qryFringebyRate.PRT) AND
(tblFringePR.PR_NUM = qryFringebyRate.PR_NUM) AND
(tblFringePR.FRG = qryFringebyRate.FRG)
WHERE (((qryFringebyRate.PR_NUM) Is Null) AND
((qryFringebyRate.FRG) Is Null) AND
((qryFringebyRate.PRT) Is Null));

 

[[MVP] S. Clark]

If your objective is to show ALL values from tblFringePR, that do NOT exist
in qryFringeByRate, then your SQL is correct. (I don't know what
qryFringeByRate is, so consider that there may be a problem in there, that I
don't see.)


Here is an example where I made a copy of Northwind Customers, and deleted
some records from the copy.
This query found the values in Customers that do not exist in Cust_Dup:

SELECT Customers.CompanyName
FROM Customers LEFT JOIN Cust_Dup ON
(Customers.City = Cust_Dup.City) AND
(Customers.ContactName = Cust_Dup.ContactName) AND
(Customers.CompanyName = Cust_Dup.CompanyName)
WHERE
(((Cust_Dup.CompanyName) Is Null) AND
((Cust_Dup.ContactName) Is Null) AND
((Cust_Dup.City) Is Null));


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Ursela]

I needed the records from qryFringeByRate and just
reading your first sentence helped clue me in. Thanks
for your help.

Ursela