Tables & Relationships: Tracking Unique Records

My first reaction is that you need to build a simulation model. While this can be done in Excel, a simulation program like i-Think makes life a lot easier (also more expensive if you don't have access to a licensed copy).

If I've misinterpreted, and you are simply looking for a way to plan actual assignments and track them on a daily basis then the following is not applicable. All you need a a table design that perhaps uses conditional formatting to flag what's available and what's already booked. If this is the case, please post back.

To build something automated in Excel, you need to think in terms of time increments. How often do drivers change vehicle assignments (every half hour on average, hour, ten minutes)? You also need to provide information (or decision rules) on how to assign drivers to trucks, how long each truck assignment will tie up that truck and driver, the miles it will put on the truck (may be redundant with time if average speed is always the same).

Unless you want to make a manual assignment decision each time-initeration in the model, then you need to build in rules to make assignments and bookkeeping variables to track what is occupied and until which upcoming time increment.

You can use successive sheets (or tables in a sheet) for successive time increments, or (usually better) write VBA code to do the calculations and then write time-increment outputs to successive rows or columns in a sheet. Then (if you've captured nad recorded the needed variables) you can sum total miles or whatever else you need from the resulting table. If you build appropriate randomness into your assignment decisions and assignment durations then you can run the model multiple times to get and estimate of variance. (Use VBA to capture and store the results of each model run to build charts of the outputs from 100 runs or so to see the variance.)

Hope these thoughts are are some value.

If you need to work in Access, then someone else needs to jump in here to help. I occasionally pull data from Access, but am a total novice when it comes to working within it.

Several Access gurus do hang around here on OzGrid, so don't give up hope for soem meaningful help.

Good luck.

How would anyone, let alone Access, know when a truck or driver was free to be assigned. In the analogy a new box would be needed to put unassigned drivers in so one could then find an empty truck box for the unassigned driver to go into. In access a similar thing would have to be done. Records would need to be entered in free truck and free driver table that said when a truck or driver was free. Then a macro would need to be written that checked any potential assignment, other than to the free box, to ensure that the proposed assignment matched a free driver to a free truck. If so, the match would be made and the free records deleted for the assignment. Sounds doable.

Some thoughts:

tbl_Drivers, tbl_Trucks - easy enough.

tbl_DriversTrucks - a historical record of who / where with sign in & sign out columns.

select free trucks: select * from tbl_Trucks inner join tbl_DriversTrucks where tbl_DriversTrucks.SignedBack is null

select free drivers - as above

To assign a driver to a truck, use a form to pick from the two select free ~ queries and a bit of code to update signedback field, then append driverID, truckID and signedout as new record.

This avoids overlapping truck / driver assignments.

You could also select busy truck, select busy driver the same way.