Updating Table from a Form Calculation?

[Wacko1971]

I don't know the best way about doing this but here is what I have (I am a newbie to all this)

I have set up a roster form where the table holds all the staff members details (ie. age, name, employee number, days of the week worked with the times worked in them etc)

I can get the staff members times worked updated through a form to the table easily enough, but what I need to do now is have it update the total hours worked for the week etc added to the table so a report can be printed.

The form calculates all the information as I input the hours worked for each day and at the end of the form produces fields for:

[FullTotalHours] = Total number of hours worked for the week
[FullNormal] = Total of hours at normal rate of pay
[FullOT1] = Total of hours worked at overtime 1.5
[FullOT2] = Total of hours worked at overtime 2.0

I need this information printed on a report and if possible saved to the table aswell.

I have a section on the staff members table for:
[NormCost]
[OT1Cost]
[OT2Cost] etc.....

How can this be done?

I have tried this:
[Event Procedure]

Private Sub FullTotalHours_AfterUpdate()
Me![NormCost] = Me![WedMinusLunch]
End Sub

But it doesn't work.

I also tried this on the report

Forms!EmployeesListing!FullTotalHours

But this just produced an error on my report.

Any help would be appreciated.

ps. I am a newbie so please be explicit in the details as the macros etc confuse the crap outta me "If who I am is what I have, then who will I be if what I have is taken from me?"

 

[mrf1xa]

Hi there

Firstly, and don't take this the wrong way, I would question your table structure. I may be wrong, but your post makes me believe that you have just one table showing staff details AND the daily records- in other words if Fred works 6 hours on Mon and 7 on Tue, the two entries both contain all the staff info and the only difference is the number of hours. If so, this is bad database design. You should have one table for all the staff info, with a unique identifier for each (perhaps employee number?). The you should have a separate table for the daily records, containing just the staff id to relate each record to the staff details. The two table are then linked one-to-many on the staff ID field.

Secondly, as a general comment, a basic rule of database design is that you don't (usually) store info that can be easliy caluclated. In other words, why bulk out your tables with extra info on cost which can be generated easily on the fly in a report? This is how I would do it.

Having said that, I accept there are some situations where other factors dictate that you must store this info in a table. If this is one of those, then it shouldn't be difficult to do. I am however a bit confused about your code- where does Me![WedMinusLunch] come ito it and what does this represent?

I would expect there to be a field on the form (or in your staff table) that gives an hourly rate. If so, you can simply calculate the hours by using something like

Me![NormCost] = Me![FullNormal] * Me![hourlycost]

and for the overtime the last part becomes (Me![hourlycost]*1.5) etc

Hope this helps- post back if you need further info
Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[Wacko1971]

Hi, thanks, so far , I never thought about splitting the details to diff tables. I have 1 table with the diff rates of pay for the ages etc, and 1 table for the employee details. I have put in a employee number field, so this will help link tables.

As for the calculations I have this setup ok. But I need to know wether I can get the calculations to save back into the table or how would I get them to show up on a report? Either way would suffice as I can get the hours entered to show up on the report, due to they are stored in the table via the form. But how do I do this with the calculation fields?

Me![WedMinusLunch] was my mistake. This the WedMinusLunch part was the calculation field I used to try to link to the report with no success. "If who I am is what I have, then who will I be if what I have is taken from me?"