Update... Sub Query Parameter Prompt?

[lameid]

The SQL below prompts me for the parameter indicated in red text when I add it. Originally I was using a similar sub query to select the status date but I was not getting expected results. I'm hoping this is something so obvious that I'm over looking... Any other suggestions are welcome too.

This is in Access 2003.

UPDATE Contract_Service_Level INNER JOIN Contract_Service_Level_Status ON Contract_Service_Level.CSL_ID = Contract_Service_Level_Status.CSL_ID SET Contract_Service_Level.Current_Status_Date = Contract_Service_Level_Status.Status_Date, Contract_Service_Level.Current_Note = Contract_Service_Level_Status.Note, Contract_Service_Level.Current_Unapproved = Contract_Service_Level_Status.Unapproved
WHERE (((Contract_Service_Level_Status.CSL_ID) In (Select CSL_ID From [qry Recent Contract_Service_Level_Status] Where  [qry Recent Contract_Service_Level_Status].CSL_ID = Contract_Service_Level_Status.CSL_ID And [qry Recent Contract_Service_Level_Status].MaxOfStatus_Date = [Red]Contract_Service_Level_Status.Status_Date[/red])));

qry Recent Contract_Service_Level_Status
SQL:

SELECT Contract_Service_Level_Status.CSL_ID, Max(Contract_Service_Level_Status.Status_Date) AS MaxOfStatus_Date
FROM Contract_Service_Level_Status
GROUP BY Contract_Service_Level_Status.CSL_ID;

The only reason I'm doing this at all is that joining in the appropriate query into another multijoin behemoth data set would make a slow query, run over lunch break slow.

 

[lameid]

After spending some time away from this, I decided to try aliasing the table that is used in both the source of the sub-query from and the table that is used in the criteria of the sub query. It worked...

UPDATE Contract_Service_Level INNER JOIN Contract_Service_Level_Status [red]AS CSLS [/red] ON Contract_Service_Level.CSL_ID = CSLS.CSL_ID SET Contract_Service_Level.Current_Status_Date = CSLS.Status_Date, Contract_Service_Level.Current_Note = CSLS.Note, Contract_Service_Level.Current_Unapproved = CSLS.Unapproved
WHERE (((CSLS.CSL_ID) In (Select CSL_ID From [qry Recent Contract_Service_Level_Status] Where  [qry Recent Contract_Service_Level_Status].CSL_ID = CSLS.CSL_ID And [qry Recent Contract_Service_Level_Status].MaxOfStatus_Date = CSLS.Status_Date)));

Anytime Access uses grouping, it seems that it can reorganize the query and get confused if the same table is used more than once. I have not seen it in the context of an aggregate sub-query used for criteria before... usually I see it in nested aggregate queries.

In any case, I hope this helps someone.