RootsMagic 5 and below support for Ahnentafel numbering is limited to 32 bits, or 32 generations, even though the report allows the user to select a larger number. If the report does run over 32 generations, the Ahnentafel number starts over and is therefore incorrect. This query demonstrates that SQLite itself can support a 64 generation Ahnentafel report.
The results of this query look very similar to those from the Ancestors Query, with the addition of both a binary and decimal Ahnentafel number for the last person in a direct ancestral line from the starting person, expanded to 64 generations. It is a very large and slow query that cries out for help from a high level language because SQLite itself does not support loops nor binary-decimal conversion. If your database is large, I strongly recommend using a SQLite manager that supports run-time parameters so that the results are limited to the ancestry of just one person (SQLite Expert Personal or SQLite Developer), not that it is faster but rather the processing or results may exceed the software’s capacity to manage memory.
To build and revise it many times, I used Excel to generate the 64 lines from each of around 8 formulas, sorting and unsorting a large block of interleaved phrases for each revision. As such, the query is not very readable, having many very long lines and lacking indentation.
There must be a more efficient way of coming up with the lineage than the two similar methods I have used in this and its parental query (despite the similarity in the appearance of the results, Ahnentafel-64 uses a significantly different relationship in building the lineage). It is simply too slow to be attractive to run on databases larger than a few thousand persons. So there’s a challenge! Come up with a better one!
Ahnentafel-64.sql
Ahnentafel-64_tidy.sql– a formatted version that might be easier to follow.
— the Excel spreadsheet on which the query was developed using formulae.
Having torn my hair out trying to reconcile differences between RootsMagic 5’s Ahnentafel list and the results I was getting with my query, I discovered that RM5’s follows the parental line selected in the Pedigree View. I had one person shown with her adoptive parents and compounded with other issues I was having with incorrect Ahnentafel calculations, missing the last parent, etc., it took me quite a while to understand that some longer lines I was getting that were not showing up in the RM5 report were not because of an error in my query but because of this undocumented behaviour in RM5! My query follows the Birth parental line only; RM5 appears to follow whichever set of parents you choose.