Managing Employee Shifts

[Vo0do0uk]

Novice Needs Help!!!!

Agreed to do a project which i can't work out in my tiny pea sized mind how to do! ok here goes

It's a database for a restarant. which i can do. however it's going to allow the user to create rotas/timetables for the employees. so...

i want the DB to show all the employees in a list automaticaly in a form. then along this form by each persons names have a drop down list containing the start time they can work, and one next to this with a drop down list containing the finish time they will work till. but i can't do it! when i change one list box for an employee they change for every employee! then i want to add up all the hours worked that week for each employee at the end and total these up to get the total hours worked for each day.

There needs to be a start time/finish time for every employee for every day of the week. mon/sat. how do i do this?? can't work out tables relationships or how to show it on a form!? if you could explain any replys in complete novice language i stand a better chance of understanding...

im new to forums, i'm hoping i'll have some begginers look at get some much needed help! it'll be greatly appriciated!

thank you very much, sitting here glued to screen in antisipation waiting for responce!!

 

[Parker]

Agreed to do a project which i can't work out in my tiny pea sized mind how to do! ok here goes

It's a database for a restarant. which i can do. however it's going to allow the user to create rotas/timetables for the employees. so...:

There’s a moral here---------Don't agree to nufink until you know what’s involved

i want the DB to show all the employees in a list automaticaly in a form. then along this form by each persons names have a drop down list containing the start time they can work, and one next to this with a drop down list containing the finish time they will work till. but i can't do it when i change one list box for an employee they change for every employee!...:

I don't think I would do it like that but post a zipped copy of wht you have and I'll (and prob. others), will take a look too see where you have gone wrong.

then i want to add up all the hours worked that week for each employee at the end and total these up to get the total hours worked for each day.

Use a calculation query to work this out.

There needs to be a start time/finish time for every employee for every day of the week. mon/sat. how do i do this?? can't work out tables relationships or how to show it on a form!? .

Once you have the data entered you can use a combination of queries and/or repots to show this information and print out a duty roster

if you could explain any replies in complete novice language i stand a better chance of understanding...

im new to forums, i'm hoping i'll have some begginers look at get some much needed help! it'll be greatly appriciated!:

I'm a novice as well so you probably won't understand me but what the hell

thank you very much, sitting here glued to screen in antisipation waiting for responce!!

You must be tired by now. sorry I missed this post earlier, and it is the weekend........A lot of people on this (and other forums) log on while they are at work to ; break the boredom, cheat the boss, have a giggle at their employees expense, Some even to help people like us or to help themselves to save from getting fired

Seriously though, don't sit "glued to the screen" it won't do you or the screen much good and the effort to remove the sticky stuff afterwards just aint worth it. Better to set your options for notification in your user profile. But I think the default is immediate notification by email anyway. So if you have a BB connection than your machine will wake you up as soon as I hit the 'Submit Reply' button and if you have dial up then you will be notified as soon as you next log on.

Post a zipped copy of what you have and lets have a look. It would be useful to know what version af Access you are using as well. Because different versions have different facilities built in.

PS I know a company that sells a good product for removing glue from almost anything. Its called 'StickyStuff Remover'

Don't forget to do a 'Compact and Repair befor you zip it up'

 

[Vo0do0uk]

That was quick!

thanks for you swift reply! i'm using access 2002 i think anyway i have office xp pro so whatever comes with that!

I would zip the work i'd done but in a moment of madness just 'deleted' it and started again so i haven't got the form which housed my original work.

How would you go about tackling a rota?

There are two rotas to do.

1) Kitchen
2) Front of house

There will be 20 employees in each rota. one form for kithen one for FOH.

I need to be able to show start time and finish time for each employee on the form for monday through to wednesday. Each report (1&2) must then be easily printed out using a report from the form to put up on a notice board so each other employee can view his/her hours for the coming week.

I'm open to suggestions about how to implement this? including tables/relationships setting out?

I do have little experiance, and have been doing tutorials with forms, querys macros and have some SQL knowledge also..

 

[Parker]

I think I would use four tables

1. Employees
   
   Store information about all employees​
2. Department
   
   Departments; Kitchen, F.O.H, Cleaners, Admin, etc.​
3. Availability
   
   Dates and times available​
4. Shift
   
   Dates and times of shifts​

All tables related to Employees. tblAvailabilty and tblShift need a one to many relationship.

