Generating Grade Point Average (GPA) in a Report 1

[chatfield]

1. I am developing a student transcript report that will show a student's
grades in each of 4 subjects for each quarter of reporting.

2. In addition a.) the report has to show the average GPA across all 4
subjects for each quarter; b.) it also has to show the average
GPA for each subject across all 4 quarters.

3. My table is designed so that each record contains a unique student ID
number; a unique subject code for each of the four subjects; and
a letter grade (with a lookup numerical value) for each of the four
marking periods. Therefore, each student has four records in the
table.

4. I want to base the report on a query that will call a function to a.)
loop through each ID and subject code for a given marking
period and return an average GPA for each that quarter; and create
another one to b.) that will generate an average GPA for each
subject across each of the four quarters.

5. What is the most efficient looping procedure to use???????

Your help will be of great assistance.

chatfield

 

[JerryDennison]

Looping is unnecessary. Use a totals query and define the Group levels you need. For example, to have an average GPA for each student and subject. Include the StudentId, SubjectID and numeric grade fields in the Query. Press the Sum button on the toolbar. Leave the StudentID and SubjectID total property as Group By and change the numeric grade total property to Average. This will give you the average grade for each subject a student takes. You may then base your report on this query. If you need to define a date range, include the date field and change it's total property to Where; then put the criteria in the criteria property. You can use parameters in the criteria so the user is prompted to enter a date range each time the query/report is run. To average the students entire grade leave off the SubjectID.