A picture of your layout, while a good start, doesn't tell anyone reading your post how you have designed the tables and relationships shown.
I see what appear to be 4 portals shown, but have no idea how they are related to your layout's table. I am guessing that the layout's table is a table of employees with one record for each employee?
Do you have this relationship?
Employees::__pkEmployeeID = UsedVacationTime::_fkEmployeeID
If an employee takes two vacation days for 7/18/2014 to 7/19/2014, will that be recorded as a single record with a Date of 7/18/2014 and a 2 in Vacation used? Or would it be a number of hours used such as 16? (8 hours for each accrued day...)
If so, Sum ( UsedVacationTIme::VacationUsed ) will compute the total vacation time used.
In phase 2 they accrue 4 vacation days, 2 for phase 3 and 2 for phase 2.
I think that you actually meant: In phase 2 they accrue 4 vacation days, 2 for phase 3 and 2 for phase 4. Is this days of vacation per year? So that Phase 4 employees accrue 8 days of vacation each year?
The table is related to the client table.which is the Parent table. If the Employee take two vacation day, it will be recorded as vacation days used, not as 16 hours. and the days of vacation is not per years but every 90days. And the Employee accrue 4 days in phase , 2 vacation days in phase 2 and 2 vacation days in Phase. total of 8 vacation day in all the years. Vacation days is available when the employee start Phase2. I want Phase 3 to say blank until employee reaches Phase 3, once the employee phase 3 start that will be 90days after phase 2, then i will like Phase3 to activate and if the employee did not use there vacation in phase2 or whatever remains from Phase 2 should automatically add to Phase3 and phase 4 will shows blank and Phase 3 will be inactive because the employee is no longer in that phase.
All table are related to one tabele like in the screen shot and yes it is one record of each employee
The table is related to the client table.which is the Parent table.
Does that mean that "Client" is selected in Show Records From for the layout shown in your screen shot?
and the days of vacation is not per years but every 90days.
I understand that each Phase is 90 days, but you only mention 4 phases = 360 days. What happens after Phase 4 is reached by an employee? Is that intended to = a year and then they start over at phase 1? (Should have done this math earlier...)
And the Employee accrue 4 days in phase , 2 vacation days in phase 2 and 2 vacation days in Phase. total of 8 vacation day in all the year
I think you mean entering Phase 2 adds 4 days, entering Phases, 3 and 4 add 2 more days each producing 4 + 2 + 2 = 8 days. Is that what you meant?
Seems like your basic calculation for each phases vacation day accrual would be:
Phase 2: If ( Get ( CurrentDate ) > Phase1Enddate ; 4 ) // accrue 4 days once Phase 2 has been entered
Phase 3: If ( Get ( CurrentDate ) > Phase2Enddate ; 2 ) //accrue 2 more days once Phase 3 has been entered.
Phase 4: If ( Get ( CurrentDate ) > Phase3Enddate ; 2 ) //accrue 2 more days once Phase 4 has been entered.
But once an employee enters (I assume) Phase 1 for the next year of service, you won't want to lose the unused accrued days, I would imagine. Correct?
I suggest that you set up a "ledger" table for managing both the accrual an usage of vacation days:
The fields in that table would be:
_fkEmployeeID (Links to client table by employee identifier)
Date (date vacation days have accrued or been consumed)
vacation used (Number of days used)
vacation accrued (Number of days accrued, added by script when the next Phase is entered.
cVacBal (calculation field; Vacation Accrued - Vacation Used)
sUnusedVacationDays ( summary; total of cVacBal )
This can be set up in your portal much like a check book register but with accrued days in place of deposits and days used in place of withdrawals. And this will keep the accrued days so that the first day of entering a new Phase 1 doesn't automatically drop the user back to 4 days vacation.
That makes for a very different situation. Then the calculations that I specified would seem to be what you need here.