"Circular" Relationship

[PeteyPueblo]

I am currently restructuring the database at my company to allow for more
efficient use. I have the restructuring almost completely done, but I am
stuck on one design issue. Here are the basics of what I have:

We have fabricated parts, which are linked in a many to many relationship in
a junction table to the weld assemblies that they are a part of. This is
because many fabricated parts can be used in many different weld assemblies.

It was recently brought to my attention that there are weld assemblies that
are used in other weld assemblies. I tried to create a junction table with
two primary keys that relate back to the same weld assembly table, however, I
cannot enforce referential integrity with this relationship.

Has anyone solved a problem similar to this before? I have seen several
examples online of employees who manage other employees, but I am not sure
how to implement this kind of relationship into an Access environment.

 

[Fred]

We do that.

The linked fields in junction tables should be FK's, not primary keys.

This is one case where it is particularly useful to recognize that a
junction table is more than an administrative tool to do a M to M
relationship. Each record in it is a record of a relationship between two
records(in your case, between two parts) . For BOM's, it's typically a
record of the fact that that "A" is used to make "B" You'll put all of
your parts into one parts table (presumably PK = Part Number) , and link
"field A" and "field B" in your junction table to the part number field in
your parts table. .

This isn't your classic "circular" relationship where a table is linked to
itself, but I guess you could this two-table loop circular.

 

[KARL DEWEY]

I think you need what is known as a 'Self-join'. An example would be an
employee list that included a Foreign Key field for Supervisor. Supervisors
would themselves be employees and be in a one-to-many relationship with
employees.
In the Relationship window add the table twice. Access adds a sufix of '_1'
to the table name of the second object.
Click on the Primay Key field of first table and drag to the Foreign Key
field of the second table. Select Referential Integerity and Cascade Updates.

 

[Fred]

I think that this is a many-to-many relationship. One part can be used to
make many different parts, and one part can be made up of several different
parts.

So, in my opinion, Karl's good & detailed advice applies, but an with the
addition of an intervening junction table.