Design a booking system

[Guest]

I already posted a thread with a question similar to this one but one got to
no conclusion at that time. Now I’m trying to go through with this new
feature in my DB so here it goes.

My company builds custom designed products different from customer to
customer. Each order may refer to only one reference or several references
from a customer. I’ve got a table of ORDERS where I keep a record of the
Order, PK number type, manual input. This table is linked by this Order field
to a second table of REFERENCES where the Reference is PK, string type,
manual input. I already have several reports and forms working properly.

Now I need to implement a delivery booking system. I have to relate the
REFERENCES table with another table of EMPLOYEES and VEHICLES. Each job may
consist of a delivery of one or more references. The delivery can be made by
us and in that case there is always one worker and one helper assigned for
each job. For each job I cannot send two helpers although I can send two
fully skilled workers. I must also choose a vehicle for the delivery through
its licence plate. On other times a delivery can be shipped by a delivery
company or by the client. In these last two situations the installation can
or cannot be made by our workers.

I have a few ideas (and already a few doubts) on what tables to create and
on how to relate them but I was hoping someone could share some ideas before
I get to the hard labour and have to change it all near the end.

Thx in advance.

 

[nanne]

Hi Jose

I would create a table JOB with fields:
Job-id, References, Employes, vehicles
and relate those fields to the other tables by query

analyse the required output carefully

suc6
Nne

 

[Guest]

The delivery can be made by

us and in that case there is always one worker and one helper assigned for
each job. For each job I cannot send two helpers although I can send two
fully skilled workers.

Just addressing this part, I would suggest that you create a query that
separates out just the fully skilled workers, and another query that lists
all helpers and fully skilled workers. Then, on your form for selecting the
employees that go with a delivery, you have one combo box that selects from
the fully skilled workers query, and another combo box that selects from the
complete list query. As two simple combo boxes, you would still have to
check that you are not selecting the same name in each box, but you would
have to select at least one fully skilled worker.