Hi all,
I have a main query which returns every record in tbl_P2_Review which meets certain criteria, plus a number of fields from related tables. Returns approx 4000 records.
I want to include a column which flags records in tbl_P2_Review which have a very specific set of criteria. So I created a subquery returning only those records (approx 3 records) and included it in the main query with a RIGHT JOIN to tbl_P2_Review.
This is my SQL for the subquery:
What I cannot work out, is, when I include the "CA_401_Grove" column in my main query, why does it insert 401 for every single record, and not just the ones returned by the subquery? If I return the Case_ID column from the subquery, it correctly populates only the 3 records that are in the subquery.
I know there is a simple workaround for this (i.e. use the Case_ID from the subquery, not the CA_401_Grove column.) But I would like to find out why this simple thing does not work as it should.
Thanks for any help!
Jim
I have a main query which returns every record in tbl_P2_Review which meets certain criteria, plus a number of fields from related tables. Returns approx 4000 records.
I want to include a column which flags records in tbl_P2_Review which have a very specific set of criteria. So I created a subquery returning only those records (approx 3 records) and included it in the main query with a RIGHT JOIN to tbl_P2_Review.
This is my SQL for the subquery:
Code:
SELECT tbl_P2_Review_CA.Case_ID, 401 AS CA_401_Grove
FROM tbl_P2_Review_CA
WHERE (((tbl_P2_Review_CA.Case_ID) Like "CA*") AND ((tbl_P2_Review_CA.txtFinalOutcome) Like "Unsuitable Advised Transfer*" Or (tbl_P2_Review_CA.txtFinalOutcome) Like "Unsuitable Insistent Transfer*") AND ((tbl_P2_Review_CA.dtmFinalOutcome)>=#2/2/2021#));
What I cannot work out, is, when I include the "CA_401_Grove" column in my main query, why does it insert 401 for every single record, and not just the ones returned by the subquery? If I return the Case_ID column from the subquery, it correctly populates only the 3 records that are in the subquery.
I know there is a simple workaround for this (i.e. use the Case_ID from the subquery, not the CA_401_Grove column.) But I would like to find out why this simple thing does not work as it should.
Thanks for any help!
Jim