Max Function in Update Query 1

[bajo71]

Hello,

I have the following statement:

UPDATE tblDSI_Stage1 INNER JOIN tblPAYMENT_LOOKUP ON tblDSI_Stage1.PRIM_INS = tblPAYMENT_LOOKUP.INS_NAME SET tblDSI_Stage1.[Best Rate_Paid(G)] = Max([tblPAYMENT_LOOKUP].[COST_PER_TREATMENT])
WHERE (((tblPAYMENT_LOOKUP.LOOKUP_TYPE)="PAID") AND ((tblPAYMENT_LOOKUP.GROUP)="Clinic"));

However, I receive the Aggregate Function error. How can I update field [Best Rate_Paid(G)] with the Max within [COST_PER_TREATMENT])??

Thank you much........Mick

 

[dhookom]

You may need to get rid of tblPayment_Lookup and reference it with DMax().

UPDATE tblDSI_Stage1 
SET tblDSI_Stage1.[Best Rate_Paid(G)] = DMax("[COST_PER_TREATMENT]","[tblPAYMENT_LOOKUP]","LOOKUP_TYPE= 'PAID' AND GROUP='Clinic' AND INS_NAME = """ & PRIM_INS & """")

Duane
Hook'D on Access
MS Access MVP