Create table
In Ms access 2010I have table friendship tblFriendship.
It has three fields Friendship_ID ; Person_ID ; Has_Friend;
First I inserted in table next values:
Friendship_ID Person_ID Has_Frined
1 AA BB
2 AA CC
Now I need logic to handle next situation :
As AA is friend with BB and CC it means that BB is Friend with AA and CC and It means that CC is friend with BB and AA. I need logic that will give me table populated as :
Friendship_ID Person_ID Has_ Friend
1 AA BB
2 AA CC
3 BB AA
4 BB CC
5 CC AA
6 CC BB
And another summary kind table that will be as :
Friendship_ID Person_ID All Friends
1 AA BB; CC
2 BB AA; CC
3 CC BB; AA
I gave example with two friends however one person could be friend with 3, 4, 5 or more people.
Logic has to handle that.
How to do this?
It has three fields Friendship_ID ; Person_ID ; Has_Friend;
First I inserted in table next values:
Friendship_ID Person_ID Has_Frined
1 AA BB
2 AA CC
Now I need logic to handle next situation :
As AA is friend with BB and CC it means that BB is Friend with AA and CC and It means that CC is friend with BB and AA. I need logic that will give me table populated as :
Friendship_ID Person_ID Has_ Friend
1 AA BB
2 AA CC
3 BB AA
4 BB CC
5 CC AA
6 CC BB
And another summary kind table that will be as :
Friendship_ID Person_ID All Friends
1 AA BB; CC
2 BB AA; CC
3 CC BB; AA
I gave example with two friends however one person could be friend with 3, 4, 5 or more people.
Logic has to handle that.
How to do this?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
And to add regarding first table I already have that table and procedure for populating it.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I agree that one way relationship doesn't imply the other. In fact you don't even show a person AA in the list.
#1. To get 2nd degree friends (friends of friends), I would use a query.
SELECT Temp.Person_ID, Temp.Has_Friend as Friend, MIN(Temp.Degree) as Degree
FROM (
SELECT T1.Person_ID, T1.Has_Friend, 1 as Degree
FROM yourTable T1
UNION ALL
SELECT T1.Person_ID, T2.Has_Friend, 2 as Degree
FROM yourTable T1
INNER JOIN yourTable as T2 ON T1.Has_Friend = T2.Person_ID
) as Temp
WHERE Temp.Person_ID <> Temp.Has_Friend
GROUP BY Temp.Person_ID, Temp.Has_Friend
This query would give you the BB=>CC relationship because BB => AA => CC, and it would indicate the degree of friendship.
#2. With regard to displaying all of the friends on the form, you should do that in a subform. You could do that with a query that looks like the following, or use the previous ones.
SELECT DISTINCT Temp.Person_ID, Temp.Friend
FROM (
SELECT Person_ID, Has_Friend as Friend
FROM yourTable
UNION ALL
SELECT Has_Friend as Person_ID, Person_ID as Friend
FROM yourTable
) as Temp
This would be the query for the subform, which you would join to the main form using a Master/Child relationship on the Person_ID field.
SELECT Temp.Person_ID, Temp.Has_Friend as Friend, MIN(Temp.Degree) as Degree
FROM (
SELECT T1.Person_ID, T1.Has_Friend, 1 as Degree
FROM yourTable T1
UNION ALL
SELECT T1.Person_ID, T2.Has_Friend, 2 as Degree
FROM yourTable T1
INNER JOIN yourTable as T2 ON T1.Has_Friend = T2.Person_ID
) as Temp
WHERE Temp.Person_ID <> Temp.Has_Friend
GROUP BY Temp.Person_ID, Temp.Has_Friend
This query would give you the BB=>CC relationship because BB => AA => CC, and it would indicate the degree of friendship.
#2. With regard to displaying all of the friends on the form, you should do that in a subform. You could do that with a query that looks like the following, or use the previous ones.
SELECT DISTINCT Temp.Person_ID, Temp.Friend
FROM (
SELECT Person_ID, Has_Friend as Friend
FROM yourTable
UNION ALL
SELECT Has_Friend as Person_ID, Person_ID as Friend
FROM yourTable
) as Temp
This would be the query for the subform, which you would join to the main form using a Master/Child relationship on the Person_ID field.
ASKER
I still did not get answer. If I have temp table as first one :
tblFriendship
Friendship_ID Person_ID Has_Frined
1 AA BB
2 AA CC
how to get from first table second table that looks like bellow :
tblFrindsihpGroup
Friendship_ID Person_ID Has_ Friend
1 AA BB
2 AA CC
3 BB AA
4 BB CC
5 CC AA
6 CC BB
I need query or script that will do it.
tblFriendship
Friendship_ID Person_ID Has_Frined
1 AA BB
2 AA CC
how to get from first table second table that looks like bellow :
tblFrindsihpGroup
Friendship_ID Person_ID Has_ Friend
1 AA BB
2 AA CC
3 BB AA
4 BB CC
5 CC AA
6 CC BB
I need query or script that will do it.
ASKER
Scott McDaniel
Something as you said.
"Then you need to add People to a Group and show the results for those Groups."
Something like that.
I have a Person in a temp table then adding to him several other persons. Then I need to make connection each one with another one in that group and put result in separate table.
Sounds simple:-)
Something as you said.
"Then you need to add People to a Group and show the results for those Groups."
Something like that.
I have a Person in a temp table then adding to him several other persons. Then I need to make connection each one with another one in that group and put result in separate table.
Sounds simple:-)
Yes. Your original table design seems bad to me. It seems it would call for people and Friends tables as I said from the beginning, or you can call it groups or whatever. All people should be in the people table. Then their relationships should be in another table (1 person can have many friends). If there is a record in the group for every relationship then the join will be simple.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to all I got idea.
ASKER
However, AA is friends with BB and AA is friend with CC, does not imply that BB is friends with CC, so I would not automatically add that "relationship".
That relationship should automatically exist.
They are belong to group of friends and they are friends between themselves too. It is assumed that that connection- relation between all members of group exists.
It means if in my application I have a form to present persons info, form “ Person” and on that form I show data for the Person AA between other fields on form I have a filed named “Friends” in that filed I need to show his friends : BB; CC.
The same is for Person BB when I bring his data on the person form in the field friends in his case I need to show CC; AA.