Return records in one table, not in another

[Guest]

I need the following translated into functional SQL:

Select * FROM tblHCounts WHERE
RCD_NEW = 'N' AND
(RCD_CHG = 'N' OR RCD_CHG = 'n') AND
RCD_ID NOT IN tblHChange

Could someone please help me out with this?
Thanks much in advance...

 

[Guest]

Sorry, I need to make a correction to that SQL so far; it should read:

Select * FROM tblHCounts WHERE
tblHCounts.RCD_NEW = 'N' AND
(tblHCounts.RCD_CHG = 'N' OR tblHCounts.RCD_CHG = 'n') AND
tblHCounts.Dept1 = tblHChange.Dept1 AND
tblHCounts.Dept2 = tblHChange.Dept2 AND
tblHCounts.RCD_ID NOT IN tblHChange.RCD_ID

Thanks and sorry for the confusion...

 

[Guest]

What the connection between the SQL and the title of the post?

To get which records are in one table and not in the other, you can use the
query wizard to create an unmatch record query.

 

[Guest]

Hi Ofer,

Admittedly, my pseudo-SQL may be confusing, but the last line is:

tblHCounts.RCD_ID NOT IN tblHChange.RCD_ID

I have tried the unmatched record query as you have suggested, but I do not
get the correct results. Is there a chance you could correct my SQL as posted
or walk me through the Wizard?

Thanks...

 

[Guest]

Try

SELECT tblHCounts.*
FROM tblHCounts LEFT JOIN tblHChange ON (tblHCounts.RCD_ID=
tblHChange.RCD_ID AND
tblHCounts.Dept1 = tblHChange.Dept1 AND
tblHCounts.Dept2 = tblHChange.Dept2)
WHERE tblHCounts.RCD_NEW = 'N'
AND tblHCounts.RCD_CHG = 'N'
AND tblHChange.RCD_ID Is Null