Many to Many relationships within table

[cooke.christopher]

I am mapping the interaction between a large list of research
activities. I have the table StateActivities which contains the data
of interest here. Each of these activities could be linked to any
number of the other activities in the same table.

I know this is a simple problem but I can't crack it:

In my main data form, I view each activity and need a subform where I
select the related activities. It seemed simple enough to use a join
table to list each pair: two columns, one for each ID. The trouble is
this: While viewing the record Activity # 7, I can link it to #'s 3, 6
and 9. But then, when viewing say Activity #9, I don't see the
already created relationship with #7 because the subform is looking
for #9 in the first column of the join table only. I need it to look
at both columns and show me the full list of relationships.

Another way of saying it that the relationships flow both ways, it
doesn't really matter which column the reference ID is stored I need
to see it.

 

[MaRSMAN]

Thanks for your input yet another ?
Can one create forms subforms quiers before creating the relationships and
also after some records are inputted into the forms marsman

 

[Allen Browne]

You have a many to many relationship between activities. That implies you
need another table to track which activities are related other ones. The
best solution will depend on how the activities relate to each other.

Perhaps you are grouping activities together, e.g. activities 7, 9, and 24
might be '6 year old boys races.' You need 2 tables to record this:
- tblGroup, with fields:
GroupID AutoNumber primary key
GroupName Text e.g. '6 year old boys races'
- tblGroupActivity, with fields:
GroupID Number relates to a record in tblGroup.GroupID
ActivitityID Number relates to a record in your main table.
So, this table will have 3 records for GroupID 1.

In this way, that activities are related to each other through the groups
they are part of.

There are other possibilities, but I think that's the most flexible.

 

[cooke.christopher]

You have a many to many relationship between activities. That implies you
need another table to track which activities are related other ones. The
best solution will depend on how the activities relate to each other.

Perhaps you are grouping activities together, e.g. activities 7, 9, and 24
might be '6 year old boys races.' You need 2 tables to record this:
- tblGroup, with fields:
    GroupID        AutoNumber  primary key
    GroupName    Text        e.g. '6 year old boys races'
- tblGroupActivity, with fields:
    GroupID        Number        relates to a record in tblGroup.GroupID
    ActivitityID     Number       relates to a record in your main table.
So, this table will have 3 records for GroupID 1.

In this way, that activities are related to each other through the groups
they are part of.

There are other possibilities, but I think that's the most flexible.

Thanks for the idea. Unfortunately, there are not static groups like
the ones you describe. The relationships are more free-flowing. A
good way to visualize this is a network map. Each activity is a node
with connections to any number of other activities. When viewing any
one node, you can see all the connections to it regardless of the
"direction" they were created in (i.e. which column of the join table
they occupy). Any groups should be an emergent property of the
network.

 

[Allen Browne]

If the relationship you are seeking to define are ad hoc connections between
pairs (e.g. activity 7 is related to activity 9, activity 7 is related to
activity 11, but that does not imply any connection between 9 and 11),
perhaps you could just use a table with fields:
Activity1 foreign key to Activity.ActivityID
Activity2 foreign key also
Primary key is the combination of the 2.

Then use a UNION query to get all the activities:
SELECT Activity1 AS Source, Activity2 AS Target
FROM tblActivityActivity
UNION
SELECT Activity2 AS Source, Activity2 AS Target
FROM tblActivityActivity;

If you now use 7 as the Source value, you can see all the targets.

Using UNION (rather than UNION ALL) deduplicates it if you did happen to
define the relationship both ways.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

You have a many to many relationship between activities. That implies you
need another table to track which activities are related other ones. The
best solution will depend on how the activities relate to each other.

Perhaps you are grouping activities together, e.g. activities 7, 9, and 24
might be '6 year old boys races.' You need 2 tables to record this:
- tblGroup, with fields:
GroupID AutoNumber primary key
GroupName Text e.g. '6 year old boys races'
- tblGroupActivity, with fields:
GroupID Number relates to a record in tblGroup.GroupID
ActivitityID Number relates to a record in your main table.
So, this table will have 3 records for GroupID 1.

In this way, that activities are related to each other through the groups
they are part of.

There are other possibilities, but I think that's the most flexible.

Thanks for the idea. Unfortunately, there are not static groups like
the ones you describe. The relationships are more free-flowing. A
good way to visualize this is a network map. Each activity is a node
with connections to any number of other activities. When viewing any
one node, you can see all the connections to it regardless of the
"direction" they were created in (i.e. which column of the join table
they occupy). Any groups should be an emergent property of the
network.

 

[cooke.christopher]

If the relationship you are seeking to define are ad hoc connections between
pairs (e.g. activity 7 is related to activity 9, activity 7 is related to
activity 11, but that does not imply any connection between 9 and 11),
perhaps you could just use a table with fields:
    Activity1    foreign key to Activity.ActivityID
    Activity2    foreign key also
Primary key is the combination of the 2.

Then use a UNION query to get all the activities:
    SELECT Activity1 AS Source, Activity2 AS Target
    FROM tblActivityActivity
    UNION
    SELECT Activity2 AS Source, Activity2 AS Target
    FROM tblActivityActivity;

If you now use 7 as the Source value, you can see all the targets.

Using UNION (rather than UNION ALL) deduplicates it if you did happen to
define the relationship both ways.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.







Thanks for the idea.  Unfortunately, there are not static groups like
the ones you describe.  The relationships are more free-flowing.  A
good way to visualize this is a network map. Each activity is a node
with connections to any number of other activities.  When viewing any
one node, you can see all the connections to it regardless of the
"direction" they were created in (i.e. which column of the join table
they occupy).  Any groups should be an emergent property of the
network.

Sounds like its exactly what I was looking for. I'll try it out,
thanks.