Sum to work out annual leave

[Elmobram22]

Hmmmm for the purposes of this it would just be to store what a persons rota would be. So it would only be contracted hours. They way I was looking at it was that you would put a start date in for a new staff member and it would work out the holidays based on that start date until the end of the fiscal year. If it was a staff change you could put a date in and it would show that as the end date for the current job and start date of the new job. Not sure how I would go about storing that data though. Maybe a second table that has start and end dates for staff with the staff member as a foreign key. So if you put the info in...

ID Staff Member Start Date End Date

Still not sure how this would work though, maybe if the start date or end date was blank it would put in 01/04/2015 and 31/03/2016. And if a staff member had an end date in that would be equal to the start date on the next record? Any help you have would be greatly appreciated.

Cheers,

Paul

 

[Elmobram22]

Right I've been thinking about this for a while now and I can see one fairly easy viable option for it. The staff table holds an ID for the staff member. The time table holds the shifts for the staff member. I have a query that adds their total rota based hours together.

The staff table also has a start and end date in. If the start date is after the start date of the fiscal year (in the query) then it would use this value to calculate that start date of the staff member until the end of the fiscal year. If the end date is before that of the end date of the fiscal year it would use that in the equation to the end of the fiscal year.

If a staff members hours change it would add a new record in the staff table based on their details. Ie new ID but with same name. Which could be automated through form buttons. That way it limits the user having to do very much. Does that sound ok? It is literally just to work out annual leave for people based on the hours in the rota within the database.

 

[Elmobram22]

Table
ID ---- FirstName ---- Last Name ---- Start Date ---- End Date
01 ---- Peter ---------- Jones --------- 03/12/2008
02 ---- Paul ----------- Bramwell ----- 09/12/2014
03 ---- Bill ------------ Johnson ------- 04/02/2012 --- 03/12/2014

Query

ID ---- Staff Member ------------ Start Date ----- End Date ------ Total Days
01 ---- Peter Jones -------------- 01/04/2014 --- 31/03/2015 ----- 365
02 ---- Paul Bramwell ----------- 09/12/2014 --- 31/03/2015 ----- 113
03 ---- Bill Johnson -------------- 01/04/2014 --- 03/12/2014 ----- 247

 

[RainLover]

I have no experience with (Whatever Country. You should display your location for this purpose.) How does the employee earn Holiday Pay. Is it a certain number of days per annum. What do we do about public holidays and sick days or even Family days. We can have a different rate depending on who you are working for. There has been no mention of these extra days in your equation. I though they should be included some how.

 

[Elmobram22]

The equation is total hours over 28 days /4 x 5.6 x (number of days worked / number of days in fiscal year)

In regards to holidays etc they do not count. It's just based on the above. And it's the uk that I'm in

 

[Elmobram22]

I've managed to work this all out apart from one vital part moving forward and that is if the end date of a staff member is before the default start date. That they are not included in the query for that year.

 

[RainLover]

You need to review the design of your tables. First of all you never have lookups in tables. Search Google the evils of lookups in tables. I am referring to tables not forms etc.

 

[Elmobram22]

Yeah I changed the design before and it was suggested I use the weeks and days as lookups rather than in tables as I previously had them. I have read the dangers though and I agree. What would be your suggestion based on my database. I am still just learning as I go.

 

[RainLover]

Yeah I changed the design before and it was suggested I use the weeks and days as lookups rather than in tables as I previously had them. I have read the dangers though and I agree. What would be your suggestion based on my database. I am still just learning as I go.

Not sure what you are saying here.Did you remove all Lookups in all the Tables. If so then that is good.So where is your problem now.It would be good to get your latest version of the database.

 

[Elmobram22]

Sorry no what I meant was someone suggested normalising the tables so to remove some tables and simplify it. I reduced it down to a bunch of tables. One for day, one for week, one for location, one for staff, one for location. But was told I could put the day and week option within the table. I guess what I'm getting at is do you have an example that shows what you mean. Are you suggesting I get rid of lookups all together and if so how do I let the user see the info they want rather than a number etc.

 

[RainLover]

The user should never ever see the Tables or Queries.The user deals with FORMS and REPORTS.Forms can have Lookups and that is the correct place for them.