Then use queries to find who is available for each day and what time they can work.

Set your shifts

Use a report to print out shift details grouped by department.

To save bloat you could use update queries to replace the information in tblAvailability and tbleShift on a weekly or monthly basis. Dependant on how often you build you rosters and calculate wages.

You can then use another query to calculate hours worked (even overtime) using the datedif method. If you need to keep records of these calculations then that can be stored in another table also linked to tblEmployee.

 

[Vo0do0uk]

Thanks again!

Got tables set up, struggling with relationships though keep getting type mismatch error?!

And could you give an example of a query i could use to find the days for each employee that they can't work!?

cheers again!

in the tblavailability would i have empid name? then how would i put in each day that they can't work? in the same row? i seem to get more confused each time i use the bloody programme

 

[Parker]

Thanks again!

Got tables set up, struggling with relationships though keep getting type mismatch error?!

Read the help files on relationships and search here for 'relationship' & 'primary/foreign keys'

And could you give an example of a query i could use to find the days for each employee that they can't work!?

Again LOOK there is tons of information here already in hundreds of threads there are even samples in the samples db section.

in the tblavailability would i have empid name? then how would i put in each day that they can't work? in the same row? i seem to get more confused each time i use the bloody programme

2 things here. You do not need the name of the employee any other tables again read up on relationships. You do not need to store both the days that someone is available and the ones where their not.

If I tell you that I am available 9am - 4pm on Monday, Wednesday and Saturday; 2pm - 11pm on Friday then you know I'm not available at any other time so why do you need to confuse the issue by writing it down

Computers are quite clever and are much better at working with logic than we are. So if we don't need to store the information then a computer defiantly doesn't.

Store one set of information and learn, if you must, how to ask the machine to tell you the rest.

Again this information has been posted here a thousand times. look and you will find far more than any one person can relate.

 

[Vo0do0uk]

Still in trouble

Ok, i have some tables there not important this was just a mess about to check i could do it all before i make a final product

as you can see i have tblEmployee, and tblhours. not sure if these are set up correct but if you could give guidance!?

Look at FrmEmployee. what i want to do sounds simple, but i just can't do it.

I'd like to have all the employees in a list from top to bottom down one side. perhaps two forms one with kitchen staff one with FOH staff that are on sepperate forms that link with a tab or something.

Anyway next to each employee i'd like two combo boxes. one will contain the hours 9am - 12 and be labeled start time. the other will contain the values from the hours table that are left over which will be 13 - 19 or whatever. and this will be labeled finish time.

Along the top i would require the labels monday - sunday and somehow show the dates for this week. and be able to record each weekly rota in it's entirity somewhere, on a weekly basis according to what hours the user has picked for which employee. for the each employee their daily hours must be totalled from what the user has sellected from the combo boxes and show a total number of hours - which will later be used to work out pay for the employee.

These can then be totalled to give the weeks hours.

It has to be in this format,

My main problem as you'll see from the frmemployee is that when i chose for instance Ben Wilson's start time as 09:00:00 it changes everyone else's start time to 09:00:00 also! it has to be just for ben wilson

the idea is to make it look simple to use, as this is what they use orinally but just on paper which they have to write out every week etc!

PLEASE GIVE ADVICE!! e-mail benwilsonuk@ntlworld.com if prefere that on here will give any more information you require to get me out of sticky mess!!

 

[Parker]

OK I'll take a look tommorow if I get a chance.

 

[Parker]

What like This?

If you make the input form for shift a single form and use a date and time picker to enter the data it will be better.

Then you can use the shift query to display the info.

You need to add the department but I've left you to work that one out. I have, however, corrected the join between the employees table and the department table.

If you use this principle to enter the data for availability you can then query that table and use the results to work out the shift patern instead of entering it manually as I have done for this example.

Don't use spaces or special characters in your naming of fields etc. And go through the lot and rename everything conventionally. This will help you later.

You will find all the info here for naming conventions if youlook for it. i.e. table 'Employees' = 'tblEmployees' ; Query 'Shift' = 'qryShift' ; Form 'Shift' = 'frmShift' , Etc. I have done this for you but you need to rename all your controls, etc.

HTH

 

[Vo0do0uk]

Thats so so so close to pretty much what i'm after!! good call!

just needs some fine tuning, can't belive you wen't through all that bother just for little old me

greatly appriciated though...

i'll let ya know how i get on!

 

[Parker]

