Using DMax in Update query

[primagic]

I have a table which has a field for leaving date. This table will have duplicate records whereby 1 customer number may have 3 different leave dates.

I need to update the most recent leave date in table 1 with the leave date from table two but I can't figure out how to use dmax.

Can it be done?

 

[dhookom]

primagic,

At the very least please provide table & field names as well as data types.

Duane
Hook'D on Access
MS Access MVP

 

[primagic]

My current query is a simple update query:

UPDATE [tblEuropean Attrition Report] INNER JOIN tblAttrition ON [tblEuropean Attrition Report].[SAP No] = tblAttrition.[SAP no] SET [tblEuropean Attrition Report].[Leaving Date] = [tblAttrition].[Leave date];

SAP No = number
Leave Date = DateTime

tblEuropean Attrition Report has the duplicate records.

tblAttrition has the leave date I want to update

 

[dhookom]

Your SQL suggests you want to update [tblEuropean Attrition Report].[Leaving Date] but your post states "tblAttrition has the leave date I want to update".
I think you want something like:

UPDATE tblAttrition
SET [Leave date] = DMax("[Leaving date]","[tblEuropean Attrition Report]","[SAP No]=" & [SAP No])

Duane
Hook'D on Access
MS Access MVP