Associations between Records

[stevepain]

Dear all,

I have an equipment database, within which I am attempting to create associations between various items. The items are distinguished as level 1, 2 or 3 depending upon where they sit in the equipment hierarchy, level 2 being sub-equipments of level 1, and level 3 being sub-levels of 2.

So for example the level 1 item could be PC, level 2 items would be keyboard, mouse, monitor, base unit etc.

What is the best way to structure this relationship? I had considered generating a sub form, and have set up queries to generate lists of potential items with each level description - so that users can select the items they want to add to the associated list of equipment, but I'm not sure how/where to generate that associated equipment list.

Hope this makes sense?

Thanks in advance for any help.

Steve

 

[The_Doc_Man]

1. Before taking this too far, read up on the topic of normalization. You are going to have a nightmare on your hands if you don't.

2. How you build this depends on some fine points. For instance, can any one item be in multiple levels at once? I.e. Can the same item stand by itself AND be a part of a larger assembly sometimes? I.e. how "pure" are your level numbers? And can you ever have more than 3?

3. For "pure" level numbers, there is no problem with having parent, child, and grandchild tables. Forms can have sub-forms, which in turn can have sub-forms. OR, you may wish to have the appropriate sub-form "popup" to allow for less cramped data entry. On the other hand, if you have "impure" levels, you have to be VERY careful because of the ambiguity. Because then you need to know whether you are adding a new item at level 1, 2, or 3.

4. I suggest you search this forum ("Search" is on the Forum's menu bar 3rd from right) for topics such as "hierarchies" and multi-level forms. You can also get useful info from Access Help.

 

[stevepain]

Thanks Doc Man - unfortunately the same bit of kit can appear at different levels - sounds like it is as complicated as I feared it might be. Cheers for the guidance though, I might have to think about ways around it!

Steve

 

[Pat Hartman]

The typical structure for a BOM is a part table and a relation table which creates assemblies.

tblAssembly
AssemblyID (foreign key to part table)
ComponentID (foreign key to part table)
Quantity
etc.

All parts - end items, components, assemblies, details are defined in the part table. Where they end up in any particular hierarchy defines what level they are. The suggested structure allows for infinite levels although you'll need to define the number of levels you need by how many times you include the tables in the join. Search for help with BOM or bill of material for more info.