Sunday, 26 March 2017

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.
how to query students payroll system in ms access

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


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 hereAlso see the full tutorial course content of this Microsoft Access Tutorial Series.


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.


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:

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.


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 sample of the query environment and the syntax is shown below.
Sample syntax for the grade list query
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.
Right click and select zoom
Follow the same step for other syntaxes.

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"))))))) 


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"))))))) 

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. 
Query result for the grade list system
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.


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.

Format the calculated field
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

ms access textbook kindle format

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.

Recommended Entrepreneurship Books

the passionate entrepreneurs strategies book by buzzer joseph

The Passionate Entrepreneur’s Strategies: Learn how to discover business ideas that will be successful, grow multiple online income streams, make money from home, attract new and retain customers, applications of SWOT analysis in any business, skills that will help you succeed as an entrepreneur, self-discipline, etc. Lucrative Business Ideas Series Book 1. By Buzzer Joseph. Read more about the book.

how to make money from blogging and affiliate marketing book by buzzer joseph

How to Make Money from Blogging and Affiliate Marketing: Learn the Step by Step Guide of Creating, Growing and Optimizing your WordPress Blog from Scratch for Search Engines (SEO) and How to Make Money through Google AdSense, Affiliate Marketing and Other Strategies for Earning Passive Income Online. Lucrative Business Ideas Series Book 2. By Buzzer Joseph. Read more about the book.

how to make money self publishing kindle books by buzzer joseph

How to Make Money Self-Publishing Kindle Books for Beginners: Learn the Step by Step Guide to Self-Publish a Best Selling Book on Amazon KDP, How to Design Book Covers with Adobe Photoshop, Format, Edit and Proofread your Book Manuscript with Microsoft Word, Promote your Book on Various Online Platforms and Earn Monthly Passive Income Online. Lucrative Business Ideas Series Book 3. By Buzzer Joseph. Read more about the book.

google classroom and zoom video online conferencing guide by buzzer joseph

Google Classroom and Zoom Meeting for Beginners: Learn the Step by Step Guide with Screenshots on How to Use Google Classroom as a Business Owner, Teacher or a Student. Also Learn how to Use Zoom for Online Meeting, Web Conferencing, Video Conferencing and Webinars in Mobile Devices, PC and Mac. Make Distant Teaching and Learning a Fun! Lucrative Business Ideas Series Book 4. By Buzzer Joseph. Read more about the book.

how to make money from cryptocurrency trading and investments by buzzer joseph

How to Make Money from Crypto Trading and Investing for Beginners: Step by step guide with screenshots on the basics of blockchain technology, bitcoin and altcoins, crypto security tips, cryptocurrency market analysis (FA and TA), long term and short term trading, how to calculate your profits in excel, legit ways to make money with cryptocurrencies, DeFi yield farming and staking, how to use any centralized and decentralized exchange platform and more, plus crypto investment bonus! Lucrative Business Ideas Series Book 5. By Buzzer Joseph. Read more about the book.

No comments:

Post a comment