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

[MajP]

After some thought, I think I have a relatively easy solution to get this complicated sort order, allow you to visually interpret the results, export the data, and make a report.

It is basically the same code as filling the tree view, but I would write the results to a table as well. It would store the entire sort order for every record in the database and some additional fields for visibility.

FAM	Mother	Category	Relation	Relation To	Family member Case Name	Sort Order
Family 1		Oldest Female				1​
		Oldest Female Child	Child	Oldest Female CaseName		2​
		Oldest Female GrandChild	Grand Child	Oldest Female CaseName		3​
		Oldest Female Great Grand Child	Grand Child	Oldest Female Case Name		4​
		Sister	Sister	Oldest Female CaseName		5​
		Sisters Child	Child	Sisters Case name
		Sisters Grand Child	Grand Child	Sisters Case Name
		Brother	Brother	Oldest Female CaseName
		Cousin	Cousin	Oldest Female CaseName
		Cousin Child	Child	Cousin Child CaseName
Family 2

That table could link to any data you want to add by CaseNum. With the categories you can then know how things are related. Was not sure how to show offspring of the oldest, sisters and their offspring, and brothers all in the same table without some crazy formatting requiring indenting and offsetting columns. You would order every record deceased or not. If you want to filter out the deceased then just do that in a query. Does not matter because the sort order is the same. If 3 is deceased and filtered out then (1,2,4,5) still sorts properly. Same thing if you include or filter certain males.

So for any record in this table you will be able to look at it and see it is an Oldest Female for a give mother, It is that females offspring, it is a sister to the female, it is a brother, ....

 

[Pia]

After some thought, I think I have a relatively easy solution to get this complicated sort order, allow you to visually interpret the results, export the data, and make a report.

Very determined! Thank you..

It is basically the same code as filling the tree view, but I would write the results to a table as well. It would store the entire sort order for every record in the database and some additional fields for visibility.

Yes, I believe that you could be on to the solution but there are some buts to follow... It may be that I need a query/queries to create 'generational levels' from the primary table? Re yr table below, remember kids order is youngest first, not oldest, and also includes males still with family (there will be many mothers with young male offspring because they are the living members of the family today) - and of course males can be younger than females or older. So, yes, (if we focus on the living members of chosen family) starting with oldest living female in family, then her youngest child first (most recent birth, male or female), their children too below them in descending date order (youngest first) etc, then next oldest sister, her kids, their kids youngest first.. exhaust all sisters, then move to cousins, same again, brothers of oldest female if any are still with the family ie. not yet independent adults when they wander off as earlier described. The PDF gives the ordering and fields' values to use as criteria.

Yes, looks like a grand idea if we can extract from main pop table to create your table dynamically? May be a problem when it comes to sort order field in that we don't know how many there will be in each category?

FAM	Mother	Category	Relation	Relation To	Family member Case Name	Sort Order
Family 1		Oldest Female				1​
		Oldest Female Child	Child	Oldest Female CaseName		2​
		Oldest Female GrandChild	Grand Child	Oldest Female CaseName		3​
		Oldest Female Great Grand Child	Grand Child	Oldest Female Case Name		4​
		Sister	Sister	Oldest Female CaseName		5​
		Sisters Child	Child	Sisters Case name
		Sisters Grand Child	Grand Child	Sisters Case Name
		Brother	Brother	Oldest Female CaseName
		Cousin	Cousin	Oldest Female CaseName
		Cousin Child	Child	Cousin Child CaseName
Family 2

That table could link to any data you want to add by CaseNum.

Think this is what I meant by creating your table dynamically from the main pop table whenever required, so it would be a temp table?

With the categories you can then know how things are related. Was not sure how to show offspring of the oldest, sisters and their offspring, and brothers all in the same table without some crazy formatting requiring indenting and offsetting columns.

Maybe for the purposes of output to Excel, data entry, the indentation isn't important, it's just the order in which they appear that is critical. For a report/treeview, indentation can be achieved there for clarity?

You would order every record deceased or not. If you want to filter out the deceased then just do that in a query. Does not matter because the sort order is the same. If 3 is deceased and filtered out then (1,2,4,5) still sorts properly.

Still see a problem with numbers of offspring within your sort order levels and knowing how to sort those because they will be variable?

In summary, we need to keep the main pop table as it is because there's a lot more important stuff in it. I feel that we need to then use queries or code to pull out the data in to a new temp table along the lines that you are nailing (because the data are dynamic) for the purposes of exporting, using for data entry in a subform where users tick off presence or absence of a living individual in the family following a census and back behind the computer, plus reporting/treeview ability (in an ideal world). This ordering has been done manually for decades and is the favoured, understood approach, hence my attempts to keep the main pop table and extract the data automatically in the order required.

