Tables & Relationships: Tracking Unique Records

  • I hope one of you Access gurus can help me with a table relationship problem:

    Say I've got 3 drivers; Alf, Bert and Charlie, and 5 trucks; T1 to T5.

    Each driver can only be assigned to one truck at a time, ie if there's a record for Alf on T1, I can't raise a new record for Alf on T2 until I've removed him from T1.

    The same thing applies to the trucks; if T1 is assigned to Charlie, I can't assign Bert to T1 until I take Charlie out of its cab.

    How should I set up the Tables/Relationships so I can track the following:


    1. Cumulative Total Miles.
    2. Cumulative miles on each truck.


    1. Cumulative Total Miles.

    All without allowing any driver/truck conflicts?

    The actual application will be more complex, and not truck-related, but that about summarises what I need to do - can it be done?


    "Varium et mutabile semper Excel"

  • 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.

  • Thanx for that Thomach, but it's Access I need to use.

    Perhaps if I put it this way..

    I want to have a record card for each driver on which I can list the trucks he's driven and how many miles he drove on each.

    I also have 5 card boxes - 1 for each truck.

    Now if I assign Alf to truck T1, I put his card in T1's box, Bert's in T3 (or wherever) and Charlie's card is on the desk because he's on holiday.

    The next day/week/month, Truck T1 goes in for service so Alf gets reassigned to T5.
    During all the time he was using T1, it and he accumulated (say) 300 miles, so that gets written on his card under T1, and added to his running total.

    It's kind of like trying to replicate a T-card system in Access, I guess.

    Does that clear the mists a little?


    "Varium et mutabile semper Excel"

  • 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.

  • Of course, you're absolutely right - my mistake - Doh!!
    I'll try posting on tek-tips or something!

    Thanks again for your help!



    "Varium et mutabile semper Excel"

  • 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!