Linking a table to itself

[ferrets]

I have a table and I need to link records within that table to other records within the same table. So for example a people table and I want to be able to link John to Andrew, Mike to John, Andrew to Mike etc.

When I bring up a record in a form I need to be able to see in the subform everybody who is linked to them. If I set up a link table with Person_ID1, and Person_ID2 then I am guessing that I would only see John's link to Andrew, but I wouldn't see Mike's link to John until I opened up Mike's record. Is there anyway I can set this up so that I can see all of John's linked friends regardless of whether John appears in the first or second link record?

Hope that makes sense!

[kd2017]

So given a form opened up on the record for John how about a subform based on a query like this:
SELECT * FROM [you're bridge table name here] WHERE Person_ID1 = [John's ID here] OR Person_ID2 = [John's ID here];

The [John's ID here] part would be a reference to a relevant ID field on the parent form.

[pbaldy]

In a query, you'd add the person table twice. Access will alias the second instance with something like Person_1, which you can change. Then you join each of your fields to a different instance of the table.

[kd2017]

In a query, you'd add the person table twice. Access will alias the second instance with something like Person_1, which you can change. Then you join each of your fields to a different instance of the table.

I hope I'm not out of line for asking questions even though I'm not OP, but now I'm slightly confused.

I'm guessing the problem is they're using a many-to-many relationship table, a person's primary key could be found in one of two different foreign key fields in the many-to-many table. So what's the best way to to select ALL of a person's friends?

Maybe like this:

SELECT Friendships.PersonID1 AS FriendID
FROM Friendships
WHERE Friendships.PersonID2=[Forms]![Person]![ID]

UNION

SELECT Friendships.PersonID2 AS FriendID
FROM Friendships
WHERE Friendships.PersonID1=[Forms]![Person]![ID]

or this:

SELECT IIf([PersonID1]=[Forms]![Person]![ID],[PersonID2],[PersonID1]) AS FriendID
FROM Friendships
WHERE Friendships.PersonID1=[Forms]![Person]![ID] OR Friendships.PersonID2=[Forms]![Person]![ID]

I'll shut up now until OP chimes back in.

[ferrets]

That's right. I can see all of John's links where he appears in PERS_ID1 but I can't see his links when appears in PERS_ID2 (at least not when looking at his record in the main form and looking at his links in the subform). Is it possible to post a test version of the database so that perhaps you could have a look?

[pbaldy]

You should be able to attach it after a compact/repair and zipping.

@kd, no you're not out of line and I may have misunderstood the question. Wouldn't be the first time, nor likely the last.

[ferrets]

If you open up the People form field you will see that John is linked to Steve and Matthew. But when you go to record 2 you see that Allan is also linked to John.

What I need to be able to see is everybody that John is linked to, whether he appears in Pers_ID1 or Pers_ID2. SO when I look at John's record I would like to be able to see links to Allan, Steve and Matthew.

People Links.zip
People Links.zipPeople Links.zip

[CJ_London]

use a union query

Select PersA, PersB FROM tblPersons WHERE PersA='John'
UNION Select PersB, PersA FROM tblPersons WHERE PersB='John'

[ferrets]

use a union query

Select PersA, PersB FROM tblPersons WHERE PersA='John'
UNION Select PersB, PersA FROM tblPersons WHERE PersB='John'

Apologies, I'm not being very clear. I use John as an example. I need a form with a subform. For whatever person is in the top of the form, I need to be able to see all of their links in the subform, regardless of whether that person appears in the link table as Pers_ID1 or Pers_ID2

[CJ_London]

the union query would be the recordsouce to your subform with the first column being the link to the record in the main form. You may need to add other fields to display in the subform, I'm just providing the principle you would use

[CJ_London]

Based on your attachment, the recordsource for your subform would be

SELECT  *
FROM (SELECT PERS1_ID, PERS2_ID, Link_Type FROM People_Link
UNION SELECT PERS2_ID, PERS1_ID, Link_Type FROM People_Link)  AS R INNER JOIN People ON R.Pers2_ID = People.Pers_ID;

my example is highlighted in blue