Results 1 to 5 of 5
  1. #1
    blitz14 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2013
    Posts
    3

    One-to-one and one-to-many relationships


    Hi, I'm very familiar with MS Access but it has been some time since I've had to dedicate a lot of time to using it. I'm hoping someone could get me going in the right direction.

    Here's what I have. I have a table for SITES, another for SYSTEMS and many for INVENTORY. The sites table includes basic site information but also includes a key to link to the systems table. The systems table is also very basic with a system name, general details and a key to link the system to the other inventory tables. The inventory tables (4 in all) have different information in them. The only common denominator is the system key. Here's the issue, when I open the sites table, I can expand it to see all of the related systems. However, I want to also be able to expand each of the systems to display the inventory for that site.

    Basic Example:

    - 123 Main Street
    .....Electrical
    .....Computers
    ..........Laptop abc
    ..........PC xyz
    .....Structure
    ..........Wood
    ..........Sheetrock
    + 456 Cross Street

    The example is just that, just an example. I have expanded the "123 Main Street" to show that it has Electrical, Computers and Structure "systems". I then expanded Computers to show there is a Laptop and PC for inventory. Additionally, Structure is also expanded and shows Wood and Sheetrock under inventory. Is this even possible? Today when I try to expand "computers" it asks for child/master. If I then select the computers table and assign child/master, only the computers table information appears for electrical, computers and structure. I believe I could solve this by putting all of the inventory into a single table but because each one is different (unique questons for some of the systems), it seemed like it would be a waste to add a whole bunch of fields and only some are used for some systems. The separate tables seems to make more sense, but how do I now get them to link to the systems and up to the sites?

    I would appreciate any assistance you can provide. Whether it be with examples or pointing me in the right direction. Much appreciated!!

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It sounds like you are opening tables and viewing data using subdatasheets. IMO, this is a waste of time and confusing. I always turn off subdatasheets. Should use queries and forms to add/view data.

    The most important this is to have a good (normalized) table structure.
    Would you post a pic of the relationships? And/or the fields and field types for the tables?
    Or post the dB for analysis?

    It sounds like there is a one to many relationship between SITES and SYSTEMS.
    It sounds like there is a one to many relationship between SYSTEMS and each INVENTORY table.

    Is this close??

  3. #3
    blitz14 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2013
    Posts
    3

    Thanks

    Quote Originally Posted by ssanfu View Post
    It sounds like you are opening tables and viewing data using subdatasheets. IMO, this is a waste of time and confusing. I always turn off subdatasheets. Should use queries and forms to add/view data.

    The most important this is to have a good (normalized) table structure.
    Would you post a pic of the relationships? And/or the fields and field types for the tables?
    Or post the dB for analysis?

    It sounds like there is a one to many relationship between SITES and SYSTEMS.
    It sounds like there is a one to many relationship between SYSTEMS and each INVENTORY table.

    Is this close??
    Yes, that's exactly the case.
    one to many relationship between SITES and SYSTEMS.
    one to many relationship between SYSTEMS and each INVENTORY table.

    I agree with the subdata, I don't typically use them but the person who started the DB wanted to. I didn't know if there were a way to expand them in this manner. Since each system is different and essentially points to different tables, I would need to use a form, for example, to be able to control viewing that table. The subdata method has to be set to a specific child/master table as far as I understand it.

    Thanks, Jon

  4. #4
    blitz14 is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2013
    Posts
    3
    Do you know where I might be able to find a sample form that I described. I'm trying to do it myself but am a bit more rusty than I thought. It's not quite like riding a bike.

    What I would like is the top of the form displays the SITE information. On the page would be a list of the SYSTEMS and the there would be an area for the INVENTORY. Eventually I would like it to be dynamic, for example if someone selects the electrical system the inventory for electrical appears (the others would be hidden). Any help would appreciated, I know I'm asking a lot. If there is an example that might show an example of the one-to-many to a one-to-many would be nice or if you have a suggestion.

    The Site and Systems relate based on SiteNumber and Systems to Inventory related based on SystemNumber. So a site could be #1, with 3 systems. The 3 systems then relate to the inventory really based on type and system number (because the inventory is in separate tables not one single table). Electrical would come from the electrical table for example. Hopefully that makes sense.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Do you have an example dB with maybe some test data in it?

    Here is one idea (untested):
    In an unbound form, have two combo boxes in the header. The first combo box would select the SITE. The second combo box would be the SYSTEM, set up as cascading combo boxes.

    In the footer have a tab control with 4 tabs, one for each inventory table.
    On each of the tabs would be one subform with the inventory.

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

Similar Threads

  1. Relationships
    By brucey54 in forum Access
    Replies: 1
    Last Post: 01-04-2013, 03:28 PM
  2. Relationships VS VBA
    By Cindy morgan in forum Access
    Replies: 1
    Last Post: 11-08-2012, 07:58 PM
  3. Relationships
    By hithere in forum Database Design
    Replies: 1
    Last Post: 04-03-2012, 11:03 PM
  4. Relationships
    By snowboarder234 in forum Database Design
    Replies: 14
    Last Post: 02-29-2012, 04:53 PM
  5. Relationships
    By funkygoorilla in forum Database Design
    Replies: 3
    Last Post: 10-30-2011, 10:30 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