Thats so so so close to pretty much what i'm after!! good call!

just needs some fine tuning, can't belive you wen't through all that bother just for little old me

greatly appriciated though...

i'll let ya know how i get on!

No probs. It only took about 10 mins. It took longer to post the reply

 

[Vo0do0uk]

Ok a couple more probs...


I've been messing all day and can't work this bit out.

You have it so it shows all dates etc, i just want monday to sunday across the top. this could be week 1 say 1st week of year. underneath the days is the date

on left like we've got already is all employee names. without having to put them in manualy tho!. but with say an option button at top to select all staff from FOH or all staff from Kitchen. so not both will be shown on the form

so i need a daystart dayfinish, nightstart nightfinish and a total for each day for each employee!

I can't work out how to just get the department selected in the form so say just the kitchen staff are present.

and if i could just sort out the monday - sunday thing, with all the dates (7ofthem) and get it so i can see the rotas for each seperate week i'll be there, these rotas need doing on a weekly bases. maybe if i had a record for week number? or something..

what you think??

if that last one took 10 mins!? spare another

and then i won't bother ya again!! (maybe)

 

[Parker]

Ok a couple more probs...


I've been messing all day and can't work this bit out.

You have it so it shows all dates etc, i just want monday to sunday across the top. this could be week 1 say 1st week of year. underneath the days is the date

Somewher I've seen a routine or function that displays days but I can't remember where off hand but, basically if you just want to show certain dates then set 'between dates' as a parameter for the query

on left like we've got already is all employee names. without having to put them in manualy tho!. but with say an option button at top to select all staff from FOH or all staff from Kitchen. so not both will be shown on the form

What I gave you is an example. Use that to generate 2 seperate queries; One for F.O.H and one for kitchen. Then create a comand buton on your form to pick between the 2 different queries. Or you can use a list box for the queries and pick from the list. THere are other ways as well but those two are prob. the easiest.

so i need a daystart dayfinish, nightstart nightfinish and a total for each day for each employee!

Again this is easiest done by designing a query.

I can't work out how to just get the department selected in the form so say just the kitchen staff are present.

If you use a query to seperate them then just use a lable that changes with the choice of query. Otherwise you will display the depatment for each employee and thats pointless

and if i could just sort out the monday - sunday thing, with all the dates (7ofthem) and get it so i can see the rotas for each seperate week i'll be there, these rotas need doing on a weekly bases. maybe if i had a record for week number? or something..

You could do it that way or you could do something like 'Week Comencing (date) To End (date)' And use the start and finish date parameters of the query.

Another alternative would be to use a cross tab Query. But I don't like crosstabs

As for your layout; You can do anything you want with that in design view. Dates along the top, botom, side, Placed at random; Access really doesn't give two hoots so long as it can find the controls in which to place your chosen information.

Don't forget, also, you will be producing a report for this lot to print out and there you can chose all sorts of grouping options visual methods of display.

what you think??

if that last one took 10 mins!? spare another

and then i won't bother ya again!! (maybe)

