Subquery

[AlSallam]

I have a query "qryConversion_Cases" which is pulling data from a table called "tblInitialExtraction". This query has the following fields

Client_Name, Case_ID, EntityID

I am Trying to add another calculated field "RC_Case_ID" with subquery to check if the Case_ID exist in another query "qryRenewal_Completed" where EntityID in "qryConversion_Cases" is the same as EntityID in "qryRenewal_Completed". I want "RC_Case_ID" to return the Case_ID if found in "qryRenewal_Completed", else the field would be blank.

I tried the following but I am either getting a message that the subquery can only return one value or syntax error
===============================================
Select Client_Name, Case_ID, EntityID, (Select Case_ID From qryRenewal_Completed where exists(select Distinct Max(qryConversion_Cases.Case_ID) FROM qryRenewal_Completed Where qryRenewal_Completed .EntityID = tblInitial_Extraction.EntityID) Group By EntityID) As RC_Case_ID From tblInitial_Extraction;
===========================================

I am not sure what I am doing wrong. i need some help. thanks

 

[namliam]

Next time try using the code tags as I am doing...
Easiest is to simply outerjoin the query

Something along the lines of....

Select Client_Name
     , Case_ID
     , EntityID
     , RCCaseid
From tblInitial_Extraction
left join ( Select Case_ID as RCCaseID 
            From   qryRenewal_Completed group by case_id ) on case_id = RCcaseid ;

 

[AlSallam]

Thank you so much for your reply. I am getting a message " JOIN expression not supported". I guess that this would work for SQL Server but not Access?

 

[namliam]

Next time try using the code tags as I am doing...
Easiest is to simply outerjoin the query

Something along the lines of....

Select Client_Name
     , Case_ID
     , EntityID
     , RCCaseid
From tblInitial_Extraction
left join ( Select Case_ID as RCCaseID 
            From   qryRenewal_Completed group by case_id ) on case_id = RCcaseid ;

just missed an allias for the subselect , thats aircode for you :banghead:

Select Client_Name
     , Case_ID
     , EntityID
     , RCCaseid
From tblInitial_Extraction
left join ( Select Case_ID as RCCaseID 
            From   qryRenewal_Completed group by case_id ) as subSelect on case_id = RCcaseid