Utilise a calendar form?

[Gasman]

Hi everyone,

I was browsing the site and came upon this thread http://www.access-programmers.co.uk/forums/showthread.php?t=287953&page=2

I downloaded the database from http://www.utteraccess.com/forum/index.php?showtopic=2001188 and feel I could use this for our holiday planning in work.

However I would like to show the remaining holidays in place of the dept data. This I feel I can do easy enough by subtracting days used from a value in the employee table, but am struggling on how to get the days used. Each record holds a startdate and enddate of the holiday. I know I would need to use Datediff +1 to get the number of holiday days for each record (and might have a problem if weekend days are selected in error?), but not sure how to 'link' the used totals value to the holiday form?

Would someone be able to point me in the right direction please?

It would not be a large database. 20 or 30 employees at the most, so no problem with number of controls, or possibly speed.

TIA

 

[Minty]

You actually need to calculate at least three values, Leave taken, leave booked but not taken, and then leave left.

I recently had to provide a backdated leave report, which proved quite challenging as our staff all have different leave year start dates. If this applies to you then you'll probably need to make a series of queries with a final summation query to get the results you need.
As a further tip, and it does go slightly against the "calculate values don't store them" principle , I would record the holiday start and end date AND the number of working days (and/or hours) that equates to with each leave event. This makes things like the task you are looking at a lot simpler.

I would actually probably make a temp table to hold the values you want and recreate it when the form is opened. Calculated on the fly will probably slow the Form down to tortoise pace.

 

[Gasman]

Thank you Minty,
The DB already uses a temp table. I have a fair bit of digging to do to see how it all works, but can see the benefit of storing the actual working days. I was even considering seeing if I could protect the weekend cells, so no holidays could be added to them.

Fortunately whilst our hours are different, the holiday year is the same for everyone, Jan to Dec.