Someone must have coded forms and modules for inventory lookup - Where one inventory item is made up of several inventory items from the same table. this is a many to many relationship, thus a connector table must be used, with two keys, one for the master, one for the child. Also the master-child relationship must also display where the child is the master and the master is the child: e.g Part 1 is made up of parts 2, 7 and 9, part 2 is used in parts 1, 5 and 7.
to save me from re-inventing the wheel, are there examples of forms and modules available?

David

Look at Employee Tables, especially the one with a recursive Treeview example from MS. An employee can be the supervisor of another employee. The treeview shows it in a graphical format.

THis may not be what you are looking for though. I have done two databases that needed similar associations, but not into multiple layers.

I did a Configuration Management database that had a Build. Inside the build was Documents, Froms, Test Procedures, Drawings, etc. So I gave each type its own table for associating:

Tbl_DwgBuild
BuildID
DwgID

Tbl_Form
BuildID
FormID

etc.

I used a form based on the BuildID from Tbl_Build, have two listboxes on the form. The left box had the available forms, docs, dwgs, etc. (populated via an option group). Then pressing a command button associates the two together, and shows it in the right listbox using a Union query to bring in all of the different tables information.

I don't remember why I used so many tables though. For a training table, I just used a Tbl_AssociateTraining with:

TrainingID
EmployeeID

but used the same two list box format.

I could probably provide an example, but i don't know how applicable it is to parts references that can be circular and numerous.

Sean.

I work with a couple tables that do what you want. My Stock table lists all of the information about each stock item. It includes the following fields and sample data:
   Item ID Composite
   ChairD N
   ChairKit Y
   ChairP N
   Headrest N
   MountBrkt N

I also have a table called Composite with the following fields and sample data:
   Key Composite ID QTY Item ID
   1 ChairKit 1 ChairD
   2 ChairKit 1 ChairP
   3 ChairKit 2 MountBrkt
   4 ChairKit 2 Headrest

The Stock table tells us that ChairKit is a composite or kit. This tells my program to look at the Composite table to find which parts and how many of each to remove from stock when I sell a ChairKit.

The composite table tells us that the ChairKit consists of a Driver's Seat, A passenger's Seat, two mounting brackets, and two headrests.

This should be the basic answer for what you are looking for.




David Seibold