Assigning trucks to deliver loads

[Guest]

Currently doing this all on paper. Here goes.

Presently the top part of the DAILY Sheet lists all available trucks to use
in order of union priority. there are some other columns that go with each
truck

example:

Contacted WK Contacted No WK Truck# Phone# Contact Client ID Location
5 555-1234 joe
12 555-1222 ann
14 555-0101 carol
22 7878787 jim
3 8866 tom
There are approx. 300 trucks

Down below I'll call them Dispatch id come in
Dispatch Id Clientid #Loads Location PO# Comments
1 ABB 5 South st. 2277
2 RW 3 1st St. 378
3 ABB 2 Kelly ave. 257

For that given day all on one sheet as the Dispatch id's come in we
highlight say in green the first dispatch id and then go above and assign 5
trucks-since 5 loads ( highlight in green) write next to them the Client id,
Location - we can assign 5 different trucks on the same truck 5 times on any
variation there of. then onto highlighting in Pink for dispatchid2. and
trucks above accordingly. Don't them to be able to assign the truck once it
is used for that day unless it is used for multiple trips for same dispatchid.

Not sure how to lay this out. Have a client table and truck table so there
can be drop downs. Just not sure of how to interact the tables and to show
ALL trucks and all dispatch id for a given day.

Thanks,
Barb

 

[Allen Browne]

Barb, it will require quite a bit of effort to create a database to handle
all aspects of this database. An expert working full time could do it in a
few weeks. Someone who is learning as they go and doing it in their spare
time will take a couple of years to get it optimal.

The basic tables would probably be:

- Client (consignees and consignors)
- Contractor (drivers and owner-drivers)
- Vehicle (rego)
- ContractorVehicle (who owns which vehicles)

If you commit to clients to perform work over time:
- Job (which client at what location)
- JobDetail (summary of what is to be moved by when)

If you give quotes before you acquire jobs:
- Quote
- QuoteDetail

Now you need to assign loads to contractors:
- Assign (header record of the driver and date)
- AssignDetail (items assigned)

Now when they complete the assignment, they submit dockets:
- Docket (the docket completed by the driver or weighbridge)
- DocketDetail (items moved on this dockete)

If you invoice the clients for the jobs:
- Invoice (header record for the client and period)
- InvoiceDetail (items on this invoice)

If you also generate RCI (recipient created invoices) for the contractors:
- RCI (header records)
- RCIDetail (items)

There may also be tables for:
- Product (what you move)
- ProductRates (price for each product per unit of measure)
- UnitOfMeasure (e.g. ton, cu yard, each, ...)
- VehcileType
and so on.

 

[Guest]

I agree - for now Just need to deal with the loads and assigning trucks to
move the loads - have the invoicing, products,quotes handled. Don't need to
worry about tying all together yet. Any input on how to handle -above.
subform with subform- not sure exactly how to layout the assigning trucks and
over assigning.

Thanks,
Barb

 

[Allen Browne]

Okay, to flesh out a couple of the suggested tables a bit more:

Job table:
JobID AutoNumber primary key (PK)
JobDateTime when you receive this request
DueDateTime when the entire job must be complete
Quantity how much to be moved.
UnitID what unit (tons, cu ft, ...)

Assign table:
AssignID autoNumber primary key (PK)
JobID which job this load belongs to. Required.
ContractorID which contractor this load is assigned to.
AssignDateTime when you assigned this to the contractor.
Quantity how much this contractor will move

The interface will be a main form bound to the Job table, with a subform
bound to the Assign table. When you first get a job, enter it in the main
form. As you break it down into loads and assign it to various contractors,
enter records in the subform. Once the sum of Quantity in the subform equals
the Quantity in the main form, the entire job has been assigned.

 

[Guest]

Thanks for the input.

Would like to be able to see ALLof the (Assigned)Jobs for the Day on the
main form - how I intially set it up-since is the main form only see one job
and subform shows all contractors assigned to that ONE job. Two main things
that need to do is see ALL available Contactors - once that contractor is
used for the day - need to color code whick job he is assigned to and color
code the job. Thinking I need two subforms - not sure how to do it.?? See
original post for a little more clarity.

Thanks,
Barb

 

[Guest]

any ideas. Also possibly make the contractor be not available from the
dropdown if already USED on that day. - Could use one contractor however for
mulitple trips in one day for same job. But once that contractor is assigned
for a given job pull it off of drop down -(Not sure how to do that)But - may
be unassigned/assigned field check it off when don't want it to be
available?????

Thanks,
Barb

 

[Allen Browne]

If the contractor is assigned once, are they then unavailable at all that
day? Or might they be assigned to a 2-hour job, and then available later?

For a discussion on how to figure out who is already booked by timeslot,
see:
Clashing Events/Appointments
at:
http://allenbrowne.com/appevent.html

 

[Guest]

The contractor(truck#) can be assigned 5 trips for one job - or one trip for
one job. Once he is finished with the job if done early for the day will
call in and say available for another load. at end of day if hasn't called
in then was just available for what he was originally assigned to. I have a
field called Contacted&Work, Maybe need another one or use that one to help
with above issues.

Thanks for your help,

Barb

 

[Guest]

Any ideas???

The contractor(truck#) can be assigned 5 trips for one job - or one trip for
one job. Once he is finished with the job if done early for the day will
call in and say available for another load. at end of day if hasn't called
in then was just available for what he was originally assigned to. I have a
field called Contacted&Work, Maybe need another one or use that one to help
with above issues.

Thanks for your help,

Barb

 

[Allen Browne]

Yes, if you need to be able to record the fact that contractror X called at
_:_pm to say they are now available again, you will need another table for
that. It will need to integrate that into the logic, e.g. if you assign
another trip after this, it supercedes that call the contractor is now
unavailable again.

The first step in designing any database is to develop the specification of
what it needs to handle. A discussion like this one can help you clarify
what you need, but we cannot create the entire design for you in the groups.
Hopefully what we have suggested here will lead you in a useful direction.