Practical Application Of Ms Access Query Functions In Students’ Result System - Part 4

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 or study part 3 here. Also see the full tutorial course content of this Microsoft Access Tutorial Series.

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 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.

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.

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.

If Access prompts syntax error dialogue box, zoom the syntax and cross check what you typed. After cross checking the syntax, click OK and then press the Enter key.

To apply zoom: Right click inside the syntax and select Zoom as shown below.

Follow the same step for other syntaxes.

b. AVG SCORE: [T SCORE]/3

c. For the various subjects grade, type the following syntaxes in the appropriate columns:

i. ENG GRADE: IIf([ENG]<40,"F",IIf([ENG]<50,"E",IIf([ENG]<60,"D",IIf([ENG]<70,"C",IIf([ENG]<80,"B",IIf([ENG]<=100,"A",IIf([ENG]>100,"ERROR"))))))) 

NOTE: 

If you don’t understand the IIF function expression above, go to Chapter 3, where I explained IIF function in detail.

ii. MTH GRADE: IIf([MTH]<40,"F",IIf([MTH]<50,"E",IIf([MTH]<60,"D",IIf([MTH]<70,"C",IIf([MTH]<80,"B",IIf([MTH]<=100,"A",IIf([MTH]>100,"ERROR")))))))

iii. BIO GRADE: IIf([BIO]<40,"F",IIf([BIO]<50,"E",IIf([BIO]<60,"D",IIf([BIO]<70,"C",IIf([BIO]<80,"B",IIf([BIO]<=100,"A",IIf([BIO]>100,"ERROR"))))))) 

d. OV GRADE: 

IIf([AVG SCORE]<40,"F",IIf([AVG SCORE]<50,"E",IIf([AVG SCORE]<60,"D",IIf([AVG SCORE]<70,"C",IIf([AVG SCORE]<80,"B",IIf([AVG SCORE]<=100,"A",IIf([AVG SCORE]>100,"ERROR")))))))

Now switch over to datasheet view. You will notice that Access has generated the values for the calculated fields as shown below. 

Try to enter more records. What did you notice when you came over a calculated field?

2. Switch back to query design view. On the criteria row for the ENG GRADE field type letter “A”. Do the same for the MTH GRADE. Click the Run icon to view the records that match the set criteria. You can also save this query result as a new query file.

3. Switch back to query design view. On the criteria row for the SEX field type “Female”. On the same row under the BIO GRADE field, type letter “A”. Click the Run icon to view the records that match the set criteria. You can also save this query result as a new query file.

HOW TO FORMAT A CALCULATED FIELD 

You can change the default properties of a calculated field to your own taste. You can add a description, set the data format, specify the number of decimal places, set input mask, add caption, set lookup for a calculated field.

You will notice from the image above that the query result in the AVG SCORE column is not fixed. You can correct this and set it to any number of decimal places of your choice.

To set the number of decimal place of that calculated column or field:

1. Go back to query design view, 
2. Right click on that calculated field and select Properties.
3. This prompts the Property Sheet dialogue at the extreme right of the screen. 
4. Note that you can also call up the Property Sheet by Clicking the Design tab and then clicking Property Sheet in the Show/Hide group as shown below.
5. The Property Sheet has two tabs: General and Lookup tabs. Click the Format property under the General tab and change the format value to Fixed.
6. Also click in the Decimal Places property and enter 2 or any number decimal places you want for that calculated field as shown below.

7. Save your work and go back to datasheet view, you will notice that all the values in that calculated field now has the number of decimal places you set in the property sheet.


You can also try to format the calculated fields further on your own.

That is all about querying the students’ grade list system. You can try some other filters on the database.

Recommended MS Access Textbook

Get this book (Kindle format): Designing Professional Database Management Systems Using Microsoft Access 2013 and 2016 and MySQL.

Click Here to know more about the book.

Now let’s go over the second illustration, which is the payroll system (part 5).

Inform your friends about this post by clicking the share button below. Comment below if you are hooked up along the installation process.

Also click Here to subscribe for free so that you will get our latest game updates in your email.