Can Access Follow the Trail of People in a Database?

[OpieWinston]

I have a growing database of currently around 700 entries where I'm record the link between 2 people. For example, column 1 is auto number, then next three columns are Person 1, Relationship to Person 2, Person 2.

I have a query where I can input a person's name and it will show me the results if that person is in either "Person 1" or "Person 2" columns. There are a lot of different family and friend relationships here though, so is it possible to have Access return every person associated with the name I put in by following links backwards? For example:

Record 1: John Doe | Parent | Jim Doe
Record 2: Jim Doe | Friend | Eric Smith
Record 3: Eric Smith | Child | Henry Smith

Is there a way to have Query John Doe and have Access show me that John Doe is linked to Jim Doe, Eric Smith, and Henry Smith?

If not, do you know of a program that can do this?

[Kyle123]

You can do t with SQL server using a recursive CTE, the express edition is free. I think you’d need to use VBA to do this in Access. Have a search for building org chart/hierarchy in access, that’s what you’re doing and it’s quite a common requirement

[OpieWinston]

Thank you much