asked on

Access - Query for Results where JOIN NOT Equal

Followup to this EE Question
I have a technical problem when I did this.

If I just do as you indicated above I get a list where it shows my TempClientAcctNbr and then every ClientAcctNbr from the other table where they don't match...

What I am needing is it to give me a list of TempClientAcctNbr if that same number doesn't show up in the ClientAcctNbr table.

SO in my inept ability I tried adding. a WHERE clause if the ClientAcctNbr table for that TempAcctNbr is null.

SELECT t1.*, t2.*
FROM TempClientTrialBalance AS t1, ClientAcctNbrs AS t2
WHERE (((IsNull([t2].[ClientAcctNbr]))=Tue));

I then went to my Temp table and changed one of the account number s so I knew I had a number that wasn't in the ClientAcctNbr table.

I don't get anything.

David Kroll

SELECT t1.*, t2.*
FROM TempClientTrialBalance AS t1
WHERE NOT EXISTS (SELECT t2.* FROM ClientAcctNbrs AS t2 WHERE t2.[ClientAcctNbr] = t1.[TempAcctNbr]);

mbizup

Your syntax looks good but you've misspelled true in your IsNull check.

Replace "Tue" with "True" in your SQL Statement.

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wlwebb

ASKER

dkrollCTN
I get
"The Microsoft Office Access database engine does not recognize 't2.*' as a valid field name or expression."

wlwebb

ASKER

mbizup
When I run the query I don't get any results and I Know I have at least one that is a new account number in the TempClientTrialBalances table.

David Kroll

SELECT t1.*
FROM TempClientTrialBalance AS t1
WHERE NOT EXISTS (SELECT t2.* FROM ClientAcctNbrs AS t2 WHERE t2.[ClientAcctNbr] = t1.[TempAcctNbr]);

wlwebb

ASKER

JimHorn That one worked!!!!

Jim Horn

Attached is a pic I've used recently to help understand JOINs.
Thanks for the grade. Good luck with your project. -Jim