Feb 6 2009, 04:02 PM
I have a table that includes an employeeID & a number of days remaining holiday. I have a form (not based on that table) which shows all holiday requests and when the Authorised? field in this form is ticked I want the holidays remaining field in the table to reduce by 1. I also want the ability for the user to change their mind - ie if the tick is removed from the authorised? field then the holiday remaining value for that employee increases by 1.
The holidays and employee tables do have a relationship of course via employee ID.
Any thoughts on how please?
Feb 6 2009, 04:04 PM
You are trying to store a calculated value - and most of the time you do not want to do this.
What you could do is create a holiday transaction table where the holiday's taken/added are inputted into this table. And then when needed, you just calculate the remaining balance.
Feb 6 2009, 04:07 PM
I understand what you are saying but each employee may have a different amount of annual holiday. My thought was at the start of the year the figures would be manually updated and then reduced as the year goes on. Is that still bad practise or am I being Mr Slow?
Feb 6 2009, 04:19 PM
That is still bad practice. The way I pointed out, you can add hours whenever you need to.
TransTypeID (1 = added, 2 = take away)
Then you can just create a query to sum the TransHoursAcq (where TransTypeID = 1 and then TransHoursUsed (where TransTypeID = 2), then just subtract them to get the current remaining balance.
This leaves less room for error when you keep updating one field.
Feb 6 2009, 06:38 PM
Good evening John
How are the entitlement of holidays worked out are they in day's or hours..
In hours it allows flexibility..
Feb 7 2009, 05:50 AM
In days at the moment. What flexibility would hours allow? You mean booking half days?
Feb 8 2009, 04:59 AM
Yes.. with hours the flexibility of half days and in most cases some people only require some hours, but what I seen in the past is when a company give time in lieu of payment hours can simply added in any amount.
Looking forward to see the progress in the project.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here