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

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");
0
zimmer9
Asked:
zimmer9
1 Solution
 
Rey Obrero (Capricorn1)Commented:

select tblOpenItems.bank
from tblOpenItems
left join tblOpenItems ON tblBanks.[Bank Code]=tblOpenItems.Bank
where tblBanks.[Bank Code] is null
0
 
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
0
 
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");
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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.

0
 
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.


0
 
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


 
0
 
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.


0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now