Results 1 to 4 of 4

Complex Multiple Math Calculations in a Query

  1. #1
    MichaelA is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    92

    Complex Multiple Math Calculations in a Query

    I've attached a picture of a query datasheet to help describe what I need to accomplish. The query, as is, showsGradeID_FK, StudentID_FK, CourseID_FK, AssignmentID_FK, AssignmentWeightID_FK, Grade, and AssignmentWeight.

    Some of the AssignmentWeightID_FK values are the same, I first need to bundle them together by adding the grades together then dividing by the number of matching AssignmenntID_FK values and multiplying that answer by the AssignmentWeight.

    In this example, (65+85+90) /3 * 0.1 =8.

    I would need to do this for any other rows which share an AssignmentWeightID_FK in common.

    Here (86+82) /2 *0.15 = 12.6.

    Next, I would need to multiply grades which do not share an AssignmenWeightID_FK with other grades.

    In this example 85*0.25=21.25. Next, I would need to add these totals together.

    8+12.6+21.25=41.85

    This sum would then be divided by the sum of all the AssignmentWeights involved.

    41.85 / (0.1+0.15+0.25) = 83.7

    A couple of other things. The rows combined would need to be for the same Student and the same Course. Other students and other courses will eventually be added to the table and will need to be calculated as well.



    Also, there will eventually be other AssignmentWeightID_FK and AssignmentWeight values in the table.

    I hope this makes sense.

    Thank you for your help!

    Click image for larger version. 

Name:	Capture.JPG 
Views:	14 
Size:	35.8 KB 
ID:	39957

  2. #2
    MichaelA is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    92
    I can't believe it but I got this figured out. I used 4 queries to get it done, accomplishing each step in the math with each successive query. If anyone has a better way, I'm game but for now, I have it working. Yahoo!

  3. #3
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,360
    You could possibly have used a function to get to the same result, but using queries is as good a method as any, and will be easily transferable if you upscale to another DMBS like SQL server.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    Join Date
    Apr 2017
    Posts
    996
    Something like
    Code:
    SELECT wag1.StudentID_FK, wag1.CourseID_FK, SUM(wag1.GradeAvg)/SUM(wag1.WeightAvg) AS WhateverName FROM
    (
    SELECT wag0.SudentID_FK, wag0.CourseID_FK, wag0.AssignmentWeightID_FK, AVG(wag0.AssignmentWeiht) AS WeightAvg, AVG(wag0.Grade)*AVG(wag0.AssignmentWeight) AS GradeAvg FROM qryWeightedAssignGrades wag0 
    GROUP BY wag0.SudentID_FK, wag0.CourseID_FK, wag0.AssignmentWeightID_FK
    ) AS wag1
    GROUP BY wag1.StudentID_FK, wag1.CourseID_FK

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

Similar Threads

  1. Replies: 19
    Last Post: 09-05-2014, 06:13 AM
  2. Replies: 1
    Last Post: 02-13-2014, 01:50 AM
  3. Replies: 4
    Last Post: 07-27-2012, 10:48 AM
  4. Replies: 7
    Last Post: 09-12-2011, 12:03 PM
  5. complex calculations like in Excel
    By jacko311 in forum Database Design
    Replies: 2
    Last Post: 11-11-2009, 05:51 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
  •  
Tech Forums: Microsoft Office Forums