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.

 

[Pat Hartman]

Your relationship is set up backwards. One person is designated as the family head and all other family members relate back to him.

tblPerson
PersonID
FirstName
LastName
FamilyID (foreign key to tblPerson.PersonID)
Address1
City
State
Zip
etc.


If you want to create geneologies, then instead of FamilyID, you would have MotherID and FatherID. Other relationships can be derived from parentage. If you want to create non-family relationships such as marriage, you need a relation table since this is a many-to-many relationship. In this case instead of the normal three table setup, you would need only two tables since the person table would be used twice (both sides of the relationship).

When you create relationships or queries with self-referencing tables, you add the table to the grid twice. Access suffixes the second instance with _1.

 

[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

 

[Pat Hartman]

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

You aren't disagreeing with me. Read the rest of my response. From the question, it sounded like the poster was trying to maintain a mailing list for a family such as might be needed by a school or church. Of course it turns out he was really asking about wigets so the answer is completely different.

A bill of material is generally implemented with two tables which in this case is a many-to-many relationship to itself so one table is used twice in the relationship.

tblParts
PartID
Nomenclature
etc.

tblComponents
ParentPartID fk to tblParts.PartID
ChildPartID fk to tblParts.PartID
Quantity

 

[neileg]

OK Pat, understood.