question

RichardCruthirds-1707 avatar image
0 Votes"
RichardCruthirds-1707 asked RichardCruthirds-1707 answered

Need help with geneology query

I have a Person table with Maternal and Paternal IDs, that point back to the same Person table . . . when building a query to eventually print a genealogy family tree, I get to the third generation, and past that, I get "Cannot Open any more databases" . . .which I assume means I'm looping back to the original Person file too many times . . .any suggestions on how to circumvent that? I need to go 5 levels into the family tree. Using Access 365

office-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KenSheridan-7466 avatar image
0 Votes"
KenSheridan-7466 answered

The following query is adapted from a query in one of my demo files, and returns each person's antecedents down to great great grandparent level. It uses two tables, FamilyMembers and Parentage, the latter modelling the many-to-many relationship type between two instances of the former:

SELECT [FamilyMembers].[FirstName] & " " & [FamilyMembers].[LastName] AS Child,
[FamilyMembers_1].[FirstName] & " " & [FamilyMembers_1].[lastName] AS Parent,
[FamilyMembers_2].[FirstName] & " " & [FamilyMembers_2].[LastName] AS GrandParent,
[FamilyMembers_3].[FirstName] & " " & [FamilyMembers_3].[LastName] AS GreatGrandParent,
[FamilyMembers_4].[FirstName] & " " & [FamilyMembers_4].[LastName] AS GreatGreatGrandParent
FROM ((FamilyMembers LEFT JOIN (((((Parentage LEFT JOIN FamilyMembers AS FamilyMembers_1
ON Parentage.ParentID = FamilyMembers_1.FamilyMemberID) LEFT JOIN Parentage AS Parentage_1
ON FamilyMembers_1.FamilyMemberID = Parentage_1.ChildID) LEFT JOIN FamilyMembers AS FamilyMembers_2
ON Parentage_1.ParentID = FamilyMembers_2.FamilyMemberID) LEFT JOIN Parentage AS Parentage_2
ON FamilyMembers_2.FamilyMemberID = Parentage_2.ChildID) LEFT JOIN FamilyMembers AS FamilyMembers_3
ON Parentage_2.ParentID = FamilyMembers_3.FamilyMemberID)
ON FamilyMembers.FamilyMemberID = Parentage.ChildID) LEFT JOIN Parentage AS Parentage_3
ON FamilyMembers_3.FamilyMemberID = Parentage_3.ChildID) LEFT JOIN FamilyMembers AS FamilyMembers_4
ON Parentage_3.ParentID = FamilyMembers_4.FamilyMemberID;

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RichardCruthirds-1707 avatar image
0 Votes"
RichardCruthirds-1707 answered

Thanks so much . . . I'll play with this and see how it works for me . . .RC

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RichardCruthirds-1707 avatar image
0 Votes"
RichardCruthirds-1707 answered

127391-geneologylevels.jpg



I'm playing with the code you sent me, but before I change my data structure into two files, instead of the single Person file that I now have . . . can you think of a way to use the self join for this? I can get the first 6 people at the great great grandparent level, and the query works fine . . .but when I add the next, I get the "cannot open any more databases" error, which I assume is exceeding the number of "connections?" . . . I've got quite a bit of work already into the database and this query is the only item I'm struggling with to print a genealogy tree. . . thanks for your consideration


geneologylevels.jpg (103.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KenSheridan-7466 avatar image
0 Votes"
KenSheridan-7466 answered

The problem with your model is that the number of joins increases exponentially with each generation. The only way I can think of to avoid this would be to return the result table of a UNION query which replicates the Parentage table modelling the many-to-many relationship type in my model. You could then use multiple instances of this in place of the Parentage table in a query like mine. The UNION query would be like this:

SELECT "Paternal" AS ParentageType, ID AS ChildID, PatID AS ParentID
FROM Person
UNION ALL
SELECT "Maternal" AS ParentageType, ID AS ChildID, MatID AS ParentID
FROM Person;

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RichardCruthirds-1707 avatar image
0 Votes"
RichardCruthirds-1707 answered

Sorry for my late reply . . .that's the kind of input I need to address the issue . . .sorry for the dumb questions . . .I'll work on that angle of attack . .thanks again. . .

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.