Calculation Like Inventory

Friends,



I need your help.

I created a database with 2 tables and 1 query: tblPayment,tblEmployee,gryCalculation

tblPayment is updated twice in a month. every payment has his own paymentID.

I have a calculated field "ontv"(total workedhours)
I have a vacationfactor
For paymentX the employee gets (ont*vacationfactor) this field is called "per"
eg: 50*0.056= 2.8

I have a field in tblEmployee called vacationsaldo.

Every payment an Employee can use their vactionsaldo


What i want is:

On every payment the field: vacationsaldo must be: vacationsaldo + per.
If in tblPayment the field: VacationOP has a value it must be subtracted of the field vacationsaldo.

Something like this: vacationsaldo + per - VacationOP

After this formula the field vacationsaldo is uptodate.

For 1 payment it works great but what to do if the table is updated twice a month?

Eg:After payment 12 this formula is used, if i enter payment 13 this formula must be used on the up to date vacationsaldo value.

Please help?

When you say "Like an inventory" it is time to split stuff out into separate tables, using old Julius Caesar's "Divide and Conquer" strategy.

If you take vacation, treat it as a consumption of your vacation-hour inventory.

If you accrue vacation, treat it as an augmentation of your vacation-hour inventory.

tVacation:
EmployeeNum = the primary key (PK) of tblEmployee
DateUsed = the date on which an indicated amount of vacation was used
AmtUsed = the amount used, written as a negative number.

Now, consider this UNION Query (if you are not familiary with UNION queries, look them up in Access Help)

qtVac
SELECT EmployeeNum, [DatePaid] as DateUsed, Ont * [WorkedHours] AS AmtUsed from tblPayment UNION
SELECT [EmployeeNum], [DateUsed], [AmtUsed] ;

Now the amount of vacation any person has on the books is

DSum("[AmtUsed]", "qtVac", "[EmployeeNum]=" & CStr([EmployeeNum]) )

The UNION consults two tables - the payment table, from which you compute accrued hours, and the vacation table, where you consume hours. The DSum, because it looks at the UNION query, sees both tables at once.

Friend,

i tried this one but with no success.

TABLE tblPayment UNION all
SELECT EmployeeID, [DatePaid] as DateUsed, Ont * [WorkedHours] AS AmtUsed from tblPayment UNION
SELECT [EmployeeNum], [DateUsed], [AmtUsed] ;


I have attached the database. Can u please tell me more how to create a union query and solve this challenge.




Creates a union query (union query: A query that uses the UNION operator to combine the results of two or more select queries.), which combines the results of two or more independent queries or tables.

Syntax

Need more information?