Report with data manipulation:

I have created a table where the name is in separate parts, a code must be tested and if the person is a Hourly worker then pay must be calculated. I want to use this information as I create a report.

Below is the layout of the table and the data.

Idno

FirstName

MidInit

LastName

PayCode

Salary

PayHr

HoursWk

120123456

Kevin

A

Brown

H

25

25

123456789

Susan

B

Ash

S

50000

0

0

124567890

John

C

Reynolds

H

0

25

40

125678901

Linda

Costa

H

0

20

25

126789012

David

M

Rogers

S

45000

0

0

127890123

Roger

T

Jones

H

0

35

50

128901234

Jane

M

Brooks

H

0

30

45

129012345

Patricia

Wilson

H

0

25

50

Now I am going to create a report. First I set up the header and put the date on the report by using the Control Source of a text box and putting in = Date( ). Then I used label boxes to put in the column headers. Remember that you can use the label box associated with the data or you can use separate label boxes for the column headers. In this case I choose to use separate label boxes. After I got them in and looked at them I decided that I wanted to increase the size. I used the cursor to highlight the whole group. The property box then showed Multiple Selection in its header. I went down in the properties until I found Font Size and I changed it to 10. This changed the font for all of the text boxes that I selected as a group. After looking at the results shown below, I realized that the change in font size had made it necessary to increase the label boxes. I went back and did that after looking at the example on the next page.

 

Next, I went to put on the data fields. In doing this report, I had not specified a table or query to be used so there were no fields available in the Field List. To establish the table to be used I brought up the properties for the report by clicking on the left most box under Report1:Report (notice that there is now a dark square inside the box). Then in the Record Source for the Report Properties I selected the only option with this database which is Payroll. The Field List box immediately filled with the fields available.

Now I need to put on the data fields in the detail area. First I brought over the Idno. The name wants to be a combination of FirstName, MidInit and LastName so I put in an unbound field and then used the expression builder to concatenate the three parts of the name. To get to the expression builder, I clicked the three dots after the ControlSource for the unbound field.

I went with the setup that the expression builder created and that in fact seems to make sense, however. It doesn't work correctly. If I go in and take out the Payroll in front of the fields then it will work correctly and the results will be produced. I have looked for an explanation for this and have not found one that I find satisfactory. If anyone can explain it, please let me know. So, now I will give you the correct entry under ControlSource.

The concatenated names are shown below:

Now I want to put the word Hourly or Salaried under Pay Type. The code is coming in as H or S and I need to do the conversion using an IIf statement. The statement that I coded is:

=IIf([PayCode]="S","Salaried","Hourly")

 

Here is how it looked when inserted as the Control Source for the column under the header Pay Type.

Now I need to use the code to calculate the weekly pay. Salaried people get their salary divided by 52. Hourly people who did not work over 40 hours get their pay per hour times the hours worked. If they worked over 40 hours then they get overtime for the hours worked. Note that this could have been done in a query prior to the report. I chose to do it in the report itself.

To do this I first decided to make an invisible field to figure out the pay for hourly workers. It is a field where I will store the result of the calculation and then mark it invisible so that looking at the report, I see nothing. The invisible field is named HourlyPay and asks the question if HoursWk is greater than 40. If it is it multiples the PayHr by 40 and then adds on the PayHr times 1.5 times the overtime hours which is HoursWk-40. This gives me the base pay plus time and one-half for the overtime hours. If HoursWk is not greater than 40, I simply multiply the PayHr by HoursWk. This answer is now stored in the Invisible field (See the properties above) under the name HourlyPay.

Now I move to the column where I want to put the weekly pay. I have already calculated the weekly pay for hourly workers, now I just have to determine whether the person is an hourly worker so the HourlyPay should be shown or whether the person is a salaried worker in which case their pay should be divided by 52.

This code is determining what will appear in the Pay for Week column. If PayCode = S then the salary is divided by 52, otherwise the HourlyPay that was just calculated is used.

Note that in some languages this could have been done with embedded if statements.

In the next column, I need to show the salary if the person is a salaried worker. This is accomplished with another question.

=IIf([PayCode]="S",[Salary]," ")

The next two fields show the PayHr and the HoursWk only if the person is an hourly worker. The code to accomplish this is shown.

=IIf([PayCode]="H",[PayHr]," ")

=IIf([PayCode]="H",[HoursWk]," ")

Note that in all of these cases, the column is set to spaces if no data will be shown.

With all numeric fields other than HoursWk, the format was set to currency. Currency automatically rounds to two decimal places.

The output that results from this is shown below: