If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Many to Many relationships within table
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. |
#2
|
|||
|
|||
Many to Many relationships within table
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 " wrote: 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. |
#3
|
|||
|
|||
Many to Many relationships within table
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. -- 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. wrote in message ... 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. |
#4
|
|||
|
|||
Many to Many relationships within table
On Jan 30, 9:02*pm, "Allen Browne" wrote:
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. -- 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. wrote in message ... 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. 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. |
#5
|
|||
|
|||
Many to Many relationships within table
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. wrote in message ... On Jan 30, 9:02 pm, "Allen Browne" wrote: 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. -- 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. wrote in message ... 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. 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. |
#6
|
|||
|
|||
Many to Many relationships within table
On Jan 31, 9:02*pm, "Allen Browne" wrote:
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. wrote in message ... On Jan 30, 9:02 pm, "Allen Browne" wrote: 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. -- 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. wrote in message .... 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. 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. |
Thread Tools | |
Display Modes | |
|
|