Littlemalky
Board Regular
- Joined
- Jan 14, 2011
- Messages
- 223
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:
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:
Code:
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 & "',"