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

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