linking table to itself?

[thadson]

Hi,

How can I handle data in access that relates to itself:

example:

I have a device.
The device has drawings and parts as follow:

main drawings:
draw1 (device1)
draw2 (device2)
draw3 (box1)
draw4 (box2)

sub drawings and parts:
under draw1:
draw5 (PCB)
draw6 (electric outlet)
part1 (capacitor1)
part2 (resistor1)

Under draw 2:
draw6 (electric outlet)
draw7 (antenna1)
part2 (resistor1)
part3 (resistor2)
part4 (capacitor2)

Under draw6
part1 (capacitor1)
part5 (bridge1)

I want to have all the drawings in 1 table, as sometimes the subdrawings actually become main drawings. (ie I need to make only a PCB, not the whole device)

I want the parts in 1 table. (ie resistors, capacitors, bridge etc...)

I want to relate them in many to many relationship:
device <-> draw <-> draw <-> part

A device can have many drawings and a drawing can belong to many devices; a drawing can have many drawings that belong to it and those sub drawings can belong to many main drawings including that sometimes they are the main drawings; a drawing can have many parts an and a part can belong to many drawings...

How can I relate the drawings to each other if I want to avoide repeated data?

This whole thing looks like a mess for me, and I would appreciate any pointers as I dont even know where to begin...

Thanks

Thadson

 

[Banana]

A great starting point would be to read up on "Self-Join"; if you google Allen Browne Self Join he has a a great article demonstrating this.

But in your case, you need many-many relationship, so you would need a junction table.

Example:

tblParts <- Stores the actual parts


tblPartDependency <- Stores information about which part depends on what part
PartID <- Related to PartID in tblParts
DependentPartID <- Long number; no relationship.

You then would join on DependentPartID in the query. You can't relate to two fields in one table, I think (could be wrong, though). But as long you enforce it via queries and form, data integrity shouldn't be a problem.

HTH.

 

[MatMac]

Have a look at the attached example relationships diagram.

The DevDrawList table permits a many-to-many relationship between Devices and Drawings.

The DrawPartList table permits a many-to-many relationship between Drawings and Parts.

The DrawDrawList table permits a many-to-many relationship between Drawings and other drawings (there is only 1 drawings table).

You'll see that I've called the second field in this table SubDrawingID. Putting drawing 1's ID in DrawingID and drawing 2's ID in SubDrawingID, means that drawing 2 is a subdrawing of drawing 1.

Pairs of drawing ID's can be entered any way round, meaning that any drawing can, if you wish, be a main or sub drawing to another. And you have complete flexibility for as many nested relationships as you need.

Cheers

Mat.

 

[thadson]

Thanks Mat for the reply.
Which version of Access did you use.
My Access 2000 can not open the sample DB saying that it needs something newer.
Please let me know which one.
Thanks
Thadson

 

[Rabbie]

Here is the link to for self joins

 

[MatMac]

I use 2003

Here it is again in V97

You might need to move the tables around when viewing the relationships, as they seem to have lined themselves up.

M.

 

[Guus2005]

IMHO

I would prefer the parent-child approach in one table and another one for the descriptions
That way you can create endless levels of drawings and sub drawings and sub sub drawings.

When creating a treeview this approach simplifies my queries.

 

[MatMac]

Sorry Guus - I don't understand your reply.

But I would be interested to know what you mean, if you want to explain.

Cheers

M.

 

[thadson]

Here is the link to for self joins

Thanks,
I already had that link before, but... In my case (I tried) it would not work as I could have an infinite number of many "parrents" (many) to an infinite number of "children" (many). In the cases of horses, you can sove the problem with that ( I might add - really ingenious) solution with 2 separate fields in the same table (Sire and Dam) It is true that a father or mother can have many children but for the sake of this argument a child can have only one father and one mother. (one to many). To get it right you have to add 2 extra field to your table. In that case you are pretty limited, because every time you are trying to introduce a new family member you have to add a new column/field to your table. Try this with a few hundred uncles, aunts and other relatives and you will see what I mean. Plus, some has large families and some dont. Lets say only one family has 200 relatives. Imagine all the wasted space in your table for all the rest of the people who had only a few and now have 200 empty fields beside their name...

Thanks againg for trying to help. I appreciate it.

 

[thadson]

I use 2003

Here it is again in V97

You might need to move the tables around when viewing the relationships, as they seem to have lined themselves up.

M.

This is great stuff. What is interesting that I already come to this solution once, but I have dismissed it because I could not make a form out of it where I could start entering some devices, then add some drawings with some subdrawings and then add in the parts. What it made it all more difficult that I need to make combo boxes, so beside entering the new stuff I could choose from the already existing stuff as well.

I got the idea from that famous horse self join example with the obvious twist of the many-to-many relationship "extra table". I really appreciate the example table you have sent. It shows that I was on the right path. I needed the validation, because I thought that the way I built it could not possibly work. Many thanks.

Now back to the "drawing board". This opens up for me the same can of worms... Trying to build the query and the form for it....

Thanks again, your answer is extremely helpful.

 

[Guus2005]

Sorry Guus - I don't understand your reply.

But I would be interested to know what you mean, if you want to explain.

Cheers

M.

What is mean is that when you have one table describing the relationship between objects and one table describing the objects, that is all you need.

You need a query to build X Parent, Child levels of objects or create a recordset for the first level (ParentID=0) and then for each ChildID determine if it is a parent. That way you can create a complete treeview.

The attached sample database shows what i mean.

HTH