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.

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

NOTES:

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

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

i. TRANSPORT ALLOWANCE: [BASIC SALARY]*6/100

ii. FEEDING ALLOWANCE: [BASIC SALARY]*4/100

iii. DRESSING ALLOWANCE: [BASIC SALARY]*5/100

iv. HOUSING ALLOWANCE: [BASIC SALARY]*8/100

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

e. GROSS PAY: 
[BASIC SALARY]+[MARITAL ALLOWANCE]+[TRANSPORT ALLOWANCE]+[FEEDING ALLOWANCE]+[DRESSING ALLOWANCE]+[HOUSING ALLOWANCE]

f. NET PAY: [GROSS PAY]-[TAX]

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.

HOW TO APPLY CONCATENATION IN A QUERY CALCULATED FIELD EXPRESSION

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.

PRACTICAL EXERCISES ON 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.

No comments:

Post a Comment

WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!