Query to show ancestry in same row

[attrofy]

I am working with Access 2003 trying to display a heirarchy of animals names and registration numbers. The data was exported from File Maker, so it is a bit rough and I cannot reorganize the data structures. Having said all that, here is what I have:

Table: Ancestry

Registered Name | Reg # | Sire | Dam
-------------------------------------
Bob             | 2001  | Fred | Marsha
Ralph           | 5468  | John | Gwen
Gwen            | 3015  | Dave | Sandy
Dave            | 2045  | Phil | Sue

For case of example, Gwen and Dave are both Ralph's parents. What I am trying to do, is display this info on a report (and form) that shows Gwen and Dave as belonging to Ralph's lineage, while at the same time extract their individual Reg #'s.

My SQL statement looks like this:

SELECT Ancestry.[Registered Name], a.[ARI #] AS sire_ARI, b.Sire AS sire_name
FROM Ancestry AS a, Ancestry AS b
WHERE Ancestry.Sire=Ancestry.[Registered Name];

BTW, I am only working on one step at the moment, as I figured if I got one part working, I could use the same idea for other parts. So to begin with, I am just trying to match the registered name of the "offspring" with the sire, and get the Sire's Reg #.

I am brand new to SQL, but this seems to get me closer on the track then doing much higher convolutions in VBA or regular queries in Access. Granted, I may be missing something simple, but this seems to be on the right path. However, teh results are nowhere near correct. For starters, the querry is asking me for data for "Ancestry.Registered Name" and "Ancestry.Sire". In typing this, I realize I am missing criteria to sort on, as is evident by the data boxes that pop up.

I also have an "non-orphaned ancestors" query - which basically just finds all 1st level lineages (parents and children) and abandons all "orphaned" names. This list is queried against a "herd" list with a matching Registered Name feild - so all non-registerd names in the Herd list become abandoned (or orphaned). The SQl for that query is:

SELECT Herd.[Full Name], Ancestry.[ARI #], Ancestry.Sex, Ancestry.Breed, Ancestry.DOB, Ancestry.Color, Ancestry.DNA, Ancestry.Origin, Ancestry.Sire, Ancestry.Dam 
FROM Herd, Ancestry
WHERE ((([Herd]![Full Name])=[Ancestry]![Registered Name]));

So I am guessing I somehow need to tie the two togther - but havent a clue where to begin.

Thanks in advance for any help - sorry to overexplain this, but figured more detail is easier to peice together, tehn not enough.

 

[lespaul]

other than the fact that it appears that John is Ralph's sire, what you need to do is a self-join.

Check out http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins]Understanding SQL Joins[/url].

You'll need something like:

SELECT A.[Registered Name], b.[ARI #] AS sire_ARI, b.Sire AS sire_name, C.[ARI #] AS dam_ARI, C.Dam AS dam_name
FROM Ancestry AS a
INNER JOIN Ancestry AS b on A.[ARI #] = B.[ARI #]
INNER JOIN Ancestry AS c on A.[ARI #] = c.[ARI #] ;

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design