SQL question

[John Kraus]

I have an Access database with about 20,000 dogs in it and would like to
make a query that returns lineage information.

Here is a query that shows the basic structure:

The system is providing the primary key but the DOG_ID is unique.

SELECT DISTINCTROW Register.ID, Register.DOG_ID, Register.NAME,
Register.SIRE_ID, Register.SNAME, Register.DAME_ID, Register.DNAME,
Register.MEM_ID
FROM Register
GROUP BY Register.ID, Register.DOG_ID, Register.NAME, Register.SIRE_ID,
Register.SNAME, Register.DAME_ID, Register.DNAME, Register.MEM_ID
HAVING (((Register.DOG_ID)=[Enter DOG ID]));

This query prompts the user for a unique DOG_ID and returns the DOG_ID, the
dogs name, the names of the sire and dame, the unique DOG_ID of the sire and
dame and the name of the person who owns the dog.

All dogs have a record that with the above statement returns that record.

I would like to figure out a query to retrieve records that show the lineage
of a given dog. One that returns the records for not just the dog and its
parents as in the above, but the records of the grandparents, great
grandparents etc etc. The data is there, I just lack the experience it takes
to retrieve it in an elegant way.

The particular table has about twenty fields total that contain detail
information about each animal.



Thanks for any suggestions, John

 

[Allen Browne]

See:
Self Joins: tables that look themselves up (Pedigrees example)
at:
http://allenbrowne.com/ser-06.html

 

[John Kraus]

Thanks Allen
I know you participate here a lot and help a lot of people. I am sure from
what you have shown me I can figure it out. I rather figured this wheel was
already invented and thought I better ask before I go off on a tangent.

Thanks Again, John

See:
Self Joins: tables that look themselves up (Pedigrees example)
at:
http://allenbrowne.com/ser-06.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have an Access database with about 20,000 dogs in it and would like to
make a query that returns lineage information.

Here is a query that shows the basic structure:

The system is providing the primary key but the DOG_ID is unique.

SELECT DISTINCTROW Register.ID, Register.DOG_ID, Register.NAME,
Register.SIRE_ID, Register.SNAME, Register.DAME_ID, Register.DNAME,
Register.MEM_ID
FROM Register
GROUP BY Register.ID, Register.DOG_ID, Register.NAME, Register.SIRE_ID,
Register.SNAME, Register.DAME_ID, Register.DNAME, Register.MEM_ID
HAVING (((Register.DOG_ID)=[Enter DOG ID]));

This query prompts the user for a unique DOG_ID and returns the DOG_ID,
the dogs name, the names of the sire and dame, the unique DOG_ID of the
sire and dame and the name of the person who owns the dog.

All dogs have a record that with the above statement returns that record.

I would like to figure out a query to retrieve records that show the
lineage of a given dog. One that returns the records for not just the dog
and its parents as in the above, but the records of the grandparents,
great grandparents etc etc. The data is there, I just lack the experience
it takes to retrieve it in an elegant way.

The particular table has about twenty fields total that contain detail
information about each animal.