Problem: Last and First do not return the last or first record based on the
value in the field. They return the last or first record of the ordered set
of records. If you aren't returning the records in any specific order then
you are going to essentially get a random record that meets the group
specifications.
Next problem: Update queries won't let you use any aggregate queries (except
in the where clause)
You can use the VBA domain functions - DMax, DFirst, DLast, etc. - in an
update query to return values.
Finally, if you can calculate the "Last" WageType you should probably not
store it in the Employee table. You will have to be constantly updating the
employee table to ensure that the "last" WageType is currently stored in the
employee table.
Without knowing how you define "last" for an entry in tblWages it is difficult
to propose a solution. I would guess last means latest and latest is defined
by some date field. On that assumption you MIGHT be able to use something
like the following correlated query - see the subquery in the where clause
UPDATE TblEmployees As E INNER JOIN tblWages as W
ON E.ID = W.ID
SET E.WageType = [W].[WageType]
WHERE W.DateField =
(SELECT Max([DateField])
FROM tblWages
WHERE tblWages.ID = W.ID)
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Secret said:
I'm trying to update my field "wagetype" in my tblemployees but I want to use
the last record from my tblwages. Here's what I have but it's not working.
Can anyone tell me where I'm going wrong?
UPDATE tblEmployees INNER JOIN tblWages ON tblEmployees.ID = tblWages.ID SET
tblEmployees.WageType = (Select Last(WageType) FROM tblWages);