Change from days to hours

[jj72uk]

Hmm..

On the main form we have the entitlement subform displayed. This will show employees entitlement when chosen from the drop down box, it shows Year, Entitlement, entitlement remaning.

I'm thinking that this subform would also be helpful to have attached to the calander form, the reason being is that the way the db is set up, when an employees opens it up the calander appears, they then can check to see what days they want and then come to me/my manager to authorise it, the DB is set up so the calander shows on load so employees can't mess around with trying to book it themselves, I was thinking of placing this subform on the calander as well to stop employees then coming to me saying...how many hours do I have left.

 

[jzwp22]

So what do you want to show on the calendar form--just the entitlement for the employee who opens the form or all employees? If it is for the individual employee you will need to know who is logging in or a combo box where the user can select their name. Do you want to show time taken, time planned (booked but not yet taken) etc.?

 

[jj72uk]

Just for all employees, a combo box to choose the employee will do.

I'm able to add a sub form into the calander, its just a matter of linking the child and to a combo box

 

[jzwp22]

See if the attached is what you are after. I just created a query and then used that as a row source of an unbound combo box directly on the calendar form. I added some textboxes that display some of the data returned by the combo box's row source.

 

[jj72uk]

This is almost right... in the current design the combo box/fields will only show if a team leader has holiday booked, is there anyway to have the combo box show all team leaders entitlement and remaning, regardless of booked holidays?

Just another quick question, more for direction rather than having a problem and needing help. Yet.

What we have now is a holiday DB which does everything that I need, now I'm thinking of extra tweaks. I'll explain what I'd like to do and you can tell me if I'm heading in the right direction.

These team leaders work differing shifts and what I'm thinking of is creating another list box on the calander so when you click a day you can see which team leaders are on shift for that day.

So... I'm guessing that I will need to make a new table with data, though my intial thoughts would mean I'd need a entry per day of the year..is there a better way?

Once I have a table in place i guess i will have to make a list box that reacts to the date clicked on and then display the correct data?

Am I being over ambitions our this feesiable?

 

[jzwp22]

I had to alter the query for the combo box to include all employees. I also noticed that the year combo box on the calendar was a value list with 5 set values 2007, 2008, 2009, 2010 and 2011. Basically this would fail on 1/1/2012, so I added some code to populate the row source with the current year, the previous 2 years and the 2 years after the current. It mimics the value list but just automates it to change the value list each year.

As to your other questions, you will probably need more than just one table. Do the employees stay on a particular shift for a period of time or does it vary by day? How far in advance are the shift assignments made?

This is how I would structure the tables if the employees are on a shift for a time frame.

tblShifts
-pkShiftID primary key, autonumber
-txtShiftName

tblEmployeeShifts
-pkEmpShiftID primary key, autonumber
-fkEmpID foreign key to employee table
-fkShiftID foreign key to tblShifts
-dteStart (start shift date)
-dteEnd (end shift date)

 

[jzwp22]

I forgot to attached the modified database in my previous post. It is attached.

 

[jj72uk]

We usually have a shift review every 12 months, though having said that we have had 3 in the last 6 months...

I can't see them changing them again for another 12 months.

I've actually attached the excel sheet so you can see the types of shifts they have.

4 team leaders are on 'main rotation' 2 on 'saturday shift' 1 on 'sunday shift' 1 on 'late shift'

When we have a shift review they are givin a minimum of 4 weeks notice, though its usually 6 weeks.

 

[jj72uk]

I've created them 2 tables as suggested apart from dtestart and dteen.

Looking at the excel sheet how the shifts are set out, whats the best way to define them?

 

[jzwp22]

From your spreadsheet you have 4 types of shifts (main, Saturday, Sunday, Late 8) each consisting of a multiple number of weeks (one-to-many relationship). I don't know if you want to capture all of the details of your spreadsheet in the database, but you can

tblShiftType (4 records: main, Saturday, Sunday, Late 8)
-pkShiftTypeID primary key, autonumber
-txtShiftType

Each shift type is composed of multiple week #'s and the week numbers names are shared between each shift type

tblWeekNumbers (8 records: Week1...Week8)
-pkWeekNoID primary key, autonumber
-txtWeekNumber

Junction table to relate the shift name to the week #'s of which it is composed.

tblShiftWeeks
-pkShiftWeekID primary key, autonumber
-fkShiftTypeID foreign key to tblShiftType
-fkWeekNoID foreign key to tblWeekNumbers

