Access Crosstab Weighted Calculation

0
votes

I have a table, Grades, where three fields are entered: StudentID, TestType (Quiz 1, Quiz 2,Final Exam), and Test Score. Therefore, if there were 3 quizzes and a final exam given for a class, there will be four rows for that student, one for each TestType.

A crosstab query from the wizard did a great job of generating a comprehensive Grade Sheet (TestType is my row heading), with one row per student that organize all their scores nicely under separate columns for Quiz 1, Quiz 2, etc:

TRANSFORM Avg(Grades.Score) AS AvgOfScore SELECT Grades.StudentID, Avg(Grades.Score) AS [Score Average] GROUP BY Grades.StudentID PIVOT Grades.TestType;

While a score average could be great, the grades are weighted. The final class grade is 50% quiz average and 50% final exam score. Is there a way to perform these calculation either in the crosstab query or in the report that will populate based on the query? I'm looking for an average of score when TestType like Quiz%, as well as avg([quiz score],[final exam])

Thanks in advance for any suggestions!!

1
ms-accesscrosstab
Please provide sample data and expected output based upon that data - Erik A
I added the criteria 'Like "Quiz*"' for TestType in my crosstab query which calculates the average field for all quiz scores correctly. Can I pull in the final exam score and overall class grade in the same query? - Virona
I would make a table of TestTypes and their weights.That table would give you the factors for each result - multiply the factors by bthe results, and then sum them up with your crosstab. - Don George
A table with weights would only work if every class had exactly the same number of quizzes and I would want something more dynamic than that in case it were to change. - Virona
@Erik von Asmuth How do I upload a file? - Virona

1 Answers

0
votes

Thanks for the insights and help! I ended up using the crosstab query with the TestType criteria set to pull only the quizzes, and then a second query to combine those scores with the final and a calculated field to generate the final class grade.