Practical Application Of Ms Access Query Functions In Workers’ Payroll System - Part 5

A payroll system is the compilation or list of workers’ information and their payment details in an organization or company. It is a database containing all workers information and their payment details such as basic salary, allowances, gross pay, and net pay.
how to query workers payroll system in ms access

In this part 5 of the chapter 4 of MS Access, I will explain how to use all the query functions and expressions to filter workers' payroll system in Access 2013 and 2016. I will also show you how to easily apply CONCATENATION in a query calculated field.

In the previous part (part 4), I illustrated the practical applications of MS Access query functions in Students’ result system (Students’ Grade List). In this part 5, I will illustrate the business applications of these query functions in workers’ payroll system.


You may wish to filter or extract some records that match your criteria. For example, you might want to see the number of workers with a certain qualification like MSc, BSc, etc. In such cases, MS Access helps to simplify the protocols for you especially in big organizations where there are very large number of workers.

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


As we move along in this illustration, we will be using some payroll terms. So I will like to define some of these terms so that you will become used to them.


This is a detached leaflet or a record from the payroll that contains a worker’s information and payment details.


This is an amount or sum of money a worker is entitled to at the end of the month in a company or organization which is highly influenced by worker’s qualifications and level, and also by the strength of the organization.


This is a fringe benefit that is allocated to every worker in order to encourage workers and enhance their performance in the company or organization.


This is a compulsory levy that is deducted from a worker’s basic salary at the end of the month in order to generate revenue for the state and federal government.


This is the summation of or the total of a worker’s basic salary and all the allowances he is entitled to at the end of every month.


This is Gross pay minus all deductions such as tax and loans. It is the final take-home of a worker at the end of the month.



1. The question is as follows:
The manager of JOE-LINKS SERVICES wants to computerize the payroll system of the company. 

a. As a spreadsheet expert, design a payroll system to his recommendation and approval to pay his workers on monthly basis such that basic salary is influenced by qualification as follows:

WASSCE = $20,000
OND = $25,000
NCE = $28,000
HND = $32,000
BSc = $40,000
MSc = $50,000
PhD = $80,000 respectively.

b. Marital allowance would be 8% and 10% for single and married workers respectively.

c. Transport, Feeding, Dressing and Housing allowances would be 6%, 4%, 5%, and 8% of their basic salaries respectively.

d. Compute a tax deduction such that: 
WASSCE = 5%, OND = 6%, NCE = 8%, HND = 10%, BSc = 12%, MSc = 15% and PhD = 20% of their basic salaries respectively.

e. Calculate the Gross pay and Net pay for each worker.

f. Setup an advanced query or filter to filter all workers that earn above $30,000.

g. Also filter al single workers with qualifications of MSc

First, you have to create a table which will contain workers’ information like ID, First name, Qualification, etc. You may wish to abbreviate these column titles but make sure that you are consistent when referring to them in the query section. Also ensure that you specified the right data type for each field column.

We will need other columns like: Basic salary, Transport, Feeding, Dressing and Housing allowances, Tax, Gross pay and Net pay. But we will generate these columns in the query environment.

While creating the table in design view, specify Number data type for columns that will contain numbers. Also specify Short Text data type for columns that will contain text like the FIRST NAME, Marital status, Qualification columns, etc. The data type for the ID column would be Auto Number as shown in the figure below.
Design view for payroll table
Now, using this payroll table as data source, create a simple select query. Drag over all the fields into the query section as shown below.

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.

Now put your cursor on the next column after the QUAL column and type the following syntax and press the ENTER key on your keyboard (remember to check the Show box and do the same for subsequent calculated fields).

1. Type in the following syntaxes in the appropriate calculated fields:

a. BASIC SALARY: IIF([QUAL]="WASSCE",20000,IIF([QUAL]="OND",25000,IIF([QUAL]="NCE",28000,IIF([QUAL]="HND",32000,IIF([QUAL]="BSc",40000,IIF([QUAL]="MSc",50000,IIF([QUAL]="PhD",80000,0)))))))

A sample of the query environment and the syntax is shown below.
Sample syntax for the payroll query


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

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
Now put your cursor in the next column after this BASIC SALARY calculated field and type the following syntax (follow this trend for others):

IIF([M STATUS]="Single",[BASIC SALARY]*8/100,IIF([M STATUS]="Married",[BASIC SALARY]*10/100))

c. For the other allowances, type the following syntaxes:





d. TAX: 
IIf([QUAL]="WASSCE",[BASIC SALARY]*5/100,IIf([QUAL]="OND",[BASIC SALARY]*6/100,IIf([QUAL]="NCE",[BASIC SALARY]*8/100,IIf([QUAL]="HND",[BASIC SALARY]*10/100,IIf([QUAL]="BSc",[BASIC SALARY]*12/100,IIf([QUAL]="MSc",[BASIC SALARY]*15/100,IIf([QUAL]="PhD",[BASIC SALARY]*20/100,0)))))))



