Having given enough basic knowledge of Microsoft Access query in the previous parts of this tutorial chapter, let me now show you some of their practical or real life applications. In these applications, we will use functions like the IIF and AVERAGE functions, etc.

I will show practical illustrations with two systems namely:
1. Students’ Result System
2. Workers’ Payroll System

I will discuss students' result system in this part 4 of chapter 4 the MS Access tutorial and discuss workers' payroll system query in part 5 (last part of chapter 4).

TIPS:

Please try to type the expressions of functions used in these examples. Don’t copy the syntaxes as it will help to reduce syntax errors and also improve your rate of comprehension.

I will also give you some practice exercises which you should do on your own in the last part of chapter 4 (part 5).

You are advised to study part 1 of chapter 4: INTRODUCTION TO QUERIES IN MICROSOFT ACCESS 2013 AND 2016 at http://www.microsofttut.com/2017/03/introduction-to-queries-in-ms-access-2013-2016.html or study part 3 - SOME SPECIAL QUERY FUNCTIONS & EXPRESSIONS IN MS ACCESS & THEIR APPLICATIONS at http://www.microsofttut.com/2017/03/some-special-query-functions-in-ms-access-applications.html

You can also take a look at the previously treated chapters in this MS Access tutorial at http://www.microsofttut.com/p/course-content-for-microsoft-access.html
.
QUERYING THE STUDENTS’ RESULT SYSTEM (GRADE LIST) IN MS ACCESS 2013 & 2016
The Students’ Result system or Grade list is a database that contains students’ information on the various subjects the offered which includes the score in each subject, grade, total score, total grade, average, total average, etc.

You can apply query to filter some record that match your criteria. For example, you might want to see the records of students who performed very well in a particular subject, etc.

In the illustration below, you will see the IIF and AVERAGE functions in action.

PRACTICAL STUDENTS' RESULT SYSTEM QUESTION
Given that the marking scheme of Government Secondary School Owerri is as follows:
• 0 – 39.9 = F
• 40 – 49.9 = E
• 50 – 59.9 = D
• 60 – 69.9 = C
• 70 – 79.9 = B
• 80 – 100 = A
• Above 100 = ERROR

If 50 Students participated in this examination in the following subjects:
• Maths
• English
• Biology

1. Using the above marking scheme, calculate the following:
a. Total score
b. Average score
c. Grades on respective subjects
d. Overall grades

2. Filter all the students that made grade “A“ in English and Maths.

3. Filter all the female students that made grade “A” in Biology.

SOLUTIONS TO THE STUDENTS’ RESULT SYSTEM QUESTION
First, you have to create a table which will contain students’ information like ID, First name, Sex, Scores for each subject, Total score, Average Score, Overall grade, etc. Ensure that you specified the right data type for each field column.

While creating the table in design view, specify Number data type for columns that will contain numbers like the T SCORE, AVG SCORE columns, etc. Also specify Short Text data type for columns that will contain text like the FIRST NAME, SEX columns, etc. The data type for the ID column would be Auto Number as shown in the figure below.

Design view for the grade list table

Switch over to datasheet view and add 50 records to the table. Don’t add anything in the T SCORE, AVG SCORE and OV SCORE columns. Their values will be calculated in the query design view. A sample of the table is shown below.

Sample datasheet view of the grade list table

Now, using this table as data source, create a simple select query. Drag over all the fields into the query section as shown below. Now insert an empty column near each subject in the simple query environment.

To achieve this: click on the column whose immediate left you wish to insert the new column, then click Insert Column located in the Query Setup group under the Design tab.
For example, to insert a column between the ENG and MTH columns, click on the MTH column.

To enter your calculations on the appropriate columns, insert your cursor in the Field row. Type the column name for that column, then type the colon sign ( after the field name in the Field row and then begin to enter the appropriate syntax at the appropriate column and then press the enter key on the keyboard after entering each syntax. Check the box for each added field under the Show row.

1. Type the following syntaxes:
a. T SCORE: [MTH]+[ENG]+[BIO]
A sample of the query environment and the syntax is shown below.
Go to http://www.microsofttut.com/2017/03/query-practical-application-students-grade-list.html to study full tutorial with screen to screen images.