• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • Last Modified:

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.
0
wlwebb
Asked:
wlwebb
  • 3
  • 2
  • 2
  • +1
1 Solution
 
David KrollCommented:
SELECT t1.*, t2.*
FROM TempClientTrialBalance AS t1
WHERE NOT EXISTS (SELECT t2.* FROM ClientAcctNbrs AS t2 WHERE t2.[ClientAcctNbr] = t1.[TempAcctNbr]);
0
 
mbizupCommented:
Your syntax looks good but you've misspelled true in your IsNull check.

Replace "Tue" with "True" in your SQL Statement.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>give me a list of TempClientAcctNbr if that same number doesn't show up in the ClientAcctNbr table.  

In the below query replace ? with however these two tables are joined.

SELECT
FROM TempClientTrialBalance AS t1
   LEFT JOIN ClientAcctNbrs AS t2 ON t1.? = t2.?
WHERE t2.ClientAcctNbr IS NULL
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
wlwebbAuthor Commented:
dkrollCTN
I get
"The Microsoft Office Access database engine does not recognize 't2.*' as a valid field name or expression."
0
 
wlwebbAuthor Commented:
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.
0
 
David KrollCommented:
SELECT t1.*
FROM TempClientTrialBalance AS t1
WHERE NOT EXISTS (SELECT t2.* FROM ClientAcctNbrs AS t2 WHERE t2.[ClientAcctNbr] = t1.[TempAcctNbr]);
0
 
wlwebbAuthor Commented:
JimHorn  That one worked!!!!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
JOINs
Attached is a pic I've used recently to help understand JOINs.
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now