UPDATE statement with subquery

I have the following query and when I try to execute it I get a message of You have written a subquery that can return more than one field without using the EXISTS reserved work in the main querys from clause.   I'm using Microsoft Access and am not sure how to revise the query.  Any help would be greatly appreciated.  I'm using Microsoft Access 2003.


Thanks

update caserecords b
set unithours  = (select caseid, sum(casehours) as hours, int(sum(casehours)/60)&":"& (sum(casehours) mod 60)   as total_time from contactevents a
where b.id = a.caseid
group by caseid)

Open in new window

lfolkuAsked:
Who is Participating?
 
miqrogrooveConnect With a Mentor Commented:
Oops the USING term is not correct..

UPDATE caserecords AS b LEFT JOIN contactevents AS a ON a.caseid=b.id
SET b.unithours = int(sum(a.casehours)/60)&":"& (sum(a.casehours) mod 60)
GROUP BY b.id
0
 
miqrogrooveCommented:
You have set unithours  = followed by several fields.  Access thinks you are trying to assign multiple values to the unnithours field.
0
 
miqrogrooveCommented:
I think you meant to do something like this:

UPDATE caserecords AS b LEFT JOIN contactevents AS a USING a.caseid=b.id
SET b.unithours = int(sum(a.casehours)/60)&":"& (sum(a.casehours) mod 60)
GROUP BY b.id

This is very complex though, you might want to try setting a simpler value to make sure the other syntax is correct first.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
lfolkuAuthor Commented:
miqrogroove,

I get a syntax error (missing operator) in query expression  'int(sum(a.casehours)/60)&":"& (sum(a.casehours) mod 60)
GROUP BY b.id'

Laurie
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
what about thius:
0
 
miqrogrooveCommented:
I can't get Jet to eat a multi-table update command.  Try angellll's idea, and if that doesn't work we can write a query loop.
0
 
lfolkuAuthor Commented:
Thanks for your help.
0
All Courses

From novice to tech pro — start learning today.