What do I think? No probs. Have another go at it and if you’re still stuck and cant apply the information then I'll have another go and send you another example to work with. But I'm pretty busy this weekend (being a bank holiday) so the time that I may have will be very fragmented. ( it's just taken me an hour and three attempts to write this in between phone calls and now I’ve got to go out ‘cause I‘ve gorra driver stuck on the A1M with a flat tire. I told her it was only flat on one side but she wasn’t amused )

 

[Rich]

and now I’ve got to go out ‘cause I‘ve gorra driver stuck on the A1M with a flat tire.

well we'll see you on Monday then, you should be back by then, if you're not too tired that is

 

[Parker]

well we'll see you on Monday then, you should be back by then, if you're not too tired that is

Nah wasn't that bad it was only 27 miles away and I've just got back

 

[Vo0do0uk]

Almost there!

Ok i've kinda worked it out, in my head!! but cant seem to get it down on access. i've jotted it in Excell to show what im on about!! and attached this and my current access work

So if you look at the attachement you'll see i have tblDepartment, tblEmployee and tblUniform. (this will hold the details of lend uniform by the company for each employee) i've to add other tables but i 'have' to just get this timetable thing sorted!!

In Excell the Yellow section are the parts i would ideally have automated. so it will show the current week the begging date of the week say monday which is when the timetables are written for each week. and therefore i will need 52 enteries per year in this table. with each week being able to record the day and the date.

So top of the form will 'automaticaly' say the week number for example 19 and then the date ending, or begining whichever doesn't matter

Then i will have employee names down the left like we had before, these could just be selected and manually put in with combo boxes.. unless you could let me know how to automate this process?? for each employee they wil have a box saying start time and finish time like in your example.

However the date will go above each start/finish time. thus there will be
Monday - Sunday accross the top with the date. the records will need to go into these days to later work out total of hours worked during the whole day by all employees. i'm not too concerned about recording the amount of hours for each employee. but it would be nice.

I figured i needed a table called tblRota. in there i was trying to work out the date by using =Date(). therefore if the company did the rotas every monday this would automaticaly show the date and the current week. which would be fine to record the date. however i wanted to show all the next 6 days and dates but can't seem to be able to!

I'm so stuck!! hope you know what im trying to achieve i think the Excell spreadsheet will simplify what im on about!!

The company want to record the rotas so they can review them and change things, for example if someone is off sick they shouldn't be paid! and likewise if they go early. so they have to be recorded, i figured by week was a good way to do this, or week beggingin date. so these can be combo boxes at the top so the user can bring up each weekly rota via this.

I just can't work out how to set out my tables!!! please help, this is all i have to do then i can bang out all my other tables/forms/querys and i've even planned so macro's and splashscreens etc! i've so much to do and i'm looking forward to mess about with fonts and colours and all the fun stuff! just need this crucial backbone to work!!

Cheers matey, as always!

Ben

 

[Vo0do0uk]

Had another brain wave

Had another idea!

think im close now... but still can't get it

got too options here, tried to include a link entity. as i think i would have a m:n and this isn;t good!

what do you think??

perhaps you could take a look and have a fiddle!

take a look at relationships.. think their close??

Cheers

p.s would love you to take another 10 mins like the last one!

your 10 mins = my 10 days!

 

[Parker]

OK here’s another tanners worth.

Your spending far too much time mucking about with tables, relationships, and forms.

The answer is in queries and reports.

Take a look at this. I think this may be what you are trying to achieve.

I'm not going to give it you all at once 'cause you wont learn that way.

I haven’t bothered to set up employee/depart links (I'm sure you can do that)
But take a look at how the queries and reports work. And I mean a close look.

An example: The group by date part of the report. Check out the formatting of the field.

Setting out the look of the report you can do--------- want the day across the top? No prob. just move stuff about like you would do with any form in design view.

I'm not saying that this is the best way to achieve your goals but it is a simple way.

Have a play with it and See how ya go.

Cheers

 

[Vo0do0uk]

Cheers again!

I see what you mean, and yeah i know i could use reports and querys to seperate up the data thats cool.

But i need to have the form like the one in excell, this system will be used by people who haven't used access before you see and so i want the form to look just like the paper one's they would normally fill in. plus they need to be changed at the end of the shift. so if ben wilson was at work till 23:00 but actually went at 21:00 this could easily be changed.

I know you say not to ponder on relationships too much, but on your example i could put in 28/04/04 Benwilson 9:00 - 17:00 kitchen and i could put in 28/04/04 Benwilson 10:00 - 15:00 Kitchen. meaning that person would have too enteries for the same shift!

So -- i'm after a table that somehow will show:

Employee ID and the Shift for each day (just one)
the date of the day
the week
the year

and then have mondaystart monday finish
tuesday start
tuesday finish

i've tried this instead of having just start time and finish time. but the form is messy, it doesn't like me changing stuff

Have a look mate!! got loads to do and just getting this relationship and form right is just the tip of the iceburg!!!

What i was going to do to seperate FOH and Kitchen was do a query like in ur example to find all staff in FOH and all in Kitchen and then make two forms, one which will just have FOH staff on and one which will just have Kichen staff on. so the user can select FOH rotas, or Kitchen rotas this is they way they do it now on paper anyway

I see the way you've done it, and it workds fine. but ideally i would have say the main form to contain RotaID, the weeknumber and year. the user will then be able to sort by weeknumber to get the rota up they require by using a combo box which will change all the data in the subform to the employees and shift times for that chosen week.

in the subform will start with Staffname then have the date above the day names which will say MondayStart MondayFinish TuesdayStart TuesdayFinish etc etc.

This is the way i really really want it!! but just can't get relationships right. i realise to even do a form your tables have to be all correct please help!

i've attached what i'd done, have a fiddle

If it's quicker e-mail me if you'd like benwilsonuk@ntlworld.com!

I've to get this done by saturday!! if i just get the table and relationships to allow me to enter data for many employees on one rota with each employee being able to work one shift per day/date and have a subform with the employee and days, and the main form with current week and year which the user can then select the rotas from week 12, or the rotas from week 19 etc and see who was working and what shifts!!

PLEASE PLEASE PLEASE HELP!! i'm so so stuck and worried i won't get it done! i was up till 04:20am!! so i'm dedicated dude!!

cheers as always! wouldn't of got this far without ya!

i wanna get onto the fun bit of writting some macros and changing fonts in forms etc!! not messing with the relationships!

now i know this example is missing the querys like staff name etc but i've started again about 20 times!

 

[Parker]

Ok I'll try and deal with this point by point

But i need to have the form like the one in excell, this system will be used by people who haven't used access before you see and so i want the form to look just like the paper one's they would normally fill in.

First having a form that looks like Excel is not really necessary nor is it necessarily desirable but this is done with on screen formatting.

The ease of use of an application depends a lot on how easy and self explanatory the input process and the user interface is and that is mostly a cosmetic problem.

Cosmetics are the last thing that you develop not the first. So forget that until you have a working system.

plus they need to be changed at the end of the shift. so if ben wilson was at work till 23:00 but actually went at 21:00 this could easily be changed.

You are dealing with 2 or possibly 3 business problems here. ------ Finding availability; Setting shifts according to that availability; and storing information about the actual time worked. THEY ARE 3 SEPERATE ISSUES that need to work together. Deal with them as such.

[QUOTE}I know you say not to ponder on relationships too much, but on your example i could put in 28/04/04 Benwilson 9:00 - 17:00 kitchen and i could put in 28/04/04 Benwilson 10:00 - 15:00 Kitchen. meaning that person would have too enteries for the same shift!

Of course you could. I didn't set the relationships and I told you I hadn't. You have already discovered how to set relationships. I merely showed you what was possible with simple queries and reports.

So -- i'm after a table that somehow will show:

The only tables you need are the three that I showed you in my last example. You do not need your other tables and you defiantly do not display the table as a form for end users. This is up to other elements of the db.

What i was going to do to seperate FOH and Kitchen was do a query like in ur example to find all staff in FOH and all in Kitchen and then make two forms, one which will just have FOH staff on and one which will just have Kichen staff on. so the user can select FOH rotas, or Kitchen rotas this is they way they do it now on paper anyway

Great a form for each department--well if you must; you have all the information you need to do this Go for It

I see the way you've done it, and it workds fine. but ideally i would have say the main form to contain RotaID, the weeknumber and year. the user will then be able to sort by weeknumber to get the rota up they require by using a combo box which will change all the data in the subform to the employees and shift times for that chosen week.

You already have that look again at the example I gave you and then look at this modified example. ------ All I have done is to change the formatting for the display of a couple of fields and Hey Presto you have week number and day instead of day and date. If you had examined this properly and spent some time with it you would have seen that.

in the subform will start with Staffname then have the date above the day names which will say MondayStart MondayFinish TuesdayStart TuesdayFinish etc etc.

One of the most powerful and useful things about Access is it's ability to crunch data ---- that is it's forte. Give it the data; tell it what you want to see and it will provide it

I've to get this done by saturday!! if i just get the table and relationships to allow me to enter data for many employees on one rota with each employee being able to work one shift per day/date and have a subform with the employee and days, and the main form with current week and year which the user can then select the rotas from week 12, or the rotas from week 19 etc and see who was working and what shifts!!

Your gonna struggle! You have all the information that you need to resolve this. Personally it would take me about 4hrs to put this together (and I have already spent more time than that trying to help you learn), there are others here that would prob knock this one up in an hour. But I'm not about to do it for you (a) I don't have the time and (b) help is one thing work is another. My time, like most others costs. And your inability to learn from what I have shown you and from information available by searching these forums indicates that what you really want is for me (or someone else) to do it for you and save your bacon. Sorry guy. Much as I am willing to help where poss. That I am not willing to do for free.

Stop going round in circles. Sit down and study the examples I have given you and other examples here on the forums. Then if you have a serious problem with fine points repost and I'm sure someone will respond.

First things you need to do is forget what you think you know and listen --- read a little on basic db design and learn what tables, forms, queries and reports are used for.