Complex Multiple Math Calculations in a Query

1. Competent Performer
Windows 10 Access 2013 64bit
Join Date
Sep 2019
Location
Wisconsin
Posts
132

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!

2. Competent Performer
Windows 10 Access 2013 64bit
Join Date
Sep 2019
Location
Wisconsin
Posts
132
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. VIP
Windows 10 Access 2016
Join Date
Sep 2017
Location
UK - Wiltshire
Posts
1,377
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.

4. VIP
Windows 8 Access 2016
Join Date
Apr 2017
Posts
1,101
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```

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 - Senior Forums