CalculationHistory

[suradj]

Friends,

Newbie

I have a database with:

Tables:

tblEmployee
tblPayment
tblVacation

Query's:

qryEmployee
qryPayment
qryVacation
qryCalculation

tblPayment is updated 2 times in a month
tblEmployee has a field VacationSaldo

On every payment you have additions and possible subtraction of vacationhours.

The addition is called [Ontv]: (NormalHours+150%Hours+200%Hours)* VacationFactor

The possible subtraction is in tblPayment [VacationOP].

Every payment has a unique PaymentID.



After the payment information is entered the field VacationSaldo in tblEmployee must be updated with the value of the field [Ontv] and [VacationOP].

And when i wil enter a new payment information this must happen again but only for the last entered payment.

It sounds like an inventory.

Can u help me with this please?

See attachment

[jzwp11]

A calculated value such as the value you are trying to update VacationSaldo with is generally not stored but calculated when you need it.

I would like to see your table structure in more detail, but I don't have Access 2007 here at work. Can you save your database in an earlier version (Access 2007 will allow you to do that) and post that?

[suradj]

Here you have a copy saved as 2000-2003.

How should i deal with this challenge?

[jzwp11]

As I said in my original reply, you should not be storing the calculated value at all in the VacationSaldo field. I would store the employee's allowable/allocated vacation time for a year (if that is consistent with your business model). You can then calculate (using queries) how much vacation time the employee has used and how much remains (alloted-used=remaining).

In terms of your table structure, you have multiple fields related to types of hours for a payment. This describes a one (payment) to many (hour types) which should be handled with a related table. Additionally, you should not store the last name of the employee in the payment table; just having their userID is enough. I would also make the ID field of the table a little more descriptive and make it the primary key field of the table. Generally the primary key should have to significance to the user.

tblPayment
-pkPaymentID autonumber, primary key
-UserID foreign key to tblEmployee
-PaymentID (I assume this is some field that has significance to your business model)


tblHourTypes (4 records: normal, 150%, 200%, VacationOP)
-pkHourTypesID primary key, autonumber
-txtHourTypeDesc


tblPaymentHours
-pkPayHoursID primary key, autonumber
-fkPaymentID foreign key to tblPayment
-fkHourTypesID foreign key to tblHoursTypes
-spHours (a field to hold the # of hours, I assume you might have fractions of an hours, so I used a single precision number rather than a long integer)



Now I am not sure why you have a separate vacation table if you are tracking vacation hours relative to the payments. Do you track time worked by date also? So would this structure be more applicable (and then you can get rid of the tblVacation altogether?

tblPaymentHours
-pkPayHoursID primary key, autonumber
-fkPaymentID foreign key to tblPayment
-fkHourTypesID foreign key to tblHoursTypes
-dteHours (date for which the hours are applicable)
-spHours (a field to hold the # of hours, I assume you might have fractions of an hours, so I used a single precision number rather than a long integer)


Now, if you really want to keep tblVacation, then the best way is to record the vacation hours taken by date and relate that back to the payment time period and run an append query to add the summed vacation hours for a period to the tblPaymentHours with the correct fkHoursTypesID. But, you would have to remember to do this for every pay period. Which approach depends on your business rules, so you will have to make that decision