Solved

# Update table with SUM from another table.

Posted on 2010-08-22
352 Views
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):

Part
PartNum (INTEGER)
Allocation (INTEGER)

OrderLine
OrderNum (INTEGER)
PartNum (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:

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

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

Any help?

0
Question by:Javin007
• 3
• 2
• 2
• +2

LVL 21

Expert Comment

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.
0

LVL 2

Assisted Solution

ajisasaggi earned 50 total points
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
0

LVL 4

Expert Comment

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

UPDATE Part
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.
0

LVL 21

Assisted Solution

Dale Burrell earned 50 total points
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.
0

LVL 4

Author Comment

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.

-Javin
0

LVL 58

Accepted Solution

cyberkiwi earned 400 total points
UPDATE Part
SET Allocation = DSUM("NumOrdered", "OrderLine", "PartNum='" & PartNum & "'")
WHERE PartNum = 'KV29'
0

LVL 4

Author Comment

Crap!  That was the correct answer, cyberkiwi!  Is there a way to "undo" the allocation of points?
0

LVL 58

Expert Comment

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 :)
0

LVL 4

Author Comment

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.
0

## Featured Post

### Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…