Update Query w/ Join

S

Secret Squirrel

I'm trying to update a field in my tblEmployees but it needs to update only
the records where the ID in the qrywagetype is the same as the ID in the
tblEmployees and those records in the qrywagetype equal "salary". I'm getting
the error that it's not an updateable query. Where am I going wrong?

UPDATE tblEmployees INNER JOIN qryWageType ON tblEmployees.ID =
qryWageType.ID SET tblEmployees.PTimePaid = True
WHERE (((qryWageType.WageType)="Salary"));
 
B

Beetle

You'll need to do something more along the lines of this;
(untested)

UPDATE tblEmployees SET PTimePaid = True Where tblEmployees.ID
IN(SELECT ID FROM tblWageType WHERE tblWageType.WageType = 'Salary')
 
S

Steve Schapel

Squirrel,

At a guess, the reason it is not updateable is that the qryWageType is a
Totals Query, or else contains a domain aggregate function (DLookup or
whatnot) in an expression somewhere... is that right?

You could change it to this, it might work better:

UPDATE tblEmployees SET tblEmployees.PTimePaid = True
WHERE tblEmployees.ID In (SELECT ID FROM qryWageType WHERE
qryWageType.WageType="Salary")
 
Top