Calculate totals from previous years

[AnthonyDixon]

I have an employee table with an employee leave child table which contains a date field and an hours taken field. My employee table contains a field that determines the allotted hours allowed to be taken each year. I am currently calculating the total time taken for the current year and displaying the remaining balance left for the year. What I'm trying to calculate is the balance of time not taken for all previous years. I'm just not sure how to how to arrive at the time not taken totals for each year.

Thank you in advance for any help.

Tony

11 Advanced Mac/PC

Employee Leave.fp7.zip

[comment]

it seems that the number of hours allowed per year is specific to an employee - but doesn't it ever change from year to year?

Note also that your file doesn't tell us in which years an employee was employed; if John Doe doesn't have any leaves in 2009, it could be because he didn't take any (and in such case he has a balance of 40 hours), or because he started working in 2010 only.

[AnthonyDixon]

Yes, there will be a field for date of hire and the hours allowed per year may change from year to year. oh boy I guess now I have really complicated things.

[comment]

I think the opposite is the truth: things are more complicated than you have made them seem.

How about a somewhat different approach?

LeavesByYear.fp7.zip

[AnthonyDixon]

What a great approach! This changes everything for the better.

Thank you so much.

Tony