Calculated column in SubQuery returns for all records in main query (1 Viewer)

[BiigJiim]

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:

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

 

[Gasman]

I'd hazard a guess at the bracketing.?
I'd put each case on a new line, so as to identify the correct bracketing and the ANDs and ORs ?

 

[MajP]

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 # ) );

if this is the subquery can you show the main query?