Why am I not able to match a record between 2 tables when joining them in a SQL inner join using Access 2003?
I attached an Access mdb file. I am working with Access 2003.
Within the Access database, I have 2 tables named tblOI and tbOINew.
Both tables have 1 record.
And there is one query named Query10
Why is the following Query10 not returning the record?
I think the 1 record is a match between the 2 tables.
SELECT a.*
FROM tblOINew AS a INNER JOIN tblOI AS b ON (a.Bank=b.Bank) AND (a.Office=b.Office) AND (a.checkNum=b.checkNum) AND (a.refNo=b.refNo) AND (a.Type=b.Type) AND (a.Pending=b.Pending) AND (a.AgeDays=b.AgeDays) AND (a.T=b.T) AND (a.Description=b.Descripti
Copy-of-ReportsDB.mdb
Within the Access database, I have 2 tables named tblOI and tbOINew.
Both tables have 1 record.
And there is one query named Query10
Why is the following Query10 not returning the record?
I think the 1 record is a match between the 2 tables.
SELECT a.*
FROM tblOINew AS a INNER JOIN tblOI AS b ON (a.Bank=b.Bank) AND (a.Office=b.Office) AND (a.checkNum=b.checkNum) AND (a.refNo=b.refNo) AND (a.Type=b.Type) AND (a.Pending=b.Pending) AND (a.AgeDays=b.AgeDays) AND (a.T=b.T) AND (a.Description=b.Descripti
Copy-of-ReportsDB.mdb
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
I realize there are errors. I removed all the objects that are confidential and not relevant.
I am working with 2 tables and I want to update 1 field of 1 of the tables.
My intent is to eventually perform the following update on many more records than just the one I presented:
This is a 1 time update.
update tblOINew a
Inner join tblOI b ON
(a.Bank=b.Bank) AND (a.Office=b.Office) AND (a.checkNum=b.checkNum) AND (a.refNo=b.refNo) AND (a.Type=b.Type) AND (a.Pending=b.Pending) AND (a.AgeDays=b.AgeDays) AND (a.T=b.T) AND (a.Description=b.Descripti
Set a.footnote=b.footnote
I am working with 2 tables and I want to update 1 field of 1 of the tables.
My intent is to eventually perform the following update on many more records than just the one I presented:
This is a 1 time update.
update tblOINew a
Inner join tblOI b ON
(a.Bank=b.Bank) AND (a.Office=b.Office) AND (a.checkNum=b.checkNum) AND (a.refNo=b.refNo) AND (a.Type=b.Type) AND (a.Pending=b.Pending) AND (a.AgeDays=b.AgeDays) AND (a.T=b.T) AND (a.Description=b.Descripti
Set a.footnote=b.footnote
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
No records selected, because some joined fields are Null.
In a previous question, I supplied this query but was with fewer records. Now it has been expanded to many fields of which some are nulls.
You need to change the Inner Join to a Cross JOIN:
Here is an example with 2 criteria fields. Expand using other fields add Nz to fields that allow nulls.
In a previous question, I supplied this query but was with fewer records. Now it has been expanded to many fields of which some are nulls.
You need to change the Inner Join to a Cross JOIN:
Here is an example with 2 criteria fields. Expand using other fields add Nz to fields that allow nulls.
UPDATE tblOI AS a, tblOINew AS b SET a.footnote=b.footnote
WHERE (((Nz([a].[bank],"nnn"))=Nz([b].[bank],"nnn")) AND ((Nz([a].[CheckNum],"nnn"))=Nz([b].[CheckNum],"nnn")));ASKER
Thank you so much hnasr. I really appreciate your insights after I awarded points already.
No problem, and welcome!
The attached MDB file contains multiple errors, notably PDFCreater, and some forms. Please correct and resubmit.
AielloJ