Microsoft Access Office VB VBA Help and Examples
           
             

Access - Genealogy Example

Also known as the "relational nightmare" example.

Please note this example is fairly straight forward however its not for the light hearted. It's design is based around maximum flexibility with minimal data complexity.

Quite often people will want to store ancestry information in a database. For people this is relatively straight forward, however for animals when litters contain many pups and you need to track litter generations and sibling relationship it can be a nightmare, and displaying this information can be just as difficult. The example database shows you how to store that information in just two easy tables. It has one example query showing how to create a family tree and one report showing how to use that tree.

Table tbl_details contains a list of all people / animals, their name , their sex and the mating/litter they came from. Table tbl_matings contains a maleid(father) a femaleid(mother) a MatingID (unique ID for each mating) and the birthdate. Because each male and female could mate more than once we need to track which mating ID a particular person or animal came from

These two tables can store all the information we could possibly want. and are already normalized. I have created lookup links between the tables for the ID's to make it easier to select items and to ensure there are no errors.

The query qry_TreeLinks shows how to extract a family tree from the two tables. It uses the much misunderstood alias ability of SQL (the ability to call a  table or field something else on the fly). If you look at the SQL you will see it's a bit of a mess, but the design view makes a little more sense.

If you run the query you will see there are people who have incomplete information, this is like the top of a family tree as you don't know their ancestors. If you discovered them you could still add them, it would not be a problem.

To create a query like this you just bring in the tbl_details (2 to the power of n)-1 where n is the number of generations you want to go back and bring in tbl_Matings (2 to the power of (n-1))-1. Then you need to give the tables good alias names. First of all lay them out as I have going from left to right, right being the older generations (A large monitor at high res is helpful here). Then link the tables. All of them must be Left joins (create the link then double click it and choose the second option).

To rename the tables, as they will have names like tbl_details1, right click on them and choose properties. Then change the Alias name. Try to think of a convention. The one I've used is fine for a few generations but for ten generations the names would become very unweildy.

Drag in the fields you need. I'm only interested in names so I've brought in each name, and run the query.

The report TreeReport shows the finished result based on qry_TreeLinks.

Genealogy example Database for Access 97

Genealogy example Database for Access 2000

 
HOME   SEARCH SITE   PRIVACY POLICY   CONTACT
The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.