Using Access to Automate Shift Schedule

[enat66]

Hello I am trying to use Microsoft Access to automate a task for a community organization I am in. Essentially what needs to be done is that we have an access table of volunteers. There are 4 positions for the volunteers and they each can only work one of those positions. I need to schedule the four positions each week.

Here is where it gets complicated. Since we have about 100 volunteers I want Access to print a report of the schedule for the next month but then update a field in the table noting when each volunteer last worked. I want it to then, each time I run the report, schedule those who haven't volunteered for the longest amount of time instead of those who recently where scheduled.

Essentially I do not know where to start, if it can even be done in Access. I'm willing to invest the time learning Access but what to know before I start if it is possible and if it is worth the effort.

The following post I will give an example that should explain better.

[renegade600]

I guess it does not include the hours the volunteers absolutely positively cannot work.

[enat66]

Ok so here would be some sample datatable:

Name Position Last Worked
Bob 1 5/5/2006
Rick 1 7/5/2006
Eric 1 12/5/2006
Judy 2 5/5/2006
Lee 2 7/5/2006
Heather 2 12/5/2006
Doug 2 5/5/2006
Nicole 2 7/5/2006
Will 2 12/5/2006


The report would need to first sort by position then last worked.
For position 2 it would pull 3 volunteers but for position 1 it would need 4, meaning it would start with those who worked the longest amount of time ago but then would need to repeat one member to fill the slots. So the Report Would Be:

Position 1 A: Bob
Position 1 B: Rick
Position 1 C: Eric
Position 1 D: Bob
Position 2 A: Judy
Position 2 B: Doug
Position 2 C: Lee

And then update the data in the table and set last worked to todays date.

Complicated I know.

[enat66]

That was a factor I was going to deal with later. Right now I am trying to figure out if I can use access to do this even.

[renegade600]

That was a factor I was going to deal with later. Right now I am trying to figure out if I can use access to do this even.

If its something you want to wait until later for no problem, but the thing is, if its going to be a part of your database, it needs to be included on the ground floor if you are going to design something in access.

I believe Access could do it but it would be a major undertaking for someone who knows nothing about access.

Check this out (http://www.kappix.com/). Its a free scheduling program I ran across once. I know nothing about it but maybe it will do what you need.