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");
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");
SELECT tblBanks.*, tblDates.dtRec, tblOpenItems.*
FROM tblDates, tblOpenItems LEFT JOIN tblBanks ON tblBanks.[Banks.[Bank Code]=tblOpenItems..Bank WHERE tblBanks.[Bank Code] is Null
FROM tblDates, tblOpenItems LEFT JOIN tblBanks ON tblBanks.[Banks.[Bank Code]=tblOpenItems..Bank WHERE tblBanks.[Bank Code] is Null
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");
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");
ASKER
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
select tblOpenItems.bank
from tblOpenItems
left join tblOpenItems ON tblBanks.[Bank Code]=tblOpenItems.Bank
where tblBanks.[Bank Code] is null