Self join or vba sql to extract correct age order and relationships from single table? (1 Viewer)

[Pia]

Hello greater minds and I appreciate any help you can offer me.. .I'm desperate to resolve this after many diverse attempts.

I have a population of animals in a single table each with a PK, a code for the individual family member (text) and a code for the mother (text). The mother code only is known, fathers do not remain with the population and can't be identified. Each individual has a mother, and the family is made up of sisters, brothers (young males who have not yet left the family), cousins and their offspring in turn.

I have got a certain way with a variety of approaches:

self-joins, linking individual code with mother in each case. This produces the correct order for each 'mother' with her offspring, but I can't arrive at the desired order​

creating a hierarchy through a self join then a UNION query with subqueries at each level, producing depth, root and path for each individual in the population​

vba sql loop - I feel that this may be the answer, but I need help with this​

I need to:

1. to find the oldest female in the family, her daughters and their offspring in descending date order.
2. to find the next oldest sister to that female, her daughters/sons, and their offspring etc for all sisters
3. to find oldest cousin to female x, her daughters, and their offspring etc.

For example, Lobelia is the oldest family member in the example below; she was born in 1980, her mother is Lucia. Her two youngest are sons born this year and in 2018. Four daughters are alive and listed in descending date order, with Latha (her daughter born in 2000) with two of her own offspring, 'LTH20' and 'Lox', again listed in descending date order. Lily is Lobelia's oldest sister (same mother Lucia) with her kids listed and so on.

