How are things going? Haven't seen any updates.
As for Dog, Sire and Dam, I just tried a small test of this general structure for hierarchy.
With some sample records
DogId |
Gender |
Sire |
Dam |
DogName |
2 |
m |
0 |
-9 |
BillsGem |
3 |
f |
-9 |
-9 |
SassyLady |
4 |
m |
-9 |
-9 |
JackSprat |
5 |
m |
2 |
8 |
Bullit |
6 |
f |
-9 |
-9 |
QueenAnne |
7 |
f |
10 |
3 |
Princess |
8 |
f |
-9 |
0 |
AuntSadie |
9 |
m |
-9 |
-9 |
OlYeller |
10 |
m |
4 |
3 |
Rufus |
11 |
m |
4 |
8 |
Jedidiah |
12 |
f |
-9 |
-9 |
SarahQ |
And a query
Code:
SELECT DogTest.DogId, DogTest.Gender, DogTest.Sire, DogTest.Dam, DogTest.DogName
, M.dogname AS Mother, F.DogName AS Father
FROM (DogTest INNER JOIN dogtest AS M ON DogTest.dam = M.dogid)
INNER JOIN DogTest AS F ON DogTest.Sire = F.DogId
WHERE (((DogTest.Sire) Not In (0,-9)) AND
((DogTest.Dam) Not In (0,-9)))
To give result
Code:
DogId |
Gender |
Sire |
Dam |
DogName |
Mother |
Father |
5 |
m |
2 |
8 |
Bullit |
AuntSadie |
BillsGem |
7 |
f |
10 |
3 |
Princess |
SassyLady |
Rufus |
10 |
m |
4 |
3 |
Rufus |
SassyLady |
JackSprat |
11 |
m |
4 |
8 |
Jedidiah |
AuntSadie |
JackSprat |
The concept is that this hierarchy is all in one table.
For each Dog you record the DogID of the Sire in field Sire and the DogId of Dam in Dam.
At the top of the hierarchy--the top dog the sire will be 0, the dam will be 0. Value is arbitrary, but I wanted to show the top dog (furthest back ancestor) and used a -9 to indicate not known or to be processed.
This is similar to 2 self joins on the table
Dog,(Dog is Sire), (Dog is Dam).