I tried a loop code using sql statements and appending to a temp table, but then I realized that I needed nested sql statements and i just couldn't see the wood for the trees? What are your thoughts re a loop in code writing to a temp table like yours? I've coded loops before but this one has floored me.

As ever, thank you MajP for gaining ground on this ... hope your day at work was good and you see the full Harvest moon tonight for inspiration!

 

[MajP]

I feel that we need to then use queries or code to pull out the data in to a new temp table along the lines that you are nailing (because the data are dynamic) for the purposes of exporting, using for data entry in a subform where users tick off presence or absence of a living individual in the family following a census and back behind the computer, plus reporting/treeview ability (in an ideal world). This ordering has been done manually for decades and is the favoured, understood approach, hence my attempts to keep the main pop table and extract the data automatically in the order required.

Yes, if not clear, that will all be to a temp table.

May be a problem when it comes to sort order field in that we don't know how many there will be in each category?

That will not be an issue since it you will create the whole table on the fly. To do this I would have a table with two fields.
Level
As I figure out what level I would save the title as well as the level code.
Level_Description
1 Child
2 Grand Child
3 Great Grand Child
4 Great Great Grand Child
.....
X Some max amount of levels
Can also add the following
-1 Parent
-2 Grand Parent
-3 Great Grand Parent
....

Through code you can then show generations starting with any CaseName and working up or down.

It sounds like what I thought were issues in the data was due to looking at a partial data set. It sounds like the table structure is better than I thought.

 

[Pia]

Yes, if not clear, that will all be to a temp table.

Great

That will not be an issue since it you will create the whole table on the fly. To do this I would have a table with two fields.
Level
As I figure out what level I would save the title as well as the level code.
Level_Description
1 Child
2 Grand Child
3 Great Grand Child
4 Great Great Grand Child
.....
X Some max amount of levels
Can also add the following
-1 Parent
-2 Grand Parent
-3 Great Grand Parent
....

Through code you can then show generations starting with any CaseName and working up or down.

Hmmmm, the code... which - from the amazing threads you sent and that I've tried to understand - you are evidently very brilliant at! Loved the tree views, with ability to collapse, expand etc. Very elegant.

It sounds like what I thought were issues in the data was due to looking at a partial data set. It sounds like the table structure is better than I thought.

Ancient and all in a single table, but I believe robust enough.

Thank you so much and have a great day when it dawns with you!

 

[MajP]

I played with this and put a couple different trees together. I can build different treeview by just changing the query. The Treeviews do not solve you immediate question, but the work to get them has prepared me to give you a solution and probably a lot of features you would like for data visualization. I built a lot of different queries that will make the final solution easy. I truly think the solution is to export this to a temptable, and I basically have all the pieces I need, just did not complete it today. However, I think the tree view will be useful for you.
Here are the two views.
One is purely all animals from the very oldest to the youngest by relation. Blue is M, Pink is F and Angels are deceased, Heartbeats are alive

This goes from the oldest (with offspring) down.

If you see there are two buttons to change the View. By family gives this view. So they are sorted by Family alphabetically and be age in each family. I will put in the drive. The tree view control is a little temperamental so be patient as it loads it will take a while. The Treeview is such a valuable tool for visually this kind of data, that is my biggest wish for Access improvement. This one works but it is iffy. You may experience some crashes.

I will post in the drive. Take a look at some of the queries. I just need to write the procedure to read them and export to the temp table.

 

[Pia]

I played with this and put a couple different trees together. I can build different treeview by just changing the query. The Treeviews do not solve you immediate question, but the work to get them has prepared me to give you a solution and probably a lot of features you would like for data visualization. I built a lot of different queries that will make the final solution easy. I truly think the solution is to export this to a temptable, and I basically have all the pieces I need, just did not complete it today. However, I think the tree view will be useful for you.
Here are the two views.
One is purely all animals from the very oldest to the youngest by relation. Blue is M, Pink is F and Angels are deceased, Heartbeats are alive
View attachment 94668

This goes from the oldest (with offspring) down.

If you see there are two buttons to change the View. By family gives this view. So they are sorted by Family alphabetically and be age in each family. I will put in the drive. The tree view control is a little temperamental so be patient as it loads it will take a while. The Treeview is such a valuable tool for visually this kind of data, that is my biggest wish for Access improvement. This one works but it is iffy. You may experience some crashes.

View attachment 94669

I will post in the drive. Take a look at some of the queries. I just need to write the procedure to read them and export to the temp table.

Amazing this tree control.. did you create it? How very clever you are! Why isn't there one for Access?

V excited to take a look at the queries too - thank you again!

 

[Pia]

You are indeed a wizard - thank you so much! i have just scanned all queries and the final tree and all looks very good. The tree is phenomenal for visualization, you are right - how very exciting. Only one query that depends upon another absent: "qryPopThree" plus the qryPopTree report is looking for qrylevelbfwd.

Yes, it takes time to open the tree, but that's a small price to pay to be able to see the entire pop. What we could perhaps do is have buttons for different trees at the outset: 'entire pop', 'living' etc. so that the tree desired is only loaded after selection? Then, if you select 'living', you can choose by family , ie. if you only want to look at the LB's, you can hit a button for 'Family' and then select LB? The ordering for LB would appear as originally discussed, but in tree view with the ability to export to Excel etc. (output by another button that only writes that family to a temp table). This should reduce the time entailed to drill down?

LOVE the whole tree though!

I'm now intriqued to see how you manage the final ordering that I'm after in my very first message.

You have been so generous of your time... why?

 

[MajP]

You have been so generous of your time... why?

There are a lot of people on this forum who spend hours helping. I explained this before in many threads why I think Access people are so helpful. Some of it has to do with the nature of most Access users. A lot of us are tinkerers. But there are plenty of forums out there where people enjoy sharing what they know.

Solved - Populating Combobox with a lot of data.

MajP, i'm trying. Sorry, this is a new concept for me. No big deal I just found it strange that you changed it after I put a big foot stomping comment. Const CharacterMin = 3 'MUST Be a module level variable as per Allen Browne Dim sProductStub As String How big is big? Lets say this list is...

www.access-programmers.co.uk

For me I learn more from helping and teaching.

I have been on these forums answering threads for years. I have learned from others questions. Teaching is the best way to learn. I enjoy taking on the challenging problems.

If you read those original threads on inbreeding you can see by the end I learned a lot. I have knowledge on doing a pretty complicated algorithm, but learned a lot about animal breeding too. A whole other world which I would never have known about.
In the other thread about Treeviews the OP asked for a lot a features I had never thought about in my code. This lead to a far better class module for working with treeviews. This was my response to the OP in that thread

Yeah it has been fun, I have learned as much as you. There are things I never would have thought about, and definitely something I can incorporate. I know a lot about Access and VBA mainly because I try to answer a lot of questions that I have to figure out how to do it.

So do not worry it is not completely altruistic, Not trying to be a Saint, I enjoy doing it like other people do crossword puzzles, solving or other challenging hobbies and get as much out of it as you do.

Amazing this tree control.. did you create it? How very clever you are! Why isn't there one for Access?

The TreeView is an ActiveX control like a third party control. It is not native to Access like a form, report, combobox etc. These work OK, but can be iffy and unstable. What I did write was a very robust class module to interface with Access data and make using a treeview much easier. It is a "Wrapper" around the Treeview. I can load and interface with a treeview very easily now without having to rewrite hundreds of lines of code.
In that article you will see that Access has IMO been neglected for years, but keeps on surviving with power users like us. It is just not as modern as other applications. A native Treeview would be great as you can see. This works but it is clunky. Not fast and not super stable.

By the way can you please tell @Uncle Gizmo that in fact I am clever. At #UncleG_MajP_Is_In_Fact_Clever#. He will be glad to know. Just kidding.

What we could perhaps do is have buttons for different trees at the outset: 'entire pop', 'living' etc. so that the tree desired is only loaded after selection?

That will be the plan if you find this useful. You can add many other views to the tree depending on what you want to see. You can add other features. Like pick one female from the list to show just those ancestors. A sort feature to find any animal in the tree.
You can pull out non-living easily. You could remove any dead animals if they have no living offspring. But that is not easy to do because you basically have to do the recursion to tell.
The problem is the case where a GMA is alive, but all daughters are deceased, and grand children are alive. In a tree there would be no way to connect the GMA to the children since there would be no middle nodes. I am trying to figure this out how to approach that to show a tree in this way.

One thing if you import these queries, code, temp tables, forms and reports into an existing DB. You need to look at the References in the vba window. You will need to add those references. The Treeview is hard because you have to browse to it. In the class module it explains where to find the references.

 

[MajP]

Indexing is a kind of an art form. If you index properly then things will speed up. If you over index you might do the opposite. But it can ensure data integrity also.

But yes CaseName is a unique value. One and only one record. This will maintain data integrity no duplicate CASENAMEs.

To ensure you can never mistakenly give two records the same Casename you can index and at the bottom make it unique. Now it is impossible to duplicate casename. A field that is required and unique should always be indexed IMO.
I indexed FAM and did not make it unique. This is for speed only not data integrity. Same with MOT.
IMO what is even more important is making composite indices when you need them. This can really help. Example I have an assignment table where I assign people to a task for a giving day. I can make an index where those three records as a group are unique. If I assign Jon to CleanUp on 1/1/2021 I can assign him another task today or cleanup on another day but cannot create more than 1 record for Jon to cleanup on 1/1/2021.

 

[Pia]

Indexing is a kind of an art form. If you index properly then things will speed up. If you over index you might do the opposite. But it can ensure data integrity also.

But yes CaseName is a unique value. One and only one record. This will maintain data integrity no duplicate CASENAMEs.
View attachment 94694

To ensure you can never mistakenly give two records the same Casename you can index and at the bottom make it unique. Now it is impossible to duplicate casename. A field that is required and unique should always be indexed IMO.
I indexed FAM and did not make it unique. This is for speed only not data integrity. Same with MOT.
IMO what is even more important is making composite indices when you need them. This can really help. Example I have an assignment table where I assign people to a task for a giving day. I can make an index where those three records as a group are unique. If I assign Jon to CleanUp on 1/1/2021 I can assign him another task today or cleanup on another day but cannot create more than 1 record for Jon to cleanup on 1/1/2021.

Thank you, a great explanation, bravo.

 

[Pia]

Are you able to help me with the final ordering required for, say, just the LB family, so that we see as per the Excel file I put in the drive?

For the purposes of monitoring a family, a 'family book' is created for all living family members minus independent males and those that have moved to other families (both of these tables are in the accdb already), with the ordering as set out in the PDF doc sent to your email.

Dreaming of a button on tree form that allows us to select a 'family book' and view then output to Excel in required format

It is the final ordering of output in Excel which I have failed to achieve.

Give me a few minutes before you check Google Drive for the Excel file....

 

[MajP]

I was about to say exactly that. I do not think this can be done in a single export, and make any sense. Not because of code, it cannot be logically. In your sorting it only works well in a very rare case that the generational level of interest is the only living generation. Let me explain.

In the LB family assume that this level and below is the only level with living members

Then you can list LIS82 as the oldest living. And sort all descendants (kids, grand kids, great grand kids) by youngest to oldest. Sisters would naturally follow oldest to youngest, and then Brothers. Then you can list the cousins for LIS82 sorted by Oldest to youngest with all offspring sorted by youngest to oldest. That would make sense.

Here is where I think the problem is, but it might not be. In fact LIS82's mother (not shown) is the oldest living member LIS. She is the only member of the previous generation.
So if you applied your rule.
LIS is the oldest then all of her offspring seen here would be sorted from youngest to oldest, Then here brothers and then her cousins. In this example LIS has no living sisters, brothers, cousins so you are not going to see much. This may be fine, but not sure if that gives you what you really need.

This may be fine. I might be wrong but I think you would might want a set of exports per family per generation.
So for family LB an export for LIS at Level 1
Then find the next oldest living in the next generation and create an export. That is not LIS82 there is an older living female cousin (same level) than Lis2. For each level of the family you find the oldest female in that generation.

 

[Pia]

I was about to say exactly that. I do not think this can be done in a single export, and make any sense. Not because of code, it cannot be logically. In your sorting it only works well in a very rare case that the generational level of interest is the only living generation. Let me explain.

In the LB family assume that this level and below is the only level with living members
View attachment 94699

Then you can list LIS82 as the oldest living. And sort all descendants (kids, grand kids, great grand kids) by youngest to oldest.

Sisters would naturally follow oldest to youngest, and then Brothers. Then you can list the cousins for LIS82 sorted by Oldest to youngest with all offspring sorted by youngest to oldest. That would make sense.

Here is where I think the problem is, but it might not be. In fact LIS82's mother (not shown) is the oldest living member LIS. She is the only member of the previous generation.

LIS moved to a different family (I think in 1999), she appears in tblFamilyTransfers, linked to tblPop thru' natal name. She needs to be removed at the outset, plus any other families that have members who've transferred (appearing in tblFamilyTransfers with same Natal Name).

LOB, full name Lobelia, is the oldest living member of the family.

So if you applied your rule.
LIS is the oldest then all of her offspring seen here would be sorted from youngest to oldest, Then here brothers and then her cousins. In this example LIS has no living sisters, brothers, cousins so you are not going to see much. This may be fine, but not sure if that gives you what you really need.

See above re LIS having moved to new family (LD).

This may be fine. I might be wrong but I think you would might want a set of exports per family per generation.
So for family LB an export for LIS at Level 1

See previous note re LIS having moved to a different family, the LD family in 1999.

Then find the next oldest living in the next generation and create an export. That is not LIS82 there is an older living female cousin (same level) than Lis2. For each level of the family you find the oldest female in that generation.

A series of exports I've tried, but because it needs subqueries in a loop I then attempted same in vba - is this not the answer to avoid a series of exports for each gen?

Now that LIS is solved, what do you think?

 

[Pia]

Sorry, when I say 'removed at the outset' re LIS, I mean removed from this family.. she appears in family LD.