Update query with Select statement in Update To

DMASRUD
DMASRUD used Ask the Experts™
on
Is it possible to do an update query like this?  I'm getting syntax error.

UPDATE tblPhysMasterFileCurrent SET tblPhysMasterFileCurrent.CummAvailHrs =
(SELECT CummHrs FROM tblCalendarPaydates WHERE Paydate=
(SELECT Max(tblYtdPayroll.Paydate)))

The SELECT statement gives one number that I want updated into tblPhysMasterFileCurrent.CummAvailHrs
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If you would like to update all the records with the CummHrs value of tblCalendarPaydates then you can try this:

UPDATE tblPhysMasterFileCurrent
SET tblPhysMasterFileCurrent.CummAvailHrs = b.CummHrs
FROM tblPhysMasterFileCurrent CROSS JOIN (SELECT TOP 1 CummHrs FROM tblCalendarPaydates WHERE tblCalendarPaydates.Paydate=(SELECT Max(tblYtdPayroll.Paydate) FROM tblYtdPayroll)) b
NorieAnalyst Assistant

Commented:
That second SELECT statement has no FROM clause.

Shouldn't it be something like this:

SELECT Max(tblYtdPayroll.PayDate) FROM tblYtdPayRoll

Or you could even try DMax:

DMax("PayDate", "tblYtdPayroll")

Author

Commented:
Thanks imnorie - I've updated as follows and no longer get a syntax error, but now am getting error "Operation must use an updateable query".  Any additional advice?

UPDATE tblPhysMasterFileCurrent SET tblPhysMasterFileCurrent.CummAvailHrs = (SELECT CummHrs FROM tblCalendarPaydates WHERE Paydate=
(SELECT Max(tblYtdPayroll.Paydate) FROM tblYtdPayroll));
HainKurtSr. System Analyst

Commented:
is this query running fine

SELECT CummHrs FROM tblCalendarPaydates WHERE Paydate= (SELECT Max(tblYtdPayroll.Paydate) FROM tblYtdPayroll)

if yes, then

UPDATE tblPhysMasterFileCurrent SET tblPhysMasterFileCurrent.CummAvailHrs = (above query)

should run fine :)

Author

Commented:
This works fine independently:
SELECT CummHrs FROM tblCalendarPaydates WHERE Paydate= (SELECT Max(tblYtdPayroll.Paydate) FROM tblYtdPayroll)

This works when viewed, but not when Run - I get the "Operation must use an updateable query" error when Run:
UPDATE tblPhysMasterFileCurrent SET tblPhysMasterFileCurrent.CummAvailHrs =
(SELECT CummHrs FROM tblCalendarPaydates WHERE Paydate= (SELECT Max(tblYtdPayroll.Paydate) FROM tblYtdPayroll))

It works when I take out the SELECT statement and put it in its own query:
UPDATE tblPhysMasterFileCurrent, qryMaxPaydate_CummHrs SET tblPhysMasterFileCurrent.CummAvailHrs = [qryMaxPaydate_CummHrs]![CummHrs];

Is that the best workaround?
Sr. System Analyst
Commented:
you can use DMax and DLookup

UPDATE tblPhysMasterFileCurrent SET tblPhysMasterFileCurrent.CummAvailHrs =
(SELECT CummHrs FROM tblCalendarPaydates WHERE Paydate= DMax("Paydate","tblYtdPayroll"))

or

UPDATE tblPhysMasterFileCurrent SET tblPhysMasterFileCurrent.CummAvailHrs =
Dlookup("CummHrs", "tblCalendarPaydates","Paydate=#" & DMax("Paydate","tblYtdPayroll") &"#")


Author

Commented:
Thanks alot - the DLookup option worked!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial