Results 1 to 10 of 10
  1. #1
    jadams0304 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    5

    Update Query with three joined tables

    I've been working on this for a couple hours trying Query Wizard, Query Design and SQL. Everything I've tried so far either doesn't give me the result I'm looking for or I get an error.



    Below are my relationships:


    Click image for larger version. 

Name:	Capture.JPG 
Views:	13 
Size:	53.8 KB 
ID:	29229

    This is what I currently have, but it's my last effort. I've tried dozens of other attempts.

    UPDATE Credits INNER JOIN (Books INNER JOIN Orders ON Books.[Book ID] = Orders.[Book ID]) ON Credits.[Student ID] = Orders.[Student ID] SET Credits.[Number of Credits] = Sum([books].[Credits])WHERE (((Books.[Book ID])=[Orders].[Book ID]) AND ((Credits.[Student ID])=[Orders].[Student ID]));

    This seems like it should be pretty easy task. Can somebody please help me?

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Why do you need to store this calculation. Normally, calculations are better done whenever and wherever they are needed.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    jadams0304 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    5
    Below is the task I'm trying to achieve:

    Click image for larger version. 

Name:	Capture1.JPG 
Views:	14 
Size:	13.3 KB 
ID:	29230

    I probably don't need to. It seems like a "catch 22" for me. No matter what I do I run into an issue, because of the query design options. For example, I can get the credits to sum the way that I want them to by "Student ID" if I use the "Totals" option while using a Select query, but then when I change Select to Update the "Totals" option goes away so I'm not longer doing a sum of the credits.

    Maybe I'm not reading the task correctly?

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I probably don't need to.
    So why do it then.

    Maybe I'm not reading the task correctly?
    Who set the task
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    jadams0304 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    5
    This is a homework assignment. I'm not looking for anybody to provide me with the answer. I'm seeking guidance.

    Given that, I think it's fairly obvious that a "stored calculation" is over my head.

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Can you post a copy of your db for us to play with
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    jadams0304 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    5
    I would, but the DB is 705 KB and the file size limit is 500 KB.

  8. #8
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Compact and compile. Then zip it
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  9. #9
    jadams0304 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2017
    Posts
    5
    Thanks, see attached. There were a lot of other queries/tables/forms/reports in there, but I deleted them to reduce the file size.

    Assignment.accdb

  10. #10
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    "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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 09-13-2016, 07:31 AM
  2. Issue with joined tables in a query
    By Magnolia1 in forum Access
    Replies: 2
    Last Post: 05-13-2016, 04:51 PM
  3. Counter in a query with joined tables
    By plus_stick in forum Queries
    Replies: 1
    Last Post: 10-30-2014, 06:36 AM
  4. Replies: 8
    Last Post: 06-04-2014, 10:01 AM
  5. Replies: 5
    Last Post: 08-07-2009, 05:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums