If you don’t understand the IIF function expression above,
go to Chapter 3, where I explained
IIF function in detail.
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.
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
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]
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
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
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
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,
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