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.

In the last part (part 4) at http://www.microsofttut.com/2017/03/query-practical-application-students-grade-list.html, 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 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 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 WORKERS’ PAYROLL SYSTEM IN MS ACCESS
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.

DEFINITION OF SOME TERMS USED IN PAYROLL MANAGEMENT

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.

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

BASIC SALARY:
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.

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

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

GROSS PAY:
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.

NET PAY:
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.

PRACTICAL ILLUSTRATION OF A PAYROLL SYSTEM
QUESTION:
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

SOLUTION TO THE PAYROLL SYSTEM QUESTION
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.

Visit http://www.microsofttut.com/2017/03/query-practical-application-in-workers-payroll-system.html to study full post with screen to screen pictures.