Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    MichaelA is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    139

    Assigning a Letter Grade to a number

    I have a query look up StudentName, Course, Average Grade from a table called Grades. I have another table called LetterGrades with fields:

    lowerlimit upperlimit LetterGrade
    0 59 F
    60 62 D-
    63 66 D

    etc.

    I want my query to take the average grade from the grades table and in another field show the corresponding letter grade.

    Any help would be greatly appreciated. Thanks in advance,


    Michael

    p.s. Access is the hardest computer program I've ever tried to tackle.

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try this in a SQL view of a new query, fixing names as appropriate:

    SELECT StudentName, Course, AverageGrade, LetterGrade
    FROM Grades LEFT JOIN
    LetterGrades ON Grades.AverageGrade >= LetterGrades.lowerlimit AND Grades.AverageGrade <= LetterGrades.upperlimit

    Note the join can't be represented in the design grid so can only be worked with in SQL view
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    MichaelA is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    139
    I'm sorry, I didn't convey accurately. The average grade field does not exist in the Grades table. Grades for individual assignments are in Grades.Grade. The average is done within the query based on StudentName and Course.

  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
    I suggest you show us your tables and relationships (relationship window) so we can see the tables involved.

  5. #5
    MichaelA is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    139
    What would be the best way to do that?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can use Advanced Editor window to build tables in post. Can even copy/paste records from Access table into post. Or attach db for analysis - follow instructions at bottom of my post.
    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.

  7. #7
    MichaelA is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    139

    Attachment

    Here's a zipGradebook.zip file of the database.

    And, thank you!

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the grades table is unpopulated - what should we expect to see there?

    And with regards average - average of to what level? by student is a given (I think) but by year?course? assignment?

    perhaps provide some example data from the grades table and the outcome required for average

  9. #9
    MichaelA is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    139
    Sorry, I deleted all that data because it had been test runs on the form and I was ready to input real data. I will enter some examples and reattach the db.

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

    New Attachment

    I populated the grade table with data. When the student's grade query is run, it searches for an individual student, sorts all the assignments by subject and returns the students name, grade level, subject and an average for all of the assignments in each subject.
    Attached Files Attached Files

  11. #11
    MichaelA is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    139
    Also, is there a simple way of setting up one grade query in which you could easily change the name and grade level to get a different student's course grade average?

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you are now using additional terminology - I take it subject is the course?

    your basic query to determine the average per student would be

    Code:
    SELECT StudentID, Course, Avg(Grade) as AverageGrade
    FROM Grades
    GROUP BY StudentID, Course
    save this query and call it say qryAvgGrades

    then in a new query, modify the code provided by Paul to use this query

    Code:
    SELECT StudentID, Course, AverageGrade, LetterGrade
    FROM qryAvgGrades LEFT JOIN LetterGrades ON qryAvgGrades.AverageGrade >= LetterGrades.lowerlimit AND qryAvgGrades.AverageGrade <= LetterGrades.upperlimit
    Note you are using lookups in your table design. These will cause you problems - as you will discover in time. Better not to use them so you know exactly what your data looks like and how you need to construct your queries, forms and reports.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't build a query and report for each student/year. Build one query and use dynamic filter criteria when opening report.

    Using Paul's suggested query, following would be RecordSource of report:

    SELECT FirstName, LastName, Course, AvgOfGrade, LetterGrade, SchoolYear FROM qryGrades LEFT JOIN LetterGrades ON (qryGrades.AvgOfGrade >= LetterGrades.lowerlimit) AND (qryGrades.AvgOfGrade <= LetterGrades.upperlimit);
    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.

  14. #14
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    A
    lso, is there a simple way of setting up one grade query in which you could easily change the name and grade level to get a different student's course grade average?
    the query provided provides the average for all students/courses so just apply a filter (usual way would be a subform or listbox on a form).

    Note in the query I provided, I missed out schoolyear so the average is across all years, just add the school year to the query in the query grid to split by year as well (isn't clear to me if this is the academic year or the student form year)

  15. #15
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    qryPaulTest:
    SELECT Grades.StudentID, Grades.Course, Avg(Grades.Grade) AS AverageGrade, Students.FirstName
    FROM Students LEFT JOIN Grades ON Students.ID = Grades.StudentID
    GROUP BY Grades.StudentID, Grades.Course, Students.FirstName;

    SELECT FirstName, Course, AverageGrade, LetterGrade
    FROM qryPaulTest LEFT JOIN LetterGrades ON qryPaulTest.AverageGrade >= LetterGrades.lowerlimit AND qryPaulTest.AverageGrade <= LetterGrades.upperlimit


    note the gap in your letter grades.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 05-04-2018, 12:17 AM
  2. Replies: 7
    Last Post: 04-23-2018, 02:11 PM
  3. Replies: 6
    Last Post: 01-16-2014, 12:41 PM
  4. Replies: 4
    Last Post: 09-18-2012, 05:07 AM
  5. Assigning a Lot number to table of information
    By mmirandola in forum Database Design
    Replies: 4
    Last Post: 05-24-2012, 08:21 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