How to find the values not present in one table compared to another table in an Access 2003 mdb type file?

I am modifying an Access program using Access 2003 with an MDB type file.

How would you modify the following SQL statement to find the values for field "Bank" in
table tblOpenItems that don't exist in table tblBanks for field [Bank Code]:

SELECT tblBanks.*, tblDates.dtRec, tblOpenItems.*
FROM tblDates, tblBanks INNER JOIN tblOpenItems ON tblBanks.[Bank Code]=tblOpenItems.Bank
WHERE tblOpenItems.t In ("A","D","E");
zimmer9Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:

select tblOpenItems.bank
from tblOpenItems
left join tblOpenItems ON tblBanks.[Bank Code]=tblOpenItems.Bank
where tblBanks.[Bank Code] is null
kmslogicCommented:
SELECT tblBanks.*, tblDates.dtRec, tblOpenItems.*
FROM tblDates, tblOpenItems LEFT JOIN tblBanks ON tblBanks.[Banks.[Bank Code]=tblOpenItems..Bank WHERE tblBanks.[Bank Code] is Null
mbizupCommented:
SELECT tblBanks.*, tblDates.dtRec, tblOpenItems.*
FROM tblDates, tblBanks RIGHT JOIN tblOpenItems ON tblBanks.[Bank Code]=tblOpenItems.Bank
WHERE tblBanks.[Bank Code] IS NULL AND tblOpenItems.t In ("A","D","E");
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

zimmer9Author Commented:
select tblOpenItems.bank
from tblOpenItems
left join tblOpenItems ON tblBanks.[Bank Code]=tblOpenItems.Bank
where tblBanks.[Bank Code] is null
----------------------------------------
I tried this in Access using a Select query and got the following error:
Syntax error in JOIN operation. (Error 3135)
You entered an SQL statement that has an invalid join operation. It could be an inner join, outer join, or self-join.

Possible causes:

A reserved word or argument name is misspelled or missing.
Punctuation is incorrect.

zimmer9Author Commented:
SELECT tblBanks.*, tblDates.dtRec, tblOpenItems.*
FROM tblDates, tblBanks RIGHT JOIN tblOpenItems ON tblBanks.[Bank Code]=tblOpenItems.Bank
WHERE tblBanks.[Bank Code] IS NULL AND tblOpenItems.t In ("A","D","E");
---------------------------------------------
I tried this in Access using a Select query and got the following error:

Join expression not supported. (Error 3296)
Possible causes:

Your SQL statement contains multiple joins in which the results of the query can differ, depending on the order in which the joins are performed. You may want to create a separate query to perform the first join, and then include that query in your SQL statement.
The ON statement in your JOIN operation is incomplete or contains too many tables. You may want to put your ON expression in a WHERE clause.


Rey Obrero (Capricorn1)Commented:


sorry, try this

select tblOpenItems.bank
from tblOpenItems
left join tblBanks ON tblBanks.[Bank Code]=tblOpenItems.Bank
where tblBanks.[Bank Code] is null


 

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zimmer9Author Commented:
SELECT tblBanks.*, tblDates.dtRec, tblOpenItems.*
FROM tblDates, tblOpenItems LEFT JOIN tblBanks ON tblBanks.[Banks.[Bank Code]=tblOpenItems..Bank WHERE tblBanks.[Bank Code] is Null
---------------------------------------------
I tried this in Access using a Select query and got the following error:

Join expression not supported. (Error 3296)
Possible causes:

Your SQL statement contains multiple joins in which the results of the query can differ, depending on the order in which the joins are performed. You may want to create a separate query to perform the first join, and then include that query in your SQL statement.
The ON statement in your JOIN operation is incomplete or contains too many tables. You may want to put your ON expression in a WHERE clause.


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.