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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

This should be split into multiple tables. That is why it isn't working well.
tblPerson should have the person ID
tblFriend would have

Then you could select as follows
from tblPerson p INNER JOIN tblFriend f ON p.ID=f.PersonID

Open in new window

For each relationship there would be a record in tblFriend.
Dale FyeCommented:
Are you assuming that you already have a table similar to the first one indicated, and you want to generate records for the inverse AA=>BB  and you want to add the BB=>AA record.  Or do you want to do this automatically as you initially add the relationship?

You could easily generate the inverse records with an append query:

INSERT INTO yourTable (PersonID, HasFriend)
SELECT HasFriend, PersonID from yourTable

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".  I also would not create a table that contains the concatenated friends, like you are showing in table #3.  If you really need to display data in that manner, you could write a query to do that.
TarasAuthor Commented:
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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

TarasAuthor Commented:
And to add regarding first table I already have that table and procedure for populating it.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
It is assumed that that connection- relation between all members of group exists.
Then you need to add People to a Group and show the results for those Groups.

And I agree with the other comments as well - storing multiple values in a single field is a bad idea, and will cause you no end of troubles. Normalize your database, and you'll find things will work much better (and be much more extensible).
I agree that one way relationship doesn't imply the other. In fact you don't even show a person AA in the list.
Dale FyeCommented:
#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
SELECT T1.Person_ID, T1.Has_Friend, 1 as Degree
FROM yourTable T1
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
SELECT Person_ID, Has_Friend as Friend
FROM yourTable
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.
TarasAuthor Commented:
I still did  not get answer. If I have temp table as first one :


Friendship_ID       Person_ID       Has_Frined
      1             AA             BB
      2             AA             CC

how to get from first table second table that looks like bellow :


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.
TarasAuthor Commented:
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:-)
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.
Dale FyeCommented:
In this case, I think I would recommend against "groups"

As I said, before, I would not create that 2nd table, I would use a query, actually 2 to get to that result.  I've tweaked what I provided above, and actually gotten a chance to test it.  The first query (qry_Reciprocal) would look like:

SELECT DISTINCT Temp.Person_ID, Temp.Friend
FROM (SELECT Person_ID, Has_Friend as Friend
FROM tbl_Friendships
SELECT Has_Friend, Person_ID as Friend
FROM tbl_Friendships
)  AS Temp;

And would give you:


The second query, would use (qry_Recipocal) to give you the 2nd degree friendships:

SELECT Temp.Person_ID, Temp.Friend, Degree
SELECT qry_Reciprocal.Person_ID, qry_Reciprocal.Friend, 1 as Degree
FROM qry_Reciprocal
SELECT qry_Reciprocal.Person_ID, qry_Reciprocal_1.Friend, 2 as Degree
FROM qry_Reciprocal INNER JOIN qry_Reciprocal AS qry_Reciprocal_1
ON qry_Reciprocal.Friend = qry_Reciprocal_1.Person_ID
ORDER BY qry_Reciprocal.Person_ID
)  AS Temp
WHERE (((Temp.[Person_ID])<>[Friend]))
ORDER BY Temp.Person_ID, Temp.Friend;

This would give you your 1st and 2nd degree friendships.  Once you have that query working, you could change it from a SELECT to a Make-Table query if you really feel you must have the 2nd table.  But if you do that, every time you add a friendship, you will have to recreate that table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TarasAuthor Commented:
Thanks to all  I got idea.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.