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
| 