Solved

Update table with SUM from another table.

Posted on 2010-08-22
10
359 Views
Last Modified: 2012-05-10
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'
;

Open in new window


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

Any help?


0
Comment
Question by:Javin007
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 33498020
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

by:ajisasaggi
ajisasaggi earned 50 total points
ID: 33498030
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

by:MeLindaJohnson
ID: 33498032
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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 21

Assisted Solution

by:Dale Burrell
Dale Burrell earned 50 total points
ID: 33498036
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

by:Javin007
ID: 33498103
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

by:
cyberkiwi earned 400 total points
ID: 33498120
UPDATE Part
   SET Allocation = DSUM("NumOrdered", "OrderLine", "PartNum='" & PartNum & "'")
WHERE PartNum = 'KV29'
0
 
LVL 4

Author Comment

by:Javin007
ID: 33498210
Crap!  That was the correct answer, cyberkiwi!  Is there a way to "undo" the allocation of points?
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33498219
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

by:Javin007
ID: 33498236
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question