---

MS Access 2003: Complex set of queries that involve calculating the pass rate for each subject

This MSAccess tutorial explains how to set up a complex set of queries that involve calculating the pass rate for each subject in Access 2003 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, I have two tables - Subjects and Grades. A grade can be an A*, A, B, C, D, E, F, or G value. A passing grade is from A* - C.

I need to create a query that calculates the pass rate for each subject.

Answer: We've created a sample Access database that you can download that contains the queries used to calculate the pass rate for each subject.

Download version in Access 2000

First, it is important to note that calculating the pass rate for each subject is too complex to be done in a single query. In fact, it takes three queries to generate these results.

The first query is called "Grade totals by subject".

This query calculates the total number of grades for each individual subject (ie: If you have 10 students enrolled in Math, then it will return 10 for the subject of Math).

The second query is called "Pass totals by subject".

This will return the number of passes per subject. A pass is defined as a grade of A*, A, B, or C.

The third and final query is called "Pass rates by subject".

This one will return the pass rates for each subject. For example, if 8 out of 10 people passed in Math, then the query will return 0.8 for Math, representing a 80% pass rate.

Remember that a query can use both tables and/or other queries to create its results.