Relating tables

[Susanne]

I have a table with action items and one to hold the relationship between
them. I want to be able to relate each action item to more than one.

AI Table: AI_PK ... other fields
AI Related: AI_PK, AI_RelatedPK

I have no problem relating these and getting the info into the table. My
problem arises, and I am having difficulty, figuring out how to have it go
the other way as well. What I mean is, if AI_1 is related to AI_3 and AI_10,
I want AI_10 to display AI_1 and AI_3 as well as AI_3 to display AI_1 and
AI_10 (the AI_#'s represent the primary/foreign key field values and are not
names of fields).

I've tested with an append query and got the appropriate information, just
that if it is run again, I then end up with too many duplicates and a mess.

Any easy solutions?

 

[Mike Painter]

You may want to create a "self join"
http://www.databasedev.co.uk/self-join_query.html gives an overview.