Random rotating work schedule...Can it be done??

[nspader]

Now that I have your attention ;) Here's what I would like to do. I would like to design Access to automatically generate a monthly schedule. Here's the catch, I have three shifts (8-4,10-6, and 12-8). Each worker is rotated daily through these shifts. To do it by hand it is somewhat random placement and EXTREMELY TEDIOUS. The shifts are only Monday thru Friday.

Now heres another catch. The 8-4 shift has to have 2 people, and the 12-8 shift has to have only 1 person.

I have one more catch if anyone can help me even get this far. I want to be able to designate days off needed, and some specfic shift needed before filling in the rest of the info. (this is not as important as I can always export the calendar and adjust for those days, but it would be nice.)

I have a decent calendar form set up started. What I really need is either subform for each day or a query or code design that will give me these results.

Thank you in advance for any help, this site has always been a great help in my learning proccess.

NICK

[mshmyob]

Hello Nick,

Maybe someone can help you but it appears you want someone to write almost the entire program. I think you may get a better response if you explained 1 specific part of the app that is not working and post all relevent information and some code that is not working and then we can narrow down your problem that way.

As for 'Can it be Done' ... The answer is 'Yes'. If you have designed your table structure properly almost anything can be done in Access (related to databases naturally).

cheers,

Now that I have your attention ;) Here's what I would like to do. I would like to design Access to automatically generate a monthly schedule. Here's the catch, I have three shifts (8-4,10-6, and 12-8). Each worker is rotated daily through these shifts. To do it by hand it is somewhat random placement and EXTREMELY TEDIOUS. The shifts are only Monday thru Friday.

Now heres another catch. The 8-4 shift has to have 2 people, and the 12-8 shift has to have only 1 person.

I have one more catch if anyone can help me even get this far. I want to be able to designate days off needed, and some specfic shift needed before filling in the rest of the info. (this is not as important as I can always export the calendar and adjust for those days, but it would be nice.)

I have a decent calendar form set up started. What I really need is either subform for each day or a query or code design that will give me these results.

Thank you in advance for any help, this site has always been a great help in my learning proccess.

NICK

[nspader]

Hello Nick,

Maybe someone can help you but it appears you want someone to write almost the entire program. I think you may get a better response if you explained 1 specific part of the app that is not working and post all relevent information and some code that is not working and then we can narrow down your problem that way.

As for 'Can it be Done' ... The answer is 'Yes'. If you have designed your table structure properly almost anything can be done in Access (related to databases naturally).

cheers,

Thats exactly where my confusion is. I am not sure how to set the proper table structure.

I do not want to have anyone write all of the code for me. I am just stuck with no knowledge of how to even get off the ground with this.

Any starting point would be helpful. I would be more then happy to post the db I have started if anyone might think that is helpful.

Thanks
Nick

I have attached what I have Thus far. Thank you again everyone.

[mshmyob]

That's a start post it and we'll check it out and give you some pointers.

cheers,

Thats exactly where my confusion is. I am not sure how to set the proper table structure.

I do not want to have anyone write all of the code for me. I am just stuck with no knowledge of how to even get off the ground with this.

Any starting point would be helpful. I would be more then happy to post the db I have started if anyone might think that is helpful.

Thanks
Nick

[mshmyob]

OK I downloaded it and sorry to tell you that the way you have it, it will never work.

Your table structure is incomplete. The tables are not normalized or linked with primary and foreign keys.

Read the following article and try again and re upload your new table design.

If you have any questions along the way after you have read the article and tried again I will be happy to answer.

Normalization

cheers,

[nspader]

Thank you for your reply. I figured I was setting tables up all wrong. I have never worked with setting up tables for this sort of thing.

I am going to read over this and work on it, hopefully over the weekend and repost as soon as I can get some sort of normalized table structure.

Thank you for you help so far.

Nick

[nspader]

OK, I have made an attempt at creating the proper tables. I am hoping you can take a look at it and see if the table design is at least correct before I move forward in further design.

Thank you so much for your help

Nick

[mshmyob]

Hello Nick,

Big improvement from your first run. Congrats!

I will make a few suggestions:

1. Do not use a person's name as a primary key/foreign key - make up an Employee Number for your employees
2. Do not use spaces in your field names - Use an Underscore ie: Employee_Name or EmployeeName
3. Do not use any 'weird' symbols ie # - use only letters and numbers for field names.

I have noticed you may be able to track time but not dates. Could you fix up the field names by the rules mentioned above AND manually enter some sample data for me. I will then give it a thorough going over.

cheers,

OK, I have made an attempt at creating the proper tables. I am hoping you can take a look at it and see if the table design is at least correct before I move forward in further design.

Thank you so much for your help

Nick

[mshmyob]

Sorry for the delay but have been very busy.

I am finally looking at it in more detail and am starting to make some minor changes. When I finish them I will repost your database.

Thanks for your patience.

cheers,

[nspader]

Sorry for the delay but have been very busy.

I am finally looking at it in more detail and am starting to make some minor changes. When I finish them I will repost your database.

Thanks for your patience.

cheers,

Thank you so much for all you are doing. Take all the time you need, your help is greatly appreciated. I am going to work on the notes above and have them ready for looking at what you come up with. Thank you.

Nick

Ok I think I have the names corrected across the tables. One relationship I couldnt get the same as the rest it is one to one instead of one to many.

Also, I am not sure how to track the dates. Does it need to be anothe rtable with all the dates. I mean the first step is to get them to appear on the calendar form in the shift rotation.

Thanks again

[nspader]

Should I start a new thread...with a different name to get better results?
Thanks
Nick

[mshmyob]

Sorry for the delay Nick. i finally got some time. I have redone your tables slightly and created some code to create a schedule - I am just checking it over and will upload to you in the next hour or so.

cheers,

Should I start a new thread...with a different name to get better results?
Thanks
Nick

[nspader]

Sorry for the delay Nick. i finally got some time. I have redone your tables slightly and created some code to create a schedule - I am just checking it over and will upload to you in the next hour or so.

cheers,

That's AWESOME. Thank you so much for the help. I know time is tight many times. I apologize if it seemed I was rushing you. I wasnt trying to. I was just seeing if you thought it would be better to have a new title to get some other eyes on it as well.

Like I said THANK YOU SO MUCH in advance.

Nick

[mshmyob]

Take a look at this and let me know if this is of any help.

cheers,

That's AWESOME. Thank you so much for the help. I know time is tight many times. I apologize if it seemed I was rushing you. I wasnt trying to. I was just seeing if you thought it would be better to have a new title to get some other eyes on it as well.

Like I said THANK YOU SO MUCH in advance.

Nick

[nspader]

It is EXACTLY what I needed to start. Thank you SOOO Much. I am going to look over some and play with. I will probably have a few questions along the way. But you have no idea how incredible a start this is.

THANK YOU

Nick

[mshmyob]

Good to hear.

We'll be here so ask if you have any questions.

cheers,

It is EXACTLY what I needed to start. Thank you SOOO Much. I am going to look over some and play with. I will probably have a few questions along the way. But you have no idea how incredible a start this is.

THANK YOU

Nick

[paultri]

mshmyob, I understand that this is a VERY old thread although it is very close to what I have been trying to do for quite a while. I am simply trying to automate a call center schedule using Access and while I have some good knowledge of Access and a very little bit of VBA what you have done here is very close. I have been trying to modify your code a little bit to be able to put "# of employees needed" for each individual shift. I have been unsuccessful. Can you offer any help? I would appreciate it!

[mshmyob]

Sure. Post what you have done and give me more details of what you want and I will see what I can do to help.

cheers,