DELIVERIES table - DB design

[Jose Lopes]

Greetings.

I work in a factory where we produce unique products according to the
client’s specs. I have to deal with a table of products identified by the
REFERENCE field (pk, string type, manual input), a table of WORKERS and a
VEHICLES table. My tables ORDERS, CLIENTS and PRODUCTS are already working
nicely but I need to expand my DB.

Now, I want to book my deliveries for the work days where two workers visit
the client with its respective product to deliver and install it. The first
one is the installer and the second the helper. So there are two categories
of workers and I cannot send two helpers for a job. Yet if I need to book two
products for the same day I must not be able to pick the same two workers
because they were already picked for the first delivery. The same situation
for the vehicle I chose for them to make this delivery; it cannot be in two
places on the same day. Can someone help me to structure this? Is it possible
to have a list box listing all the “remaining†workers not assigned so I can
pick two of them for the job? I’ve been thinking of a DELIVERIES table but I
don’t see how I can restrict the worker assignments and the vehicles…

Thanks a lot everyone.

 

[mnature]

When you start a new database in Access, there is one template available on
your computer, that might be a starting point for you. It is called Resource
Scheduling. Upon glancing through it, it looks like they accomplish the
restrictions through VBA programming. You could set up your installers,
helpers, and vehicles as Resources, which would then be assigned to a
customer's job. You set up a day and time period that the resources would be
used, and it prevents you from scheduling those same resources for the same
time period. It does not limit your choices, though.

The interface is a little primitive, but you could perhaps modify it to suit
your purposes.

 

[Tom Ellison]

Dear Jose:

This does not sound to me like a "table design" problem. Here's my ideas.

It sounds like any installation is an "all day" project for two persons and
a vehicle. If you were going to pick one installer from a combo box list on
the form, one helper from another combo box list, and one vehicle from a
list, then the next job you want those 3 to disappear from their lists.
This could be done by having the source query for those 3 combo boxes
respect any person/vehicle already chosen for that day and not show them.

There are so often exceptions. It may be you should allow the user to
override this feature and select anyone or any vehicle they wish. Perhaps
there is are two easy, quick installation that are very near eachother, and
one team can handle both in a day. I would seriously consider adding a
checkbox on the form that allows choosing any persons or vehicles. This
kind of flexibility allows you do make the default behavior what it should
be, but adds the necessary flexibility when desired.

Tom Ellison