VBA SQL Max Function

[Littlemalky]

Hi, I'm trying to write an update query in which it filters for a specific employee ID, but also takes the Max of the primary key field. I'm not sure how to do this because it doesn't seem like a WHERE clause. I think I need to do a GROUP BY, but I'm not sure how to write it exactly in an UPDATE query?

I'm updating table "[tbl_TERRITORY_EMPLOYEE_MAP]", setting the [TERRITORY_START_DATE] field equal to a field on my form; inserting a MODIFIED BY name and a MODIFIED DATE stamp where the [EMPLOYEE_ID] field equals a value on my form. The problem is that the [EMPLOYEE_ID] field can have duplicates so to limit it further, I'd like to take the Max of the [ID] field, which is my primary key in the table.

Here is my current statement:

strSQL_TSDC = "UPDATE [tbl_TERRITORY_EMPLOYEE_MAP] SET [TERRITORY_START_DATE] = #" & Forms![frm_SALES_EMPLOYEE_MANAGEMENT]!txtTERRITORY_START_DATE_CHANGE & "#" & _
",[MODIFIED_BY] = '" & UserName() & "', [MODIFIED_DATE] = #" & Now() & "# " & _
"WHERE [EMPLOYEE_ID] = '" & Forms![frm_SALES_EMPLOYEE_MANAGEMENT]!cmbEMPLOYEE_ID & "',"

 

[xenou]

You could probably use a DMax() function in the where clause. The "where" part of the DMax function will have the same employee id you are talking about (Max id where employee id = blah blah blah).

 

[Norie]

Where exactly do you want to use the max of the ID in the query?

 

[Littlemalky]

I'm not sure how or where to put it. I'd just like to narrow this down to isolating one row, but taking the Max of [ID] where the [EMPLOYEE_ID] equals the value on the form.

 

[xenou]

How to use DMax:
https://www.techonthenet.com/access/functions/domain/dmax.php

 

[Norie]

Are you trying to find the latest record in the table for the [EMPLOYEE_ID] that's been entered on the form?

For example, if there are 5 records with [EMPLOYEE_ID] equal to 57 and they have the IDs 3, 12, 45, 67 and 69 you would want to return, and update, the record with the ID equal to 69?

 

[xenou]

Guessing but something like this should be added to the where clause:

 AND [ID] = DMax("[ID]", "[tbl_TERRITORY_EMPLOYEE_MAP]", "[EMPLOYEE_ID] = '" & Forms![frm_SALES_EMPLOYEE_MANAGEMENT]!cmbEMPLOYEE_ID & "'")

 

[Norie]

xenou

Will DMax work if the OP was using something like ADO (or DAO) to run the query?

 

[xenou]

Good question. Probably not, unless using DoCmd.RunSql()

 

[Littlemalky]

Thank you xenou! That worked.