Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Create table

Posted on 2014-07-23
12
Medium Priority
?
146 Views
Last Modified: 2014-07-25
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?
0
Comment
Question by:Taras
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 25

Assisted Solution

by:SStory
SStory earned 500 total points
ID: 40214296
This should be split into multiple tables. That is why it isn't working well.
tblPerson should have the person ID
tblFriend would have
    ID
   PersonID
   

Then you could select as follows
SELECT p.ID,f.ID
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.
0
 
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 1000 total points
ID: 40214319
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.
0
 

Author Comment

by:Taras
ID: 40214632
fyed
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Taras
ID: 40214644
And to add regarding first table I already have that table and procedure for populating it.
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40214714
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).
0
 
LVL 25

Expert Comment

by:SStory
ID: 40214730
I agree that one way relationship doesn't imply the other. In fact you don't even show a person AA in the list.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40214757
#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.
0
 

Author Comment

by:Taras
ID: 40215685
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.
0
 

Author Comment

by:Taras
ID: 40215724
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:-)
0
 
LVL 25

Expert Comment

by:SStory
ID: 40216612
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.
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 1000 total points
ID: 40216742
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
UNION ALL
SELECT Has_Friend, Person_ID as Friend
FROM tbl_Friendships
)  AS Temp;

And would give you:

AA   BB
AA   CC
BB   AA
CC   AA

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

SELECT Temp.Person_ID, Temp.Friend, Degree
FROM (
SELECT qry_Reciprocal.Person_ID, qry_Reciprocal.Friend, 1 as Degree
FROM qry_Reciprocal
UNION ALL
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.
0
 

Author Closing Comment

by:Taras
ID: 40219384
Thanks to all  I got idea.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question