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

[blitz14]

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!!

[ssanfu]

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??

[blitz14]

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

[blitz14]

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.

[ssanfu]

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.