Question Calculations in Form/Subform

[suradj]

All,

Greetings to everyone. First of all i am new to this forum.

I have a challenge.

I created a database,

The database has a Form an a subform. In the form i have employee information and in the subform employee payment information. There is a "one to many" realationship between the table employee information and employee payment information.

The payment table is updated every month.

I have a field called vacationhours.(this field shows the balance of hours a employee has realtime).

Every month an employee can get a payment for his vacationhours if they give how many hours they want.

Example: 3/30/2010: vacationhours = 80
4/30/2010: vacationhours payment = 20

So the new balance on 5/30/2010 should be 80 -20 = 60, and the vacationhour field must be updated to 60.

And this field must be updated after every payment with a calculation: total workhours*vacationhoursFactor)

Example: 3/30/2010 vacationhours = 80
4/30/2010 vacationhours = vacationhours +(total workhours(50)*vacationhoursFactor(0.056))

The updated vacationhours field must be then 80+2.8= 82.8.

All this works only on the first record of the payment subform.
If i go to the next month and i enter new payment information,then in the main form the field vacationhours is not updated.

So, the vacationhours field must be updated very month by a addition and somtimes with a subtraction! this field must show the realtime information everymonth!!

Can u please help me?

Regards,

Suradj

 

[pbaldy]

In my view, this is fundamentally no different than an inventory application, except that the inventory is hours. Just as I wouldn't store quantity on hand in an inventory application, I wouldn't store hours here. I'd have transaction table that recorded each accrual or use of hours. My available hours is the net of those at any given point. The other advantage to that table is that it keeps a history of the transactions. Inevitably, someone will say their balance is wrong, and you will need to recreate it.

 

[suradj]

Paul,

Thanks for replying.

I need some more information. If i got you right then i have to create another table, with the transactions? Can i send u an example of my database?

thnx

 

[pbaldy]

I'm not saying you have to, just that I would. You can post your db here if you want.