Results 1 to 10 of 10
  1. #1
    Douglasrac is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    92

    Question Database Structure Advice


    Hi,

    I would like some advise on how to make my database. Actually is already done and working for months now, but I have a problem and I don't know how to solve it.

    Currently, the basic structure is like this:



    I have a client, each client have a product. T1 is Truck 1, Truck 2. Each Truck has 1 or more drivers (D1, D2).

    Every year I need to update the product, meaning the Truck will change. So I will exclude (hide) the T1 and input a new one, T3 for example. The problem is that the drivers won't change. So, if I put a new one, I need to input all drivers again, because they are related to T1. But T1 is over, expired. I dont want to modify T1 to T3, because I dont want to lose old information. So instead of replacing T1 to T3, which is actually what is happening, I create a new record, T3. But the Drivers from T1 should come with it.

    Any ideas?

  2. #2
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Are you saying T1 and T2 are seperate tables? Perhaps it would be better to show us the TABLE structure.

  3. #3
    Douglasrac is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    92
    No, they are the same table. The Structure is showing records.

    Client is one table, Ts are another table, and Ds are another table.
    T1 and T2 are two records in T table. Same for D1, D2 and so on.

  4. #4
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Then perhaps I am not understanding because it sounds like it should be add Trucks and Drivers to the Trucks table and then whenever you add a Truck the driver appears as well. OR maybe that's it you need a junction table...

    tblTruckDrivers
    tdTruckID (FK - relate to tblTrucks)
    tdDriverID (FK - relate to tblDrivers)

  5. #5
    Douglasrac is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    92
    Hm. I don't understand.
    Trucks have an ID. Drivers as well. When I add Drivers to Truck 1, those drivers are of course related to that Truck. They are T1 drivers.
    Then I add a new Truck, T3 (with different ID) and then how can I assign the same drivers to it? In the current Form, Table relationship, that is not possible.
    What I imagine is that I will need some kind of table with all drivers in it where I check the ones I want for that truck.

  6. #6
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    That is what I was saying, my suggestion is for a another table as the tables you have do not allow for your above stated scenario. With my table you can assign a driver to any truck you wish. Then when looking up the Truck you will see all Drivers assigned to that Truck.

  7. #7
    Douglasrac is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    92
    How is this junction table? What is FK?

    I already have a table with all drivers. What I need is a form that will allow me to search and check the available drivers. And also an option to include a new one when necessary.

  8. #8
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Right now you have...

    tblTrucks
    tblDrivers

    ...you want a way to add multiple Drivers to one multiple Trucks that is a many-to-many. That means a junction table...

    tblTruckDrivers
    tdTruckID (FK - relate to tblTrucks)
    tdDriverID (FK - relate to tblDrivers)

    Then making that junction table the RowSource of the combo box and setting the criteria per Truck you can see which Drivers are available AND create a *Not In List* event if the Driver is not attached to that Truck.

    Or am I misunderstanding?

  9. #9
    Douglasrac is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    92
    No you are right.

    I had to learn a bit about Many-to-many relationship. This was very helpful: http://www.techrepublic.com/article/...access/5285168
    So I need a third table.
    That is exactly what I will do.
    Thanks.

  10. #10
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    No problem, glad to help!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Database advice
    By PRINCE SWAGG in forum Access
    Replies: 29
    Last Post: 06-21-2011, 03:56 PM
  2. Replies: 1
    Last Post: 11-03-2010, 10:41 AM
  3. Developer Gone Cannot Access Database Structure
    By jonsuns7 in forum Security
    Replies: 4
    Last Post: 08-01-2010, 11:07 PM
  4. Help with Database Structure
    By scottay in forum Access
    Replies: 8
    Last Post: 06-30-2010, 08:16 AM
  5. Database Structure | Best Practice Question
    By davidson12 in forum Forms
    Replies: 0
    Last Post: 11-05-2009, 03:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums