"Create a query that updates students' credits..."
You may have correctly stated that you need to update a total since you were there when the assignment was given, but we were not. This statement does not say to me that you need to update a total at all. You could have a CreditID (autonumber?) and the StudentID should be a foreign key in that table. It could be indexed, but dupes need to be allowed. Then for each order received (I don't see what denotes received from ordered and received versus just ordered) there would be a record for that StudentID and a Credit value (I have no idea what that is, but I sure hope it's a number). Then a query can total the credits, grouped by StudentID.
However, Credits seems to be an attribute of the book, thus there's no need for a credits table at all if you're not storing the number.
Hopefully, that is not giving you too much information with respect to how limited you wanted that to be. I have to assume the table structure was not given to you (i.e. it is your own) otherwise, the design is not what I'd expect an instructor to foist upon you given the task as I understand it.
EDIT:
Date is a reserved word and should not be used to name things in Access. OrderDate would be better (note - no spaces!)
This is one way to get the total per student (leaves you with the challenge of incorporating it into the end goal). It does not use the Credits table.
Code:
SELECT Orders.[Student ID], Sum(Books.Credits) AS SumOfCredits
FROM Books INNER JOIN Orders ON Books.[Book ID] = Orders.[Book ID]
GROUP BY Orders.[Student ID];
Last edited by Micron; 06-24-2017 at 07:36 PM.
Reason: added info
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.