Help with Logic!!

[Michael]

I have one table that has a list of schedules with the following columns:

AutoNumber, Date, Shift. Example data looks like:

Autonumber Date Shift
1 1/2/08 7: 00AM – 4:00PM
2 1/2/08 7:00AM – 4:00PM
3 1/3/08 4:00PM – 12:00AM

I have another table of employees requesting the available shifts with the
following columns:

Date, Shift, Employee, DoubleDay, Reliability Rating. Example data looks
like:

Date Shift Employee Doubleday Rating
1/2/08 7: 00AM – 4:00PM John Doe Sun 5%
1/2/08 7: 00AM – 4:00PM Jane Doe Wed 2%
1/2/08 7: 00AM – 4:00PM Jose Doe Sun 6%
1/3/08 4:00PM – 12:00AM Jane Doe Sat 10%

The object is to assign a schedule to the employee with the following logic:

1st logic – Request date is not the same as the Double Day gets the shift.
2nd logic – Rating with the lowest percentage gets the shift.

So looking at the above examples the result would be the following:

Shift Autonumber 1 will be given to Jane Doe.
Shift Autonumber 2 will be given to John Doe.
Shift Autonumber 3 will be given to Jane Doe.


Any help would be greatly appreciated

 

[Dale Fye]

I'm assuming that you have a typo (extra space) in the Shift column of
several records in both of your tables (at least in the example you provided).

So, let me make sure I understand this:
1. The first table defines the positions (date/shift) you need filled.
2. The second field defines the shifts people have signed up for.
3. You want to fill all of the positions in table #1 with people from table
#2 based on your logic. What happens if there are not enough volunteers to
fill all of the positions in table #1?

I'm not sure I understand Rule #1. Do you mean that if 1/2/08 is Sunday,
John Doe is not elegible for that date?

Dale

 

[Michael]

Hi Dale...

Thanks for replying...
1. If there are not enough people then the shift does not get assigned.
All we are looking for is to assign those shifts that are volunteered for.

2. If 1/2/08 is a Sunday and John Doe has Sunday listed as a DoubleDay then
John is not elegible for the shift.

All your other assumptions are accurate!

Thanks again!!