Results 1 to 4 of 4
  1. #1
    MichaelA is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    139

    Finding Weighted Grade Average

    I created a query that averages grades for all assignments in a course. Right now, it takes all assignment grades and simply averages them all. I would like to assign different weight to some assignments. (e.g. Make a chapter quiz worth more toward a grade than daily assignments.) Any suggestions on how to do this would be appreciated.

    I have tblAssignments which has fields AssignmentID_PK, CourseID_FK and Assignments; tblGrades with GradeID_PK, SudentID_FK, CourseID_FK, AssignmentID_FK and Grade. Grades are averaged using a query.



    SELECT tblGrades.StudentID_FK, tblStudents.FullName, tblGrades.CourseID_FK, tblCourses.Course, Avg(tblGrades.Grade) AS AverageGrade
    FROM tblStudents LEFT JOIN (tblCourses RIGHT JOIN tblGrades ON tblCourses.[CourseID_PK] = tblGrades.[CourseID_FK]) ON tblStudents.[StudentID_PK] = tblGrades.[StudentID_FK]
    GROUP BY tblGrades.StudentID_FK, tblStudents.FullName, tblGrades.CourseID_FK, tblCourses.Course;

    Any suggestions would be greatly appreciated.
    Attached Thumbnails Attached Thumbnails qryAvgGrade.JPG  

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    How would you identify an assignment as one of the 'special' ones that requires weighting? Possibly you need another table with the assignments and the additional value they should be assigned. What is your formula for this weighting? Must be able to do this manually in order to program an algorithm.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    MichaelA is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    139
    I was thinking of creating tblAssignmentWeight and then adding the AssignmentID_FK to tblAssignments. tblAssignments already exists. The tblGrades calls AssignmentID_FK. The formula:

    Average grade for assignments with a 10% weight * .1
    Average grade for assignments with a 15% weight * .15
    Average grade for assignments with a 25% weight * .25
    Average grade for assignments with a 50% weight * .5

    Adding these totals together would give the grade.

    I would also need to accommodate a course where the assignments are no weighted and every assignment carries the same value.


    Quote Originally Posted by June7 View Post
    How would you identify an assignment as one of the 'special' ones that requires weighting? Possibly you need another table with the assignments and the additional value they should be assigned. What is your formula for this weighting? Must be able to do this manually in order to program an algorithm.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Review the Similar Threads at the bottom of the page for ideas.

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

Similar Threads

  1. Calculating a weighted average
    By lugnutmonkey in forum Queries
    Replies: 2
    Last Post: 01-29-2013, 04:49 PM
  2. Weighted Average in SQL
    By Ashe in forum Forms
    Replies: 9
    Last Post: 10-11-2011, 12:46 AM
  3. Weighted-average inventory costing.
    By evander in forum Database Design
    Replies: 9
    Last Post: 01-03-2011, 08:32 AM
  4. Finding a weighted average
    By oldman in forum Queries
    Replies: 3
    Last Post: 09-10-2010, 08:41 PM
  5. Weighted Average - Challenges
    By edmund_xue in forum Access
    Replies: 0
    Last Post: 04-02-2008, 12:54 AM

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