Question: Create a query that updates students ’ credits to account for the book
ID: 3866807 • Letter: Q
Question
Question: Create a query that updates students ’ credits to account for the books they ’ ve ordered and received. Click theRun button to test the query. When prompted to change the records, answer “ Yes. ” Save the query as Credit Update.
I need to do this in Microsoft Access and I cannot for the life of me figure it out. Here are my tables:
"Students Table"
"Credits" Table
"Books" Table
"Orders" Table
***EDIT*** Sorry, I don't know how I left out the orders table.
Relationships
I am not sure if this is how I should have the relationships set up so please let me know if I should do it a different way.
Student IC | Last Name B-17 F-59 L-29 M-62 May P-91 Q-13 S-63 Z-30 | First Name Address City State Zip Cell Phone Telephone Email Address 19101 215-876-3376 215-877-4673 abrew@yahoo.com 19111 215-765-1263 215-877-2342 gf59@gmail.com 19101 569-001-0989 215-234-8876mrl@yahoo.com 19111 215-887-3829 215-998-3928 belle@comcast.net 19101 215-342-9087 215-887-9238 jp82@comcast.net 19125 569-984-3894 215-987-3427 quinn45@gmail.com 19111 235-121-4736 215-763-3342 patti1@gmail.com 19125 215-776-4536 215-675-0091 zeconj@comcast.net 9 Pleasant Way Philadelphia PA Brewster Freeny Lopez Angela Maria Allie Jane Samantha 59 W. Central Ave Philadelphia 89 Orchard 54 Oak Ave 1700 E. Lincoln Ave Philadelphia 58 W. Central Ave Philadelphia PA Philadelphia PA Philadelphia PA PA PA Pettyjon Quinn Zecon JoyceExplanation / Answer
Hi, in order to write a query where two or more tables are involved, it's better to use joins.
There are several types of joins. Based on your requirements you should use them. Here, I am using just inner joins.
Here, I am writing this query assuming that credits will be updated based on orders table and books table.
QUERY:
UPDATE Credits
SET Number of Credits = Number of Credits + 1
FROM Credits
INNER JOIN Orders
ON Credits.Student ID = Orders.Student ID
INNER JOIN Books
ON Orders.Book ID = Books.Book ID;
Explanation:
Here, we are updating the 'Number of Credits' column by 1 of Credits table based on the Student ID column of Credits and Orders table and Book ID column of Orders and Books tables.
This is joined query used when two or more tables are involved in a transaction. Means when there is a dependency between two or more tables we use joins. Hope you understand.
Please do comment in case of queries. Thanks!!!
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.