Conditional counting/substracting values based on combo field

[jamilian]

Hi,

I want to create a simple employee leave tracker that will keep a balance of the amount of vacation days entitled for each person.

There are 6 fields:
1. Leave Description
2. Reason - this is a selectable list with the values 'worked','leave','sickness'.
3. Date from
4. Date to
5. Days
6. Balance

The 'Day's field will be calculated automatically (including weekends) from the difference between the two fields, 'Date from' and 'Date to'.
What's the formula for this?

The 'Balance' field should start with a value of 30 (days each employee is entitled to) and according the list value selected in the 'Reason' field, either add or subtract the amount of days in 'Days' field from the 'Balance' field.

For example, if the value in the 'Reason' field = 'Worked', then the days from the 'Days' field should be added to the 'Balance' field.
On the other hand, if the value in the 'Reason' field = 'Leave', then the days from the 'Days' field should be subtracted from the 'Balance' field.
What's the formula for this?

All help appreciated as I cannot get my head around this one.

 

[apr pillai]

The Balance field can be part of the main table Employees. Other fields shown can be the transaction table storing leave credit/debit activities based on "worked", "sickness","leave" as separate transactions, to maintain the history. But you should add the Employees-Code field also. You should establish relationships between the Employees Table and the Transaction table using employee_code.

You can calculate number of days by subtracting Date-From from Date-To (internaly Date-Values are numbers only)

Me![Days] = IIF([Reason]="worked",Me![Date To]-Me![Date From],-(Me![Date To]-Me![Date From]))

This formula you can write in the Lost_Focus() Event procedure of the [Date To] field.

You need an Update Query similar to the following:

UPDATE Employees INNER JOIN Transactions ON Employees.EmployeeID = Transactions.EmployeeID SET Employees.Balance = [Balance]+Forms![Transactions]![Days]
WHERE (((Employees.EmployeeID)=Forms![Transactions]![EmployeeID]));

 

[gemma-the-husky]

personally, i would store all days with the appropriate sign

so - holiday granted, with a plus

holiday taken with a minus


then you just need to sum all the leave to get the total. much easier than having to test the type of leave.

i would also store the initial grant of 30 days in this same table, for the same reasons.