Update table with SUM from another table.

So here's what I've got:

I have a table full of the number of parts ordered (Part) and another table with the items that have been ordered (OrderLine):

Allocation (INTEGER)

OrderNum (INTEGER)
NumOrdered (INTEGER)

Now, I need to get a sum of all the items that are in OrderLine that have "PartNum" and update the Allocation field of "Part" to this value in a single SQL statement.

I've been at this for about 5 hours now and can't seem to figure it out.  Initially, I thought this should work:

   SET Allocation = (SELECT SUM(Temp.NumOrdered)
                    FROM OrderLine AS Temp
                    WHERE Temp.PartNum  = Part.PartNum)
   WHERE Part.PartNum = 'KV29'

Open in new window

But this kicks back telling me that it's not an "updatable query".

Any help?

Who is Participating?

Improve company productivity with a Business Account.Sign Up

cyberkiwiConnect With a Mentor Commented:
   SET Allocation = DSUM("NumOrdered", "OrderLine", "PartNum='" & PartNum & "'")
WHERE PartNum = 'KV29'
Dale BurrellDirectorCommented:
That looks OK - can you post the exact error. My best guess at this point is that Part or OrderLine is actually a view which could explain the problems with updating it.
ajisasaggiConnect With a Mentor Commented:
This site explains the problem and provides some alternate solutions.
http://www.fmsinc.com/microsoftaccess/query/non-updateable/index.html#Example 2: Updating a Field with a Summary Query
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

first temp may be a key word, don't use it.

   SET Allocation =isnull( (SELECT SUM(T.NumOrdered)
                    FROM OrderLine AS T
                    WHERE T.PartNum  = Part.PartNum),0)
   WHERE Part.PartNum = 'KV29'
Also you have partnum as an integer in both tables but in your where clause you are using a string.
Dale BurrellConnect With a Mentor DirectorCommented:
Oh sorry, just realised you're using Access... I don't know whether you can use a sub-query to update a table in Access?

Here is an interesting article http://msdn.microsoft.com/en-us/library/bb221186(office.12).aspx if you follow the links at the bottom is talks about how to solve such issues - http://www.fmsinc.com/microsoftaccess/query/non-updateable/index.html.
Javin007Author Commented:
Ugh.  I was hoping this wasn't the answer.  This is a college class about constructing relational databases, and we're forced to use MS Access for it.  I am not even kidding when I say I honestly think I know more about databases than our instructor.  We've already been taught that MSAccess is an RDBMS (It's not.  JET is, Access is just a client).  That it doesn't support stored procedures (it does) and now we've been asked to do something MS Access isn't capable of doing without creating/deleting stored procedures on the fly, or creating/deleting tables...

I hate college.

Thanks for your help!  Articles answered my question.  Have to give the bulk to "ajisasaggi" as he was first with the link.

Javin007Author Commented:
Crap!  That was the correct answer, cyberkiwi!  Is there a way to "undo" the allocation of points?
At the bottom of the question area (top of page), there is a Request Attention link. You can put some words about what you wish done.

Only if it is no trouble :)
Javin007Author Commented:
Honestly, it's not to take the points away from the others (because their answers were also partially true, that it was specifically an MSAccess issue) but you had the correct answer to the workaround that their links didn't.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.