Should I be using a nested subquery approach for this or a union query with subquery (which I've also tried, but failed to land the correct sort at the very end whereby sisters are together before cousins)?

I feel that perhaps I should be constructing a vba sql nested subquery statement, but I can't see how to construct the correct sql to find the next oldest, having dealt with the oldest etc.

I can send many constructs for my different attempts as attachments.

Thx a million for your time!

 

[Uncle Gizmo]

There was a thread a while back concerning birds and I believe there were similar requirements. @MajP provided many useful answers and pointers to the user. If you could find that thread, I think you will have a good start!

 

[Uncle Gizmo]

Found it:-

Calculate Inbreeding​

Question - calculate inbreeding

Hi. I am building a database for bird breeding. Does anyone know of any code / Function to calculate inbreeding and AVK. Cheers

www.access-programmers.co.uk

 

[MajP]

That db has most of what you need, but you will need to add some modifications for some of the sibling data. That does full pedigrees but was not the focus. If you provide a full dataset that will make things much easier to help. Pretty sure you still have to rely on recursion unless you know the max level of offspring

 

[Pia]

Thank you so much for the quick replies Uncle Gizmo and MajP. I shall take a look at the thread you've suggested Uncle Gizmo, and many thx MajP. I'm so grateful and shall send one complete family, rather than the entire population, given that this will be the focus and has all of the characteristics of the entire population. You are right, my case is different in that I'm not looking at 'inbreeding' (2 parents) as such, but rather a female-led cohort - so a series of mothers and their offspring (and Grandmothers in the recursion and in order to pull out sisters - same mother - and cousins - same grandmother - all from a single table. An sql nested loop maybe? I did build a hierarchy, but I still can't order the data in the manner required.

In the table attached (not the snapshot below, but the tble in the attached accdb), for one family (the AAs), the 'casename' is the assigned code for the individual, 'MOT' is the mother - so the 'MOT' code can also be found in the 'Casename' field. The crux of my problem is the final ordering as earlier described in my first post and as illustrated again below for this particular family (partially appears in the snapshot - whereby eldest female in family is selected, then eldest sister .. etc for all sisters, then their offspring in descending date order, then cousins, their offspring etc.

Hope I've given you enough insight and data... THANK YOU!

 

[MajP]

This data is either incomplete or I do not understand how you have a relationship constructed.

the 'casename' is the assigned code for the individual, 'MOT' is the mother - so the 'MOT' code can also be found in the 'Casename' field.

This is what I expect, but that is not what I see. CaseName if the primary key for an individual animal. Its parent ID is MOT.
So looking at the data
If I pick AVA19. AVA19's mother is AVA. AVA's mother is AME, but there is no AME in the table. All known parents should be in the table.
If I pick AST17, parent is AST, AST mother is ALI, but no ALI in the table.

Query1 Query1

CASENAME	MOT
250	ALY
405	AME
431	ABI
457	WAR
458	ALI
483	AMY
505	ALI
578	ALT
629	AGA
768	ANG
769	AGA
971	ALX
AAN	AST
AAN20	AAN
ABR	AUD
ABR12	ABR
ABR17	ABR
AKI	ANH
ALF	ANN
ALF20	ALF
ALI12	ALI
ALT	AGA
ALX15	ALX
AME12	AME
AMO	ANG
AND	ALT
AND20	AND
ANG	AMY
ANG15	ANG
ANG20F	ANG
ANG20M	ANG
ANH	AME
ANN	AME
ANN12	ANN
ANN17	ANN
ARD	ALI
ARD13	ARD
ARS	ART
ART	ALI
ART17	ART
ART21	ART
AST	ALI
AST17	AST
AUB	AMB
AVA	AME
AVA19	AVA

 

[MajP]

Something like this.

Since you know the max levels and it is not many (maybe three). You could probably do this in a series or queries and then use sorting and grouping to do it. How to get that format would make my head hurt. I can build that exact format use should using a temp table, and it would probably take me a few minutes. So personally I would go the code route.

 

[Pia]

MajP, forgive me, I forgot to extract qualifying males (sex =M) in the first sweep and only living family members that haven't joined another family. I will send you another dataset shortly for a different family (always made up of adult females and their offspring, adult males being independent of family).

Yes, you've grasped the nettle, but it's the ordering that's challenging. You are also right in that there are rarely four levels in the path, so I believe that there's a chance that the outcome can be dealt with quickly and speedily with queries. At times I've felt so close to nailing it, but then failed to order correctly, extracting eldest - sisters then cousins - their kids always in descending birth year order, youngest child first etc.

Let me improve dataset and send you my hierarchy...

Thank you!

 

[Pia]

MajP, thank you for your time and patience first and foremost!

Please find attached accdb containing a table and a query. I messed up my tree path query series, so only have a snapshot of partial dataset of a different family that I achieved with a union query and a subquery for each level. It shows the gist of what I achieved, by using an intermediate update query to create a new 'MotherID' field that linked back to the PK 'ID' field in same table (Casename and MOT being text fields).

I'm recreating now the same for LB family and will send. Thank you.

 

[MajP]

Please do me a favor and send the complete dataset. I cannot tell you how many threads I have worked where the solution works for the small test data, but there is more to it then that. There are cases that the test data does not account. Just tell me explicitly the rules. I would rather do my on query on the full dataset to build the test data set. Based on what I am hearing now. My base query would include

All individuals group by family (Fam = XXX) where Sex = F and death_verified = false and count of family = 1

(I forgot to extract qualifying males (sex =M) in the first sweep and only living family members that haven't joined another family.)

In your test report I see some males, so why are those shown based on what you just said?

but it's the ordering that's challenging.

You did not answer, so is the order that I presented what you are looking for?

Getting the correct data in a query is not that challenging if you have a known small amount of levels. The problem is getting a nice format. Displaying hierarchical data can be challenging.

Which you likely would have to do is build a query that can handle the max amount of levels. Then for the family report you build several different reports formatted for the max level of family. You would have a level 2, level 3, level 4 .... report. Then you can select a family, determine max levels (this can be done in a function) then open the correct report.
The reason you would likely do it this way is because you would likely need a column for each level. (ex. Grandparent, Parent, Child, Grandchild) If the family only goes to grand children you would not want a report with a great grand children column taking up space.

However the other trick is what you did. You have a single column for individuals and add the appropriate indentations for each level. This simplifies things because then you can build a single report.

Building a nice formatted query to do this is not technically hard but can be very tedious in order to get nicely formatted data for all possible levels. That is why for me I will do this in a temp table. It is less time consuming to write a procedure that populates a temp table in this format.

Also you have two new fields that are not explained. What is a GMA and GMAFin?
I am confused with your data and the missing information, again I would expect every entry that is in the MOT field is also in the Case Name. So if I start my family I start somewhere. I buy dog Spot from another user. Then Spot is the first dog and gets a casename. I do not have mother information because she is not from my breeding program. I would expect the MOT to be null. In your case you have MOT without a corresponding individual. Only thing I can assume is that you know the mothers name from the previous breeder, but do not have (nor care) about any other information because not one of your breeders. If this is the case I would not do this. You cannot enforce referential integrity. And yes you can enforce referential integrity in a self referencing table. I would likely have a MOT field which only references a value in the CaseName field (referential integrity). For these cases where I only know the mothers name, but not part of my breeding program I would have another field "ExternalMOT" which only hold a name (if that is all you know).

To do this right a MOT value needs to be a value from CaseNum or NULL. You could also reference a dummy record, but I prefer NULL. Is CaseNum your PK? If not it needs to be. If ID is your PK then MOT needs to be a numeric foreign key and hold an ID number referencing another ID. CaseNum should be fine (if it is always unique, changes rarely, and you know it at the time of data entry). I would seriously consider going with the autonumber if your names are more complex. They are good now (single alphanumeric words). But if you are going to have special characters besides numbers and letters then that can be problematic.

With the query you showed, have you tried making a grouped 3 level report. The report may be able to sort within the group even though the query is not.

 

[arnelgp]

Please do me a favor and send the complete dataset. I cannot tell you how many threads I have worked where the solution works for the small test data,

Wow!
Be warned; Pride cometh before destruction!

 

[Pia]

And I can achieve this, which shows max of 3 levels for this family, but I'm stumped as far as how to get the final ordering with a cool tree look like your stunning example..

Please do me a favor and send the complete dataset. I cannot tell you how many threads I have worked where the solution works for the small test data, but there is more to it then that. There are cases that the test data does not account. Just tell me explicitly the rules. I would rather do my on query on the full dataset to build the test data set. Based on what I am hearing now. My base query would include

All individuals group by family (Fam = XXX) where Sex = F and death_verified = false and count of family = 1


In your test report I see some males, so why are those shown based on what you just said?


You did not answer, so is the order that I presented what you are looking for?

Getting the correct data in a query is not that challenging if you have a known small amount of levels. The problem is getting a nice format. Displaying hierarchical data can be challenging.

Which you likely would have to do is build a query that can handle the max amount of levels. Then for the family report you build several different reports formatted for the max level of family. You would have a level 2, level 3, level 4 .... report. Then you can select a family, determine max levels (this can be done in a function) then open the correct report.
The reason you would likely do it this way is because you would likely need a column for each level. (ex. Grandparent, Parent, Child, Grandchild) If the family only goes to grand children you would not want a report with a great grand children column taking up space.

However the other trick is what you did. You have a single column for individuals and add the appropriate indentations for each level. This simplifies things because then you can build a single report.

Building a nice formatted query to do this is not technically hard but can be very tedious in order to get nicely formatted data for all possible levels. That is why for me I will do this in a temp table. It is less time consuming to write a procedure that populates a temp table in this format.

Also you have two new fields that are not explained. What is a GMA and GMAFin?
I am confused with your data and the missing information, again I would expect every entry that is in the MOT field is also in the Case Name. So if I start my family I start somewhere. I buy dog Spot from another user. Then Spot is the first dog and gets a casename. I do not have mother information because she is not from my breeding program. I would expect the MOT to be null. In your case you have MOT without a corresponding individual. Only thing I can assume is that you know the mothers name from the previous breeder, but do not have (nor care) about any other information because not one of your breeders. If this is the case I would not do this. You cannot enforce referential integrity. And yes you can enforce referential integrity in a self referencing table. I would likely have a MOT field which only references a value in the CaseName field (referential integrity). For these cases where I only know the mothers name, but not part of my breeding program I would have another field "ExternalMOT" which only hold a name (if that is all you know).

To do this right a MOT value needs to be a value from CaseNum or NULL. You could also reference a dummy record, but I prefer NULL. Is CaseNum your PK? If not it needs to be. If ID is your PK then MOT needs to be a numeric foreign key and hold an ID number referencing another ID. CaseNum should be fine (if it is always unique, changes rarely, and you know it at the time of data entry). I would seriously consider going with the autonumber if your names are more complex. They are good now (single alphanumeric words). But if you are going to have special characters besides numbers and letters then that can be problematic.

With the query you showed, have you tried making a grouped 3 level report. The report may be able to sort within the group even though the query is not.

MajP thank you.

Yes, I would love a tree like yours, but the ordering is incorrect - my fault.

Yes, you are right, better that you have the dataset and I apologize for the obfuscation and omissions. May I send it just to you in confidence... it's a legacy table, is not for general consumption, and the intellectual property isn't mine to give away? If okay, how do I send it to you by email?

Text doc attached explaining the loops required if handling the entire dataset.

Males appear because young males remain with the female unit until they become independent and are no longer with the family group. Once adult, they roam and mate with different females in different groups and play no part in raising the offspring. We never know the father of the child.

Ouput can't just be a report because I need to use the ordering in different contexts - printable data sheets (for which an Access report format would be fine), but also as subdatasheet in a form, and as export to Excel file. Reports, as far as I'm aware, can't be exported to an Excel file and can't be used for data entry in a subdatasheet?

If you really are happy to pursue this then, apart from being hugely grateful, I need to send you male independence table and you have the family transfers table already. Any female that has split from her original family appears in tblFamilyTransfers with her new family eg Lisa belongs to LD family, and was originally a member of the LB family. You will need these tables as well now that you are starting from scratch. Again, please may I send the accdb just to your email address? For the benefit of other users, perhaps we can ultimately post a subset of the data with the solution?

 

[Pia]

View attachment 94569

Is this correct? This is done using your query and sorting and grouping. This would be your 3 level report.

Many thx MajP, I can achieve this but as my latest post explains I can't just use the report output. The ordering needs to be according to the steps sent as attached PDF, where oldest female in family appears first, with her daughters and kids appearing in descending birth order, not ascending as in your example. Then next oldest sister to that female with her kids and their kids listed in descending birth order (youngest first). Then when sisters to original female are looped through in ascending age order, move to cousins and so on... it's tricky and I can't use report sorting and grouping format for reasons explained.

How do I send you the entire dataset where it won't be public?

 

[MajP]

Still trying to wrap my head around what is desired.

Lobelia is the oldest female in this family that is entered in the DB. The mother is LUC, but all listed mothers are not records in the database as far as I can tell. So all records in the first column are the top level in the database.
Lobelia is the oldest female of the family. Her descendants are all listed from youngest to oldest.
Is that part correct?
Lily is a sister of Lobelia they have LUC as a mother. You would next like Lily and here descendants. In the same order as prior.
So the next oldest female is Leah (whose mother is also LEA?). So you want Leah and descendants, followed by Letter (mother is LEA) and descendants. and then Lola, and Lupita (hose parrents are LEA but on next page).
Is that correct.
If this is correct this may answer why you have hardwired GMA/GMA FIN. You want to group by the TOP level family. That should probably be done dynamically and not hardwired.

 

[MajP]

So here is my second guess. Lobelia is the oldest breeder. With youngest children and grand children listed young to old.
Her sister is lily
Leah is the next oldest breeder and Leiter and Lola are sisters

The treeview is not a report but a control, so it is nice for display and can provide it to you.

As you said if you need this format for export, this is far easier in my opinion to write it to a table. It gets really painful trying to maintain a set of queries and reports to do this.

You will need these tables as well now that you are starting from scratch. Again, please may I send the accdb just to your email address? For the benefit of other users, perhaps we can ultimately post a subset of the data with the solution?

You can PM me direct and if possible you can use a google shared drive or onedrive.

Based on your PDF I believe that format is steps 1 and 2. That shows oldest Breeder and Sisters and their offspring.
The next step is is brothers. Formatting wise how would you like to show that?

Lets assume you were going to export this data to Excel in the most useable format. Can you do a mockup in Excel what it would look like for a case that has Brothers and cousins? I think until I get my head fully wrapped around this writing the results in Excel may be the simplest. Then you have the flexibility to format and put data wherever you want. I think the above could be done with subreports for Sisters and Offspring, Brothers, Cousins but that data will unlikely be exportable..
If I understand GMA is grandMa used for finding cousins. This should likely be calculated and not hardwired. Or at least run code to populate this field. There is not way to maintain referential integrity and if that field gets out of synch your data will be wrong.

 

[Pia]

Still trying to wrap my head around what is desired.
View attachment 94571
Lobelia is the oldest female in this family that is entered in the DB. The mother is LUC, but all listed mothers are not records in the database as far as I can tell. So all records in the first column are the top level in the database.
Lobelia is the oldest female of the family. Her descendants are all listed from youngest to oldest.
Is that part correct?

Yes correct - as is your obs that all records in the first column are the top level.

In answer to yr earlier Q, for better or worse, GMA field is known grandmothers, but sketchy n gappy because this was pre-47 yrs ago. Where there is no GMA, I created a dummy field 'GMAFin' (added 'G' prefix to the MOT to fill in null values), so that I could pull out cousins.

Lily is a sister of Lobelia they have LUC as a mother. You would next like Lily and here descendants. In the same order as prior.

Spot on yes.

So the next oldest female is Leah (whose mother is also LEA?).

Leah next, yes, mother is Leana (casename LEA) so, yes, correct again

So you want Leah and descendants, followed by Letter (mother is LEA) and descendants. and then Lola, and Lupita (hose parrents are LEA but on next page).
Is that correct.

Yes, correct, followed by Leiter (mother is Leana, casename LEA), correct again. Lola follows, yes, then the male still with the family (brother to cousins of Lobelia) called Lightning - he's not yet independent, being a young male and still with the female family unit (perhaps you do then need the full dataset, I don't know?) then comes Lupita, yes (mother is Leana, casename LEA) correct.

If this is correct this may answer why you have hardwired GMA/GMA FIN. You want to group by the TOP level family. That should probably be done dynamically and not hardwired.

You likely know better than me at this point - my brain is so frazzled by trying to nail this! Problem is that, as you surmised earlier, if the recordset isn't complete (for records way back), then hardwiring was perhaps the only way I felt was 'out' .. but if there's a dynamic way that would be fantastic?

I believe that you fully understand the challenges of this imperfect old dataset now - bravo and thank you again!

 

[Pia]

View attachment 94573
So here is my second guess. Lobelia is the oldest breeder. With youngest children and grand children listed young to old.
Her sister is lily
Leah is the next oldest breeder and Leiter and Lola are sisters

The treeview is not a report but a control, so it is nice for display and can provide it to you.

As you said if you need this format for export, this is far easier in my opinion to write it to a table. It gets really painful trying to maintain a set of queries and reports to do this.


You can PM me direct and if possible you can use a google shared drive or onedrive.

View attachment 94573
So here is my second guess. Lobelia is the oldest breeder. With youngest children and grand children listed young to old.
Her sister is lily

Perfect, yes!

Leah is the next oldest breeder and Leiter and Lola are sisters

Correct again!

The treeview is not a report but a control, so it is nice for display and can provide it to you.

Amazing thank you!

As you said if you need this format for export, this is far easier in my opinion to write it to a table. It gets really painful trying to maintain a set of queries and reports to do this.

I agree, if only I could get there..

You can PM me direct and if possible you can use a google shared drive or onedrive.

How do I do that? Is your email address public somewhere? I can WeTransfer to your email address, if that works for you?

Thank you!

 

[MajP]

How do I do that? Is your email address public somewhere? I can WeTransfer to your email address, if that works for you

In the upper right corner is a letter symbol. I sent you a private message. There should be a little red 1. Click and open. By you can click anywhere on my Avatar. It should bring up a way to start a conversation or go to the letter symbol

 

[MajP]

You likely know better than me at this point - my brain is so frazzled by trying to nail this! Problem is that, as you surmised earlier, if the recordset isn't complete (for records way back), then hardwiring was perhaps the only way I felt was 'out' .. but if there's a dynamic way that would be fantastic?

I get storing the GMA now. It can be done dynamically. You could do it in a query, but if not to many records you can use these functions.

Public Function GetGMA(CaseName As String) As String
   Dim MOT As String
   MOT = GetMOT(CaseName)
   GetGMA = Nz(DLookup("MOT", "qryPopData", "CaseName = '" & MOT & "'"), "No GMA")
End Function
Public Function GetMOT(CaseName As String) As String
  GetMOT = DLookup("MOT", "qryPOPdata", "CaseName = '" & CaseName & "'")
End Function

So you could use these functions to update the entire database and/or update a records GMA when you add it. So to test my functions I compared to what you had. As far as I can tell my code is correct and you have bad GMA data that needs updating.
Query1 Query1

MOT	NATALNAME	CASENAME	NAME	GMA	MyGma
LAN	LAN90	LATA	LATA	LET	No GMA
LAN	LAN00	LTP	LAPTOP	LET	No GMA
LATA	LATA04	LPO	LATAPOA	LAN	LAN
LATA	LATA11	982	LESHAN	LAN	LAN
LATA	LATA15	LATA15	LORENZO	LAN	LAN
LATA	LATA20	LATA20		LAN	LAN
LBD	LBD14	LBD14	LIMAU	LEH	LEH
LBD	LBD20	LBD20		LEH	LEH
LCY	LCY15	LCY15	LOUIS XIV	LOI	LOI
LCY	LCY20	LCY20		LOI	LOI
LEA	LEA83	LEH	LEAH		No GMA
LEA	LEA90	497	LAL		No GMA
LEA	LEA03	LOA	LOLA		No GMA
LEA	LEA10	961	LIGHTNING		No GMA
LEA	LEA94	LIT	LEITER		No GMA
LEA	LEA14	LEA14	LUPITA		No GMA
LEH	LEH15	LEH15	LAURETTA	LEA	LEA
LEH	LEH20	LEH20		LEA	LEA
LEH	LEH12	LEH12	LUYI	LEA	LEA
LEH	LEH04	846	LUXOR	LEA	LEA
LEH	LEH00	LBD	LAMBDA	LEA	LEA
LEH	LEH96	616	LYLE	LEA	LEA
LEN	LEN08	922	LAWALAWA	LOI	LOI
LEN	LEN14	LEN14	LIKHETSO	LOI	LOI
LEN	LEN20	LEN20		LOI	LOI
LET	LET85	412	LLOYD		No GMA
LIT	LIT15	LIT15	LUCRETIA	LEA	LEA
LIT	LIT20	LIT20		LEA	LEA
LIT	LIT11	983	LUKA	LEA	LEA
LLY	LLY08	LIC	LICORICE	LUC	LUC
LLY	LLY15	LLY15	LEOPOLD	LUC	LUC
LLY	LLY20	LLY20		LUC	LUC
LOA	LOA20	LOA20		LEA	LEA
LOB	LOB00	LTH	LATHA	LUC	LUC
LOB	LOB08	LBG	LILYBUG	LUC	LUC
LOB	LOB12	LOB12	LUCIADIANA	LUC	LUC
LOB	LOB15	LOB15	LAKSHMIBAI	LUC	LUC
LOB	LOB18	LOB18		LUC	LUC
LOB	LOB21	LOB21		LUC	LUC
LOB	LOB93	545	LONGIDO	LUC	LUC
LOI	LOI10	LAY	LAYLA	LEA	No GMA
LOI	LOI96	LEN	LENNOX	LEA	No GMA
LOI	LOI02	LHM	LAHM	LEA	No GMA
LOI	LOI99	LCY	LACEY	LEA	No GMA
LOR	LOR01	LTC	LANTICA	LET	No GMA
LOR	LOR96	615	LLCOOL	LET	No GMA
LPO	LPO18	LPO18		LATA	LATA
LTC	LTC15	LTC15	LUDWIG	LOR	LOR
LTC	LTC20	LTC20		LOR	LOR

LATA and LTP (case name) have a mother as LAN. However there is not LAN in the database. In fact LET is only a mother to 412.
The same issue lower down. You show LEA as the grandmother to Lay, LEN, LHM, and LCY. They have a mother of LOI. But there is not LOI.
So the question is, is the GMA data bad or did the records for LAN and LOI get deleted.

This is why if you can enforce referential integrity it is a big deal with any self referencing table. Since you are populating your results recursively, if you lose one record you end of losing visibility on the whole branch. If LOI is lost then you query will not print the children of LOI.

 

[Pia]

I get storing the GMA now. It can be done dynamically. You could do it in a query, but if not to many records you can use these functions.

Public Function GetGMA(CaseName As String) As String
   Dim MOT As String
   MOT = GetMOT(CaseName)
   GetGMA = Nz(DLookup("MOT", "qryPopData", "CaseName = '" & MOT & "'"), "No GMA")
End Function
Public Function GetMOT(CaseName As String) As String
  GetMOT = DLookup("MOT", "qryPOPdata", "CaseName = '" & CaseName & "'")
End Function

So you could use these functions to update the entire database and/or update a records GMA when you add it. So to test my functions I compared to what you had. As far as I can tell my code is correct and you have bad GMA data that needs updating.

Thank you v much MajP! I shall try these functions.

Re. 'GMA', yes, as mentioned yesterday, GMA is incomplete because the data go so far back that when monitoring of pop started, GMAs weren't known.

'GMAFin' is the dummy GMA I assigned which uses the mother's name with a 'G' prefix, rather than your generic 'no GMA', because that then identifies the grandmother of that casename and you can sort relationships with it... I think, but correct me if I'm wrong?

Query1 Query1

MOT	NATALNAME	CASENAME	NAME	GMA	MyGma
LAN	LAN90	LATA	LATA	LET	No GMA
LAN	LAN00	LTP	LAPTOP	LET	No GMA
LATA	LATA04	LPO	LATAPOA	LAN	LAN
LATA	LATA11	982	LESHAN	LAN	LAN
LATA	LATA15	LATA15	LORENZO	LAN	LAN
LATA	LATA20	LATA20		LAN	LAN
LBD	LBD14	LBD14	LIMAU	LEH	LEH
LBD	LBD20	LBD20		LEH	LEH
LCY	LCY15	LCY15	LOUIS XIV	LOI	LOI
LCY	LCY20	LCY20		LOI	LOI
LEA	LEA83	LEH	LEAH		No GMA
LEA	LEA90	497	LAL		No GMA
LEA	LEA03	LOA	LOLA		No GMA
LEA	LEA10	961	LIGHTNING		No GMA
LEA	LEA94	LIT	LEITER		No GMA
LEA	LEA14	LEA14	LUPITA		No GMA
LEH	LEH15	LEH15	LAURETTA	LEA	LEA
LEH	LEH20	LEH20		LEA	LEA
LEH	LEH12	LEH12	LUYI	LEA	LEA
LEH	LEH04	846	LUXOR	LEA	LEA
LEH	LEH00	LBD	LAMBDA	LEA	LEA
LEH	LEH96	616	LYLE	LEA	LEA
LEN	LEN08	922	LAWALAWA	LOI	LOI
LEN	LEN14	LEN14	LIKHETSO	LOI	LOI
LEN	LEN20	LEN20		LOI	LOI
LET	LET85	412	LLOYD		No GMA
LIT	LIT15	LIT15	LUCRETIA	LEA	LEA
LIT	LIT20	LIT20		LEA	LEA
LIT	LIT11	983	LUKA	LEA	LEA
LLY	LLY08	LIC	LICORICE	LUC	LUC
LLY	LLY15	LLY15	LEOPOLD	LUC	LUC
LLY	LLY20	LLY20		LUC	LUC
LOA	LOA20	LOA20		LEA	LEA
LOB	LOB00	LTH	LATHA	LUC	LUC
LOB	LOB08	LBG	LILYBUG	LUC	LUC
LOB	LOB12	LOB12	LUCIADIANA	LUC	LUC
LOB	LOB15	LOB15	LAKSHMIBAI	LUC	LUC
LOB	LOB18	LOB18		LUC	LUC
LOB	LOB21	LOB21		LUC	LUC
LOB	LOB93	545	LONGIDO	LUC	LUC
LOI	LOI10	LAY	LAYLA	LEA	No GMA
LOI	LOI96	LEN	LENNOX	LEA	No GMA
LOI	LOI02	LHM	LAHM	LEA	No GMA
LOI	LOI99	LCY	LACEY	LEA	No GMA
LOR	LOR01	LTC	LANTICA	LET	No GMA
LOR	LOR96	615	LLCOOL	LET	No GMA
LPO	LPO18	LPO18		LATA	LATA
LTC	LTC15	LTC15	LUDWIG	LOR	LOR
LTC	LTC20	LTC20		LOR	LOR

LATA and LTP (case name) have a mother as LAN. However there is not LAN in the database.

LAN (casename is LET69) is in the full population table, but because I'm only concerned with living members of the family at any point in time, she doesn't appear in the output except as a GMA.

In fact LET is only a mother to 412.
The same issue lower down. You show LEA as the grandmother to Lay, LEN, LHM, and LCY. They have a mother of LOI. But there is not LOI.
So the question is, is the GMA data bad or did the records for LAN and LOI get deleted.

LOI is also in the full pop table, but because she's dead (DCAS= X, died in 2014, birth verified) she is included in the output as a granny in order to extract cousins etc, but she is no longer alive, so does not appear as a MOT in the output.

The LB data you have shows MOT for living family members only and the GMA is there to show grandmother if known, but she can be dead.

This is why if you can enforce referential integrity it is a big deal with any self referencing table. Since you are populating your results recursively, if you lose one record you end of losing visibility on the whole branch. If LOI is lost then you query will not print the children of LOI.

LOI is not lost, and appears in family output as a GMA to one or more casenames who are alive. Ouput only includes living members of the family at any point in time (DCAS=L), as indicated in the PDF I sent.

Is this a problem? Should I leave living and dead in the initial querying/tree and then find a way to deal only with the living at the very end as output?

Surely, just dealing with the living MOT (=casename) at the outset will give us the same result. LOI and LAN are dead, so don't appear as MOTs but appear as GMAs.

Would be very cool to have a tree for both - expanded includes everyone forever with an asterisk if they're dead, 1st collapse shows only the living, complete collapse shows only the families listed.. sounds v challenging to me with the required sorting?

Thank you for your amazing, continued patience with this!