Each week will have multiple days on which to work

tblDayNames (Sunday, Monday...)
-pkDayNameID primary key, autonumber
-txtDayName



Each Shift type-week combination will have many days that have varying start and end times, so we need another junction table:

tblShiftWeekDays
-pkShiftWeekDaysID primary key, autonumber
-fkShiftWeekID foreign key to tblShiftWeeks
-fkDayNameID foreign key to tblDayNames
-dteStartTime
-dteEndTime


Now you need to relate the shift assigned to the employee. You will need to specify the start date, but since we know how many weeks/days a person will be on the shift, we do not need the end date since it can be calculated

tblEmployeeShifts
-pkEmpShiftID primary key, autonumber
-fkEmpID foreign key to employee table
-fkShiftTypeID foreign key to tblShiftTypes
-dteStart (start shift date)

 

[jj72uk]

Thats ALOT of work to do!

Maybe I could cut a corner and make a report per week rota and just add a click box on each week to show the shifts for each team leader.

 

[jzwp22]

The final decision is yours, but you have to think of your longer term goals of what you want the application to do.

 

[jj72uk]

Hi jzwp22,

I've decided not to make another list box on the calander, with the shifts changing regularly I don't think it would be effective to add it in, for now.

On a side note.

How easy would it be to add a couple more leave types?

E. Holiday, Late and Other.

With E. Holiday and Other taking off entitlement and late not.

I've added them to the leave type table, what do I need to do next?

 

[jzwp22]

To make things a little more flexible, I would recommend adding a yes/no field to the leave table. Check the box if the leave type impacts entitlement; don't check if it does not. In order to account for all book dates that have any of the leave types that impact entitlement, you have to add a query that pulls the data for all leave types where the yes/no box is checked (=-1) and then sum up the number of hours. I went ahead and added the query qryGatherLeave and then modified the query that summed up the hours to reference the new query rather than the booking table directly as we did before. The revised DB is attached.

It sounds like your requirements keep changing. I would recommend taking some time to talk to your upper managment and your users to get a clearing definition of what they want this database to do. You can then use that information to plan the next major revision to the database which will incorporate all of their changes rather than making small, piecemeal changes.

 

[jj72uk]

lol..the requirements only change as the upper management sees it, likes it and thinks, can we add this, can we do this, how about this etc.

I'm sure you have expereince this before.

In its entirity these last chnages is all that would be needed. Though I cannot see a check box.

If we keep this as it is, would check boxes be needed?

Is this left for me to add?

 

[jzwp22]

I added a yes/no field (should show as a textbox) in the table: tblLeaveType

I use the yes/no field as a criteria in a query but it should not show up anywhere else in your present database. I also added the other leave types you mentioned as records in the tblLeaveType, so the database I attached should be ready for you to use.

I've run into the upper management issue before...
I guess I've learned enough over the last 20 years to anticipate what they might want, but they do surprise me with some "unique" requests that defy logic

 

[jj72uk]

Ahh i see now, sorry i didnt realsise it was actually in the tbl. My mistake!

I guess all i need to do now is to re import the data from my current db into this one.

My intial though of just creating a report for the manager to click on to see what rota is for that week won't work, as when going into design view of the calander it only shows the main outline, I cannot physically go to each month and add a rpt button.

I guess he'll have to cope without.

 

[jzwp22]

I'm not sure I understand what you are saying here:

My intial though of just creating a report for the manager to click on to see what rota is for that week won't work, as when going into design view of the calander it only shows the main outline, I cannot physically go to each month and add a rpt button.

If you want to put the shift that the various people are on, then you will have to add the tables to handle it like I illustrated previously. Or were you after something else?

 

[jj72uk]

ideally what you illustrated would be spot on, but far i think its a bit much for me. I could make the tables and make a fair go at the queries. Which would feed into a list box on the calander when a specific day was clicked.

What I then thought of doing was just making a button at the start of each week which would then open up a report to show what week was running

And then I had a afterthought about just making a test box next to the start of each week to show which shifts were running.

The form idea and the text box idea wouldnt work, becasue the calander form is designed so all you see is the main outlay and not the design per month.

Thats all i was saying

 

[jzwp22]

I'm not sure how I would approach it either, but you should be able to modify the calendar to add a button along the left side. There probably is a way to do it, but anything you try requires adding the new tables first, so you might start there and see where it leads you.