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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update Query 6
Update Query Question 3
Append Crosstab Query to Existing Table 0
dlookup in Query 10
Append Query 11
Query Date Problem 8
Crosstab Query - External Criteria 9
Outer Joins??? 1

Top