Now cross check the syntaxes you entered for each column and then run your query by clicking the Run icon in the Results group of the Design tab. You will notice that you now have 13 columns, 9 calculated columns with the values for each field have been generated by the syntaxes you entered in the query design view as shown below.
Try to enter more records. What did you notice when you came over a calculated field? Save your query with a unique file name.

g. For the first filter, switch back to query design view. On the Criteria row for the NET PAY field, type 30000. Then run your query. You may also wish to save this query filter with a unique file name.

h. For the second filter, switch back to query design view. On the Criteria row for the M STATUS column, type (in quotes) “Single”. Also, on the criteria row for the QUAL column, type (in quotes) “MSc”. Then run your query to view the result. You may also wish to save this filter with a unique file name.

That is all about querying worker’s payroll system in MS Access. You can try some other filters on the database.


Concatenation simply means “joining”. You can create a new calculated field when building or constructing your simple select query which will contain values from two previously created field by using concatenation.

For example, a new calculated field called Full Name will contain the first name and surname joined together with a space separating them. The ampersand character (&) is used for concatenation or joining two strings in MS Access Queries.

To Concatenate Two Previously Created fields in a New Calculated Field:

1. Click in an empty Field cell in Query Design View.

2. Type the name of the newly-calculated result, a colon, and the formula you wish to calculate.  Refer to any fields in your calculation by entering their names surrounded by square brackets.  Incorporate any text that will not change (including spaces) inside quotation marks.

3. Use an ampersand character (&) to join any two strings of text in your expression (one fields with another, the contents of a field with text in quotations, etc.)

For example, the full expression for a new calculated field called Full Name will be: Full Name:[Employee First Name]&“ ”&[Employee Last Name]

4. Save your query and use the View button to check the results and debug your calculation if necessary.

Now let’s give you some practical exercises. Please try to practice them on your own as it will help broaden your knowledge about query.


1. Queen’s College, Lagos wants to computerize her students result in the following subjects:
English, Mathematics, Chemistry, Physics, Biology, Economics, Literature and Government.

a.) As a Spreadsheet Manager, design a result System to compute the Total Score and Average Score. 

b.) Using IF function, generate the grade letters for each subject and the overall grade using the following grade Scheme:

0 – 39.9 = Fail
40 – 49.9 = Pass
50 – 59.9 = Credit
60 – 69.9 = Upper Credit
70 – 100 = Distinction

c.) Generate 30 records and Set up an advance filter to filter Science Students from Lagos State.

2. a.) Diamond Bank PLC wants to computerize her worker’s payroll system to compute the Basic Salary based on the following qualifications:

SSCE = $1200
NCE = $1300
HND = $1500
BSc = $1800
MSc = $2200
PhD = $2800

b.) Compute 8%, 12%, 7.5%, 6% and 5% of the Basic Salary as Housing, Transport, Medical, Feeding and Dressing allowances. 

c.) Tax deduction should be calculated as follows:

SSCE = 1.5%
NCE = 2%
HND = 2.5%
BSc = 3%
MSc = 3.5%
PhD = 4%

d.) Calculate the Gross and Net Pay.

e.) Set up an advance filter query to filter the following:

i. All HND holders
ii. All the workers that earn above $35000

With this you have covered almost everything you need to know about querying a database in MS Access. This is just the simple part of MS Access query. 

Recommended MS Access Textbook

ms access textbook kindle format

Click Here to know more about the book.

In the next chapter (chapter 5), I will treat Advance Access, where I will show you how to construct advance queries like parameter query, data definition queries, delete queries, etc.

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.

diy grid and off-grid solar pv installation guide by buzzer joseph

Complete Solar PV System Installation Guide: DIY guide on how to install grid-connected and off-grid solar PV systems, how to build solar-powered electric cars, convert conventional cars to electric cars, online and offline ways to make money from solar energy sector (including stocks), electrical wiring and protection system installation guides, plus free resources for skill acquisition and personal development. 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.

blockchain practical guide kindle book by buzzer joseph

Blockchain Practical Guide: Step by step guide with pictures, on how to monetize the latest blockchain technology trends: crypto trading and tools, especially Ethereum, DeFi, Staking, NFTs, Top 100 Play-to-Earn (P2E) and NFT games, Web 3.0, Metaverse, Virtual Reality (VR), Augmented Reality (AR), blockchain job opportunities, with crypto investment bonus! Lucrative Business Ideas Series Book 6. By Buzzer Joseph. Read more about the book.

how to make money with blockchain web3 apps kindle book by buzzer joseph and kelly joseph

How to Make Money with Blockchain Web3 Apps: Step by Step Guide with Screenshots and Videos on How to Make Money from Blockchain and Web3 Projects – NFTs, Play-to-Earn (P2E) Games, Move-to-Earn (M2E) Apps, Watch-to-Earn (W2E) Apps, SocialFi and GameFi Apps, with crypto investment bonus! Lucrative Business Ideas Series Book 7. By Buzzer Joseph and Kelly Joseph. Read more about the book.

No comments:

Post a Comment