# Complex Multiple Math Calculations in a Query

1. 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.  Reply With Quote

2. 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!  Reply With Quote

3. 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.  Reply With Quote

4. Expert Windows 8 Access 2016    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
(  Reply With Quote