Can a table have a relationship with itself?

[BlurryEddie]

How can I have table data refer to other data in the same table? For example: If I have a table of people I know, and I want to 'link' family members together, and these other family members are also listed in the table of people I know.
Table: People
Field#1: PersonID
Field#2: PersonName
Field#3: FamilyMember1 (using the PersonID)
Field#4: FamilyMember2 (using the PersonID)

I hope I've explained myself clearly.
Thanks for any help.
-E

 

[aleb]

Simply add the same table to the query ... and link your Field#3 ... to the personID

 

[neileg]

The way you have set up your table is quite normal. You don't set up a relationship in the Relationship Diagram, but you make the connection in any queries you create.

I have a query that creates a pedigree for our cats (a family tree on its side!) and this has my main table added in 31 times for the 31 family members.

 

[neileg]

I wouldn't normally disagree with Pat, but on this one I do.

I believe that the correct way to store this type of data is to hold the IDs of the mother and father of the person, which is what I assumed the FamilyMember fields are doing. In my application, I assign a dummy mother and dummy father to the oldest persons (when I don't know the true parents). By doing this, I can update the ancestors when this data becomes available.

 

[autocol]

What about if there's an unknown (and very large) number of relationships within the same table?

I'm trying to construct a similar thing to this family tree, but with components, rather than people.

A bolt, for example, is a part, which is also in many other assemblies (which are listed as parts in the same table)... those assemblies may be included in ANOTHER assembly, which is also a "part", in the parts table.

I've managed to make a relationship that allows me to link a part to another part (making it effectively a sub-part), but I can't figure out how to make it link to MANY other parts (the total number of relationships needs to be VERY large - ie, a part could end up a sub-sub-sub-sub-part in one assembly, and a sub-part in another - so I couldn't link the table again, every time...)

Any thoughts?

 

[BlurryEddie]

Does copying the table make sense?

I appreciate the advice Neileg, Pat, and Aleb. Since I'm no db pro, I'm trying to make sense of it all. I know this may not be the 'correct' way, but since it's only for personal use, do you think it makes sense just to copy the entire table of names. Then have the first table have a one to many relationship with the second table. This way, when I query or do a report, everthing is simple and straightfoward. Obviously the problem would be that I would have to update both tables with any new data.

 

[neileg]

Nope, that's the wrong way. I'll knock up a quick sample of how this should work and post it this morning.

Edit: There you go. One table, one query, one family tree,

 

[BlurryEddie]

Thanks again

Ed

 

[neileg]

OK Pat, understood.