1

I am using microsoft access and I have a total of 3 tables: Credits, Orders, and Books which are shown below. I need to create an update query that updates the existing Number of Credits for each student based on the books that they have ordered and the corresponding amount of credits for each book.

For instance, student B-17 starts with 24 credits but after the update query it should change the student's credits to 32.

Credits Table

Student ID    Number of Credits
B-17          24
F-59          30

Orders Table

Student ID    Book ID
B-17          101
B-17          102
F-59          101
F-59          105

Books Table

Book ID    Book Title    Credits
101        English I     3
102        Accounting    5
105        Calculus      5

This is what I am trying but I keep getting a syntax error in Access.

UPDATE Credits c
SET [Number of Credits] = [Number of Credits] + (SELECT SUM(Credits)
FROM Orders o, Books b ON
o.[Book ID] = b.[Book ID] WHERE 
c.[Student ID] = o.[Student ID])
WHERE c.[Student ID] = o.[Student ID];
Dab Sauce
  • 59
  • 6
  • Saving calculated data, especially aggregate data is usually a bad idea. Save records for incoming/outgoing transactions and then calculate net balance when needed. Review http://allenbrowne.com/AppInventory.html – June7 Aug 05 '17 at 22:27
  • What is the error you are getting? – ChrisM Aug 05 '17 at 22:53
  • Syntax error in query expression – Dab Sauce Aug 06 '17 at 00:46
  • Possible duplicate of [Updating values in a table based on the values from a different table](https://stackoverflow.com/questions/45526208/updating-values-in-a-table-based-on-the-values-from-a-different-table) – Gustav Aug 06 '17 at 07:14

1 Answers1

0

You can try to use TEMP table for storing your data from the 2nd and 3rd tables; Do like this: 1) create another 4th table (i.e. Temp) with two columns: Student ID and Credits; 2) run this script:

SELECT o.[Student ID], sum (b.Credits) as Credits INTO Temp 
FROM books b INNNER JOIN orders o
on b.[Book ID] = o.[Book ID] 
GROUP BY o.[Student ID];

3) Start to Update:

UPDATE Credits c, Temp t set c.[Number of Credits] = c.[Number of Credits] + t.Credits
WHERE c.[Student ID] = t.[Student ID];

4) Save and Finished!

Useful: 1) "Operation must use an updateable query" error in MS Access; 2) MS Access database (2010) how to create temporary table/procedure/view from Query Designer

Ikrom
  • 474
  • 4
  • 10