Update Query

[Secret Squirrel]

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);

 

[Secret Squirrel]

I should note that I'm trying to update more than just 1 employee. I want to
use the last record from the tblwages but I need to use the last record for
every employee in that table. So if there were 3 employees in the tblwages
then I want to find the last record for each of them and then use the value
in wagetype to update to my tblemployees wagetype.

 

[Brian]

Try this instead (uses DLast instead of Last):

UPDATE tblEmployees SET tblEmployees.WageType =
DLast("[WageType]","[tblWages]","[ID] = " & [tblEmployees].[ID]);

However, I personally dislike using Last or DLast without having some
chronological factor to determine which is truly the last; that is, I would
probably rely on a DMax of the PK in tbWage entries for the employee or,
better yet, the last effective date for a wage entry for the employee.

 

[Secret Squirrel]

Thanks Brian.

Wouldn't DMax do the same as DLast? Not sure I understand what the
difference would be. DLast will use the last record for that specific
employee, correct?

Try this instead (uses DLast instead of Last):

UPDATE tblEmployees SET tblEmployees.WageType =
DLast("[WageType]","[tblWages]","[ID] = " & [tblEmployees].[ID]);

However, I personally dislike using Last or DLast without having some
chronological factor to determine which is truly the last; that is, I would
probably rely on a DMax of the PK in tbWage entries for the employee or,
better yet, the last effective date for a wage entry for the employee.

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);

 

[Secret Squirrel]

If I was to use DMax would I just replace the DLast with DMax? Would I get
the same result where it would use the last record for each employee in the
tblwages?

Try this instead (uses DLast instead of Last):

UPDATE tblEmployees SET tblEmployees.WageType =
DLast("[WageType]","[tblWages]","[ID] = " & [tblEmployees].[ID]);

However, I personally dislike using Last or DLast without having some
chronological factor to determine which is truly the last; that is, I would
probably rely on a DMax of the PK in tbWage entries for the employee or,
better yet, the last effective date for a wage entry for the employee.

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);

 

[John Spencer]

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 Squirrel]

Thank you for the explanation John. I will take your advice and not store the
value in my tblemployees. I created a query based on your code to extract the
last wage type for each employee and use it from the query instead of saving
it in the tblemployees.
Makes much more sense to do it this way and not have to worry about running
the risk of not having the latest value in my tblemployees.

Thanks Again!
SS

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

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);