Hi all,

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?

Many thanks

John

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.

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?

thanks

John

That is still bad practice. The way I pointed out, you can add hours whenever you need to.

For example...

tblEmployee
EmpID
EmpFName
EmpLName
etc...

tblEmployee_Transactions
TransactionID
EmpID
TransTypeID (1 = added, 2 = take away)
TransHours
TransDate
etc...

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.

Good evening John

How are the entitlement of holidays worked out are they in day's or hours..
In hours it allows flexibility..
(IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif)

Hi Larry

In days at the moment. What flexibility would hours allow? You mean booking half days?

Thanks

John

HI John
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.

(IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif)