7
Running a Query / Modifying a Query
1. Select "Queries", double-click "BASIC-PAYROLL-QUERY"
(note from author: There are at least (2) ways to learn: One
is by Rote , following orders literally, explicitly; the 2nd is "experimenting-to-learn ",
trying, pushing the 'edge' all the time.
I am using Rote to get you started, to help you "get" the structure.
Then , experimenting, making sure that you can re-trace your steps.
Experimenting will create Success which builds Confidence which gives
Empowerment which gives Mastery. Do you understand? Don't be
afraid to experiment.)
2. Double-clicking "BASIC PAYROLL QUERY" will generate this screen!
3. Now we experiment a bit. The theory is "If the Query is
based on the Table, then changing the Table should produce new results
with the Query." Let's test it: Access the table
"PAYROLL-TABLE" in Datasheet view, change one of the PAYRATE amounts to
100, exit, access Query, and run BASIC PAYROLL QUERY to test your
hypothesis. Did it work? If not, carefully retrace your
steps.
4. Now we will expand the Query to account for Overtime Hours,
which, as you know, are paid at time-and-a-half. Review
Payroll Calculator
to ensure you understand how Overtime calculations work. Up
to the 1st 40 hours are at Regular Rate, and over 40 are 1.5 times Regular
Rate. Let's build on our BASIC PAYROLL QUERY query to include
Overtime Pay.
5. First, open PAYROLL-TABLE, and change the HOURS and PAYRATE fields as
shown.
(To
make it easier to see the OT calculations.)
6. We want to pay employees their Reg Pay for their 1st 40 hours,
right?
Calculating Regular Pay:
If someone works < 41 hours, his Gross is his number of Hours * Payrate.
If someone works > 40 hours, his Gross is his Payrate * 40 (for the 1st 40
hours that he worked!)
Next, open up BASIC-PAYROLL-QUERY in design view, and put this
expression into the 6th column:
Type the statement -
REGPAY:IIF(HOURS>40,PAYRATE*40,PAYRATE*HOURS)
carefully into the form, noting that IIF (Immediate IF)
is capitalized, with ( ,
) commas between the conditions! Explained below:
REGPAY:I If
( HOURS > 40 , 40 * PAYRATE ,
HOURS * PAYRATE )
! ! !
!
! - - - - No. Result if not
true
! ! !
! - - - - - - - - - - - YES. Result if true
! !
! - - - - - - - - - - - - - - - - - Condition Tested.
! ! (Is
"HOURS" > 40?)
! ! - - - - - -
- - - - - - - - - - - - - - - -
I mmediate
IF
statement
!
- - - - - - - - - - - -
- - - - - - - - - - - - - User-defined Field Name
Restating: REGPAY is a field
name that you create; you know what IIF stands for, HOURS>40 is the
condition you are testing for, and
if True (
over 40 hours), REGPAY = 40 * PAYRATE
and
if False (not over 40 hours),
REGPAY = HOURS * PAYRATE
(this is the same as the =IF statement in EXCEL)
Calculating Overtime Pay
Write the expression to generate OTPAY. (Note that CHARLIE received
15 for 1 hour of Overtime.)
Entering this IIF statement for OTPAY
OTPAY: IIf([HOURS]>40,([HOURS]-40)*1.5*[PAYRATE],0)
Generates OTPAY shown above.
(Access will accept either iIF or IFF, and the brackets can be
omitted.)
Calculating Gross Pay
Step-by-step: Open the query which is called OT-PAYROLL-QUERY in
design view. In the next available column, type GROSSPAY:
REGPAY+OTPAY . Note EASY with 43 hours earns 40 hours @ $10 and 3
hours @ $15 for a total of $445. Exit, save, then Run the Query by
double-clicking it... Verify that your results equal the ones above.
Calculating NET PAY
Go to the Field row (top row) of OT-PAY-QUERY.. and the
next column. Using NETPAY
as the result field, write
the expression to calculate it. No spaces, all capital letters, don't use
brackets around field names.