Create a "family tree" style flowchart report based on Mentor/Mentee relatoinships

[prsaddict]

Howdy!



I've got what I believe is a complicated goal, and I'm hoping there's some super power users out there who can help point me in the right direction (and that's assuming what I'm trying to accomplish is even possible!)

I have built an access database that keeps track of our Mentor groups. Each group has 1 mentor and 2 "mentees". However, it is possible for a Mentee to be the mentor of another group, thus creating a new "generation" of mentorship.

What I'd like to do is, with the click of a button, launch a flowchart report depicting this "family tree" of mentor/mentee relationships. I would love to see the groups arranged via connected line like a family tree of mentors and mentees!

Possible??? Does anyone have any ideas on what a solution, whether within Access or perhaps an additional program?

Thanks!

[orange]

This seems to involve a self join since a person could be a Mentor and a Mentee.
I'd start with a table along these lines.

tMentorInfo
PersonId PK
FirstName
LastName
HasMentor <----this is NULL for the top most mentor. It is the PersonID of this person's mentor.
OtherInfoYouMayNeed

PersonID
FirstName
LastName
HasMentor

1
mentor
TopDog



2
Jim
Smith
1

3
Sam
Adams
1

4
Amanda
Hugg
3

5
Cody
Ackbear
4

6
Jane
Dough
3

Let's consider some people

Mentor TopDog is the head honcho No Body mentors him/her. The associated record is:

1... Mentor... TopDog

To see who is mentored by whom (the Mentees), a query such as

SELECT tMentorInfo.PersonID, tMentorInfo.FirstName AS Person
, tMentorInfo.LastName, tMentorInfo.HasMentor
, [tmentorInfo_1].[firstName] & ' ' & [tmentorinfo_1].[Lastname] AS IsMenteeOf
FROM tMentorInfo AS tMentorInfo_1 INNER JOIN tMentorInfo
 ON tMentorInfo_1.PersonID = tMentorInfo.HasMentor
WHERE ((Not (tMentorInfo.HasMentor) Is Null))
ORDER BY tMentorInfo.HasMentor;

PersonID
Person
LastName
HasMentor
IsMenteeOf

3
Sam
Adams
1
mentor TopDog

2
Jim
Smith
1
mentor TopDog

6
Jane
Dough
3
Sam Adams

4
Amanda
Hugg
3
Sam Adams

5
Cody
Ackbear
4
Amanda Hugg

To see who are the Mentors:

SELECT tMentorInfo.PersonID
, tMentorInfo.FirstName AS Person
, tMentorInfo.LastName
, [tmentorInfo_1].[firstName] & ' ' & [tmentorinfo_1].[Lastname] AS IsMentorOf
FROM tMentorInfo INNER JOIN tMentorInfo AS tMentorInfo_1 
ON tMentorInfo.PersonID = tMentorInfo_1.HasMentor
ORDER BY tMentorInfo.PersonID;

PersonID
Person
LastName
IsMentorOf

1
mentor
TopDog
Sam Adams

1
mentor
TopDog
Jim Smith

3
Sam
Adams
Jane Dough

3
Sam
Adams
Amanda Hugg

4
Amanda
Hugg
Cody Ackbear

To find the person who is head of the group---ie does not have a mentor

SELECT tMentorInfo.PersonID
, tMentorInfo.FirstName
, tMentorInfo.LastName
, tMentorInfo.HasMentor
FROM tMentorInfo
WHERE (((tMentorInfo.HasMentor) Is Null));

I hope this is useful and offers some ideas.

Good luck.