Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking junction tables (Access 2003)

    I have a database of company vehicles. I have a table of the static data for these vehicles (reg no, engine size etc.), a table showing when the company buys and sells these vehicles who from etc. and then three tables of to allocate the vehicles - one for drivers, one for locations and one for passengers.

    I could do with some way of linking these tables so that each vehicle has to be allocated a driver and location once purchased (passengers are not always mandatory) and also so that once a vehicle is sold, the three other allocations are automatically marked as archived.

    Totally unsure on this. My tables work fine in isolation and produce the data required but there's always the chance of missing something and problems could occur as more data is entered over time.

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Linking junction tables (Access 2003)

    I suppose that the driver and location allocated to a vehicle can change over time. Does your database only need to record the current situation, or do you need to keep a history of which drivers and locations have been allocated to a vehicle over the course of time?

  3. #3
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking junction tables (Access 2003)

    Yes we do need a history of who had which vehicle, where and with which passengers. It doesn't need to be kept in the same table once no longer current though - could be archived off to another table.

    We also have situations where a group of criteria (driver, location and passengers) may just swap from one vehicle to another. At present i have to unallocate each criterion and re allocate to new vehicle - would be good to automate it.

    Thanks again.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Linking junction tables (Access 2003)

    Links between the tables, with referential integrity enforced, are useful to ensure that it's impossible (for example) to assign a non-existing driver to a vehicle, or to prevent deleting a driver who is/has been assigned to a vehicle. But to do what you want requires VBA code behind forms, links cannot take care of archiving records for example, or switching sets of allocations.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •