Results 1 to 2 of 2
  1. #1
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    92

    Linking records from the same table to three different tables depending on another field.

    I need your help again.



    As some of you may know, I'm designing an inventory DB for keeping track of all components, users and workstations in my organization. Since the previous inventory (half access, half excel) was organized on a 'table per component type' basis, I kept the idea and came up with a relational DB design that covers all our requirements. It is now fully functional and running but as Ajax stated in one of my threads, having a table for each component type makes it harder to add new component types to the DB since it involves the creation of a new table and the alteration of quite some lines of VB code. So, even though it's not likely we're adding more component types in the near future, I'm working on an improved version keeping all components in the same table, and all the models for whatever component type in the same 'models' table as well.

    Thing is, I'm having trouble with a particular field of the 'Components' table. This field is supposed to link the component to where it's installed. Unfortunately, that might be a location in general (table Locations), a workstation (table Workstations, linked to table Locations) or another component (namely HDDs, which are supposed to be linked to the computer they are installed on, which are on the 'Components' table too). So i though of having three separate fields on the 'Components' table and linking each to a table, filling only the appropriate one depending of the component type. But to avoid having blanks, I thought of having three separate tables ('components_Computers', 'components_Workstations' and 'components_Locations') each has a 1-1 relation to the PK of the components table and a 1-many relation to the PK of the other table, but it's giving me some headaches to update an independent combobox's source on the 'Components' form and I'm not sure this is the correct design anyway.

    What would be the best way to approach this?

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    One to one relationships are almost always a design error, there are exceptions but it's normally a poor design choice.
    The answer is normally to look at your data again and break it down.

    If you added a LocationType to your Locations table e.g. WorkStation, Computer, Other would that then Identify the sub group sufficiently?

    Perhaps you can present some sample data to better visualise the problem?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Replies: 16
    Last Post: 03-22-2018, 09:27 AM
  2. Replies: 13
    Last Post: 02-28-2016, 06:30 AM
  3. Replies: 5
    Last Post: 08-13-2015, 10:16 AM
  4. Replies: 2
    Last Post: 03-13-2013, 06:30 AM
  5. Replies: 0
    Last Post: 02-22-2013, 02:13 AM

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