Automatically assign a value based on rotating list

[cvandijk07]

Hello All,

I am starting the development of a new application for one of our departments
and while meeting about what they were interested in yesterday I got a
strange request for the program. The premise of the program is to schedule
inspectors to do specific inspections and record when each of these
inspections are done and the certificates that they are issued which let
others know that the appropriate inspection is done.

The part that they requested be included in this new application is to
automatically assign an inspector to an inspection based on a rotating list.
Essentially, creating a table which contains the list of inspectors and their
respective ID's assigning inspector 1 to task 1, inspector 2 to the next task
and so on continuing to rotate through inspectors as the tasks are created.
The person in charge now wants to ensure that the work is fair and equally
distributed to each inspector.

So far, I have created two tables - tblInspector and tblInspections.
tblInspector contains InspectorID (primary key, autonumber) and InspectorName.
tblInspections contains all the pertinent inspection information and contains
InspectorID as a foreign key.

I followed some steps found in another forum for creating a similar type of
form, however, the form only works when you open it and once you cycle
through the list of inspectors, it doesn't start from the top of the list
correctly. I want to include a button for adding new inspections that moves
the next entry automatically to the next inspector.

I hope someone understands what I have described above and could point me in
the right direction as to how this could possibly work.

Thanks and if you have any questions please contact me.

Chris

 

[Jeff Boyce]

Several things occur to me in reading your description...

First, the presumption is that all tasks are created equal ... that is, one
inspection is very much the same as any other, so equally dividing the
inspections among inspectors results in a "fair and equal distribution". Is
that a valid presumption?

Next, I assume you'll also need to handle the situation when new inspectors
are added to the list of inspectors, and when inspectors have to be "removed
from service". You wouldn't want to simply delete them from the table,
because their previous assignments would end up with <No Inspector Found>!
You'll probably need/want a [InspectorOutOfService] date field. ... and do
you need to handle when folks are out sick or on vacation?

I'm thinking this calls for three tables, not two. You have inspectors, you
have inspections/tasks, and you have Inspector-Assigned-to-Inspection/Task
.... that sounds like three tables...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[cvandijk07]

Hi Jeff,

Thanks for your prompt response.

You are correct in saying that all tasks are created equal. If one inspector
is scheduled to give a fire safety talk, the next inspector could be assigned
to do a smoke detector inspection. While these tasks are not equal, he is
hoping that when the next time a fire safety talk pops up, a different
inspector will be assigned to that task.

That is a good suggestion about the RemovedFromService field. They are very
big on archiving past work so you are correct that I wouldn't want to delete
them because that would detract from the actual archive we are hoping to
establish.

Finally, that does sound like the right track that I should be going on.
Work is almost through for today but I will see if I can get some time
tomorrow to experiment with the three table theory and see how I make out.

Several things occur to me in reading your description...

First, the presumption is that all tasks are created equal ... that is, one
inspection is very much the same as any other, so equally dividing the
inspections among inspectors results in a "fair and equal distribution". Is
that a valid presumption?

Next, I assume you'll also need to handle the situation when new inspectors
are added to the list of inspectors, and when inspectors have to be "removed
from service". You wouldn't want to simply delete them from the table,
because their previous assignments would end up with <No Inspector Found>!
You'll probably need/want a [InspectorOutOfService] date field. ... and do
you need to handle when folks are out sick or on vacation?

I'm thinking this calls for three tables, not two. You have inspectors, you
have inspections/tasks, and you have Inspector-Assigned-to-Inspection/Task
... that sounds like three tables...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello All,

[quoted text clipped - 41 lines]

Chris

 

[KARL DEWEY]

More bells and whistles -- UNTESTED --
[Oldest_Inspect_Task] --
SELECT TOP 1 [Inspections_Task].[ID], Max([Inspections_Task].[InspectDate])
FROM [Inspections_Task] INNER JOIN [Inspectors] ON [Inspections_Task].[ID] =
[Inspectors].[ID]
WHERE [Inspectors].[Active] = -1
GROUP BY [Inspections_Task].[ID]
ORDER BY [Inspections_Task].[InspectDate];

1- This pulls last inspection date for each inspector -
Max([Inspections_Task].[InspectDate])
2- Only active inspectors - WHERE [Inspectors].[Active] = -1
3- Sorts in ascending order - ORDER BY [Inspections_Task].[InspectDate]
4- Pulls only one - TOP 1

--
KARL DEWEY
Build a little - Test a little

Hi Jeff,

Thanks for your prompt response.

You are correct in saying that all tasks are created equal. If one inspector
is scheduled to give a fire safety talk, the next inspector could be assigned
to do a smoke detector inspection. While these tasks are not equal, he is
hoping that when the next time a fire safety talk pops up, a different
inspector will be assigned to that task.

That is a good suggestion about the RemovedFromService field. They are very
big on archiving past work so you are correct that I wouldn't want to delete
them because that would detract from the actual archive we are hoping to
establish.

Finally, that does sound like the right track that I should be going on.
Work is almost through for today but I will see if I can get some time
tomorrow to experiment with the three table theory and see how I make out.

Several things occur to me in reading your description...

First, the presumption is that all tasks are created equal ... that is, one
inspection is very much the same as any other, so equally dividing the
inspections among inspectors results in a "fair and equal distribution". Is
that a valid presumption?

Next, I assume you'll also need to handle the situation when new inspectors
are added to the list of inspectors, and when inspectors have to be "removed
from service". You wouldn't want to simply delete them from the table,
because their previous assignments would end up with <No Inspector Found>!
You'll probably need/want a [InspectorOutOfService] date field. ... and do
you need to handle when folks are out sick or on vacation?

I'm thinking this calls for three tables, not two. You have inspectors, you
have inspections/tasks, and you have Inspector-Assigned-to-Inspection/Task
... that sounds like three tables...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello All,

[quoted text clipped - 41 lines]

Chris

 

[Mike Painter]

The part that they requested be included in this new application is to
automatically assign an inspector to an inspection based on a

How automatic is automatic? If inspector X goes on vacation or gets sick
what happens?
If the talk and the check is in the same building on the same day what
happens?
(I got bit with "automatic" with the first application I wrote, I thought
they meant it...)

While there are several ways to do this I would present the user with a fake
listbox formed from a subform and present all inspectors all the time.
It could be linked to a subforn that showed the current history of that
inspector.
The inspector form would have his name, a check box, and be color coded for
vacations, etc.
Clicking on the inspector would assign his ID to the job and check the check
box.
Since you might want to assign the same guy to two jobs in a row, a popup
might warn if he or she is checked off.
When all of them have rotated then a button to clear the checks would clear
the table.
Not automatic but only one extra (double) click.

(Oh, by the way Inspector Fred and I switched jobs the last time around.")

 

[cvandijk07 via AccessMonster.com]

I definitely have the same reservations about the whole automatic process
that you do. However, the user still wished to have it done like this. The
beauty of the system though is that inspectors are not responsible for making
exact deadlines so should one be on vacation or sick, that work is expected
to stay in the queue until the inspection is complete.

He wanted to be able to keep track of how many outstanding complaints are
being left in the system and determine the reason for the backlog. He is
aware of all vacations and sick time that the inspectors take so should
something slip through he will be able to look back and determine whether or
not it was laziness or justified.

I do like your suggestions though and I am going to see if I can try some of
that this afternoon now that all my meetings are done. Both ideas posted,
Mike and Karl, should be beneficial to my development and I thank you.
Should I have any success I will let you know how I make out.

Chris