SQL Problem

[John Kraus]

I have a table which has the following structure:

SELECT PedigreeTable.DOG_ID, PedigreeTable.NAME, PedigreeTable.Level,
PedigreeTable.S_SIRE_ID, PedigreeTable.S_SNAME, PedigreeTable.S_DAME_ID,
PedigreeTable.S_DNAME, PedigreeTable.D_SIRE_ID, PedigreeTable.D_SNAME,
PedigreeTable.D_DAME_ID, PedigreeTable.D_DNAME, PedigreeTable.SS_SIRE_ID,
PedigreeTable.SS_SNAME, PedigreeTable.SS_DAME_ID, PedigreeTable.SS_DNAME,
PedigreeTable.SD_SIRE_ID, PedigreeTable.SD_SNAME, PedigreeTable.SD_DAME_ID,
PedigreeTable.SD_DNAME, PedigreeTable.DS_SIRE_ID, PedigreeTable.DS_SNAME,
PedigreeTable.DS_DAME_ID, PedigreeTable.DS_DNAME, PedigreeTable.DD_SIRE_ID,
PedigreeTable.DD_SNAME, PedigreeTable.DD_DAME_ID, PedigreeTable.DD_DNAME
FROM PedigreeTable;

This is an example query and shows all ancestor dogs from the third and
fourth generation ago. The table actually goes six generations deep.

It is for tracking pedigree information of dogs. Each dog has a unique
DOG_ID and each record contains information tracking back six generations.
What I need to figure out is how to get the field [PedigreeTable].[Level] on
to a form and have it correctly display the information contained in that
field for each dog in every generation. The field contains two and three
letter codes that refer to specific awards and one to identify if the dog is
deceased.

The field(s) such as [PedigreeTable].[S_SIRE_ID] contain the same unique
identifier that is in the [PedigreeTable].[DOG_ID] field for a given dog.
Those fields display correctly on the form (pedigree tree) as do the fields
containing the dog names, [PedigreeTable].[S_SNAME].

Somehow I have to use the ancestor [xxxxxx_ID] field and reference it to the
DOG_ID field to accurately retrieve the [PedigreeTable].[Level] record
information for each of the ancestors of the dog being viewed in the
pedigree tree.

I am concatenating the fields [xxxxxxx_ID & " " & xxxxxxxx_xNAME] on the
form. I want to include the [PedigreeTable].[Level] information in that
concatenation. I can't do that unless I can figure out how to reference the
record of a given ancestor dog in the table.

At present, an example of the displayed information for every ancestor dog
in the presentation looks like:

AA-690KS, SPARKY

I don't know if this can be done easily. I am thinking this may require a
subquery for each one. There are 62 of them. YUK!!! Not to mention I don't
really know how to do that (properly). I've tried but have mostly succeeded
in making a mess.

Any help or guidance in achieving this is appreciated.

John Kraus

 

[SteveS]

Hi John,

A long time ago, I started doing a genealogy on my family. I found a little
info on pedigree charts. The father's index is always an even number (index)
and the mother's index is an odd number.

http://www.brrl.lib.va.us/kids/PedigreeTree.pdf


From the looks of your query, it looks (to me) like your table structure is
set up wrong. If you go up to 6 generations, in your current table design, it
looks like you will have over 126 fields (a flat file table - aka spreadsheet
format) >


Here is a link to a general pedigree data model:

http://www.databaseanswers.org/data_models/genealogy/index.htm


And here is a link to a site that shows what a pedigree query might look
like (Allen Browne's site):

http://web.archive.org/web/20031216110451/users.bigpond.net.au/abrowne1/ser-06.html


And lastly, here is a link to Roger Carlson's site that has an example
pedigree mdb (for cats - but you could modify it):

(should be one line)

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Query_Pedigree.mdb'


Sorry I couldn't be of more help...

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I have a table which has the following structure:

SELECT PedigreeTable.DOG_ID, PedigreeTable.NAME, PedigreeTable.Level,
PedigreeTable.S_SIRE_ID, PedigreeTable.S_SNAME, PedigreeTable.S_DAME_ID,
PedigreeTable.S_DNAME, PedigreeTable.D_SIRE_ID, PedigreeTable.D_SNAME,
PedigreeTable.D_DAME_ID, PedigreeTable.D_DNAME, PedigreeTable.SS_SIRE_ID,
PedigreeTable.SS_SNAME, PedigreeTable.SS_DAME_ID, PedigreeTable.SS_DNAME,
PedigreeTable.SD_SIRE_ID, PedigreeTable.SD_SNAME, PedigreeTable.SD_DAME_ID,
PedigreeTable.SD_DNAME, PedigreeTable.DS_SIRE_ID, PedigreeTable.DS_SNAME,
PedigreeTable.DS_DAME_ID, PedigreeTable.DS_DNAME, PedigreeTable.DD_SIRE_ID,
PedigreeTable.DD_SNAME, PedigreeTable.DD_DAME_ID, PedigreeTable.DD_DNAME
FROM PedigreeTable;

This is an example query and shows all ancestor dogs from the third and
fourth generation ago. The table actually goes six generations deep.

It is for tracking pedigree information of dogs. Each dog has a unique
DOG_ID and each record contains information tracking back six generations.
What I need to figure out is how to get the field [PedigreeTable].[Level] on
to a form and have it correctly display the information contained in that
field for each dog in every generation. The field contains two and three
letter codes that refer to specific awards and one to identify if the dog is
deceased.

The field(s) such as [PedigreeTable].[S_SIRE_ID] contain the same unique
identifier that is in the [PedigreeTable].[DOG_ID] field for a given dog.
Those fields display correctly on the form (pedigree tree) as do the fields
containing the dog names, [PedigreeTable].[S_SNAME].

Somehow I have to use the ancestor [xxxxxx_ID] field and reference it to the
DOG_ID field to accurately retrieve the [PedigreeTable].[Level] record
information for each of the ancestors of the dog being viewed in the
pedigree tree.

I am concatenating the fields [xxxxxxx_ID & " " & xxxxxxxx_xNAME] on the
form. I want to include the [PedigreeTable].[Level] information in that
concatenation. I can't do that unless I can figure out how to reference the
record of a given ancestor dog in the table.

At present, an example of the displayed information for every ancestor dog
in the presentation looks like:

AA-690KS, SPARKY

I don't know if this can be done easily. I am thinking this may require a
subquery for each one. There are 62 of them. YUK!!! Not to mention I don't
really know how to do that (properly). I've tried but have mostly succeeded
in making a mess.

Any help or guidance in achieving this is appreciated.

John Kraus