At my site, I am not allowed to download arbitrary files that don't come from an approved site, so I cannot look at what you've done. Here is how I would approach your problem.
1. You must have a clear company policy on the date at which this rollover occurs. New Year's day or the beginning of the company's fiscal year or whatever.
2. You must have a clear company policy on what happens if leave that was in the rollover from the previous year remains available. I.e. let's say I'm so senior in the company that I get 3 weeks a year - but I'm a workaholic. For two years in a row I only take one week each year. At the end of the second year, ONE of my remaining weeks HAS to be a carried-forward week from the previous year's rollover. Do I lose the previous year's carry forward?
3. When a person accrues leave, does that accrual always become available at a particular day? (i.e. leave accruals only take effect on paydays? on the "rollover" day? on the first of the month?)
4. Does a person who takes leave on a given day accrue hours anyway as long as their employment isn't terminated? Or can you only earn leave when you are actually at work? (Potentially affects amount accrued.)
5. When a person COULD take a Holiday but was asked to work through it anyway, can that leave be carried forward? Or is that a use-it-or-lose-it situation?
6. (I would presume this, but it is good to verify it for sure...) When a person uses leave AND has a rollover, is it assumed/automatic that the rollover gets depleted first, or does it really matter?
7. Can a leave balance be negative? (I.e. borrowing leave ahead of yourself?)
8. Can an employee sell back leave? (I.e. get paid for the leave they promise they won't use? Other rules might apply as to allowed sales increments, like "can only sell a week at the time.")
Without these rules being known, you could have a hellish problem on your hands. If you already know the rules for each of these cases, though, you can do some good things with this knowledge.
OK, now that you have the rules defined, I would take this approach.
tblEmployee
fldEmpID - prime key, number of some sort, might be autonumber
other stuff about the person...
tblLeave
fldEmpID - foreign key matching corresponding field in tblEmployee
fldXactDate - date/time field
fldXactType - type of transaction (used leave; accrued leave; leave rollover; took Holiday leave; granted Holiday leave; etc.)
fldXactAmt - hours & fractions thereof actually used - Single (floating-point)
tblLeaveArchives - same structure as tblLeave BUT does not count in current leave reports. Present for required record-keeping only. Perhaps on your rollover date, you export this to a text file first, then empty it with an Erase query before you archive your primary table. That's your call.
On days when each person's leave accrual is posted, you append a bunch of "accrued leave" transactions, one for each employee. When the person takes leave, an individual "used leave" transaction gets posted. When a person takes a holiday, you append a "took Holiday." But a "took Holiday" can only occur if there is a corresponding "granted Holiday" transaction on record. (Or not, if you allow compensatory leave for folks who worked through a holiday. See what I mean about the rules giving headaches? The question here is whether Holiday is just ordinary leave or is another category of excused absence that isn't ordinary leave.)
For the leave rollover and Holiday availability, you can create the individual transactions as you need them. For leave rollover, a new employee who started in the middle of the year doesn't have one, but you make one for him/her anyway, with a zero starting balance when you create the account. You also can create the "granted Holiday" entries as required when creating a new employee. Finally, for a new employee, you can create whatever you need for accrued leave - including no record at all if they don't accrue leave until a specific date or number of days of employment.
On the rollover evaluation date, you run a query that sums the employee's available leave as of that date. You can do this as a UNION query of several selects from the leave table, where the sum you want is add the "granted leave" transactions and also sum the negative of the "used leave" transactions i.e. Sum( -fldXactAmt) where fldXactType is "used leave". A "granted Holiday" is a plus; a "used Holiday" is a minus; a "leave rollover" is a plus. A "sold back Leave" is a minus.
Using the SUM query as a data source, you then append a "leave rollover" transaction. If that sounds like I meant a second Append query based on the first Select query, you got it right. If an employee works through a Holiday and that particular type of leave cannot be accrued, it is a GOOD thing that you can isolate the type of leave by having a separate transaction. But if it is just ordinary leave, you still can tell the difference.
Now, archive the records from tblLeave. That is, append to tblLeaveArchive all records from tblLeave earlier than the rollover date/time. Then Erase (remove) old records from tblLeave so that NO transactions of any type remain that have fldXactDate earlier then the date of the "leave rollover" transaction. Now fill in the coming year's "granted Holiday" transactions for each employee.
Since you have a specific ROLLOVER transaction, you can call it out on a report. Since the holiday transactions can be called out separately, you can present them separately on the report.
Note that I would not have taken this approach if you did not have the requirement to separate out the types of leave. The basic principle I was addressing with something so complex is that if you have to report on it, you have to track it in the first place. Since you have to report on various "flavors" of leave, that means you must track transactions that tell you those flavors.
