I cannot find anything to help me with this so hopefully it has not been answered already.

I have a single table:

Registration Number
Name
Sire
Dam

The sire and dam field are the registration number for the sire and dam. I can get the numbers, but how do I get the name field for those numbers as well.

I am trying to create a query which will show the following:

Registration Number
Name
Sire
Name
Dam
Name

You can't get the names of the sire and dam as you have not entered them into your table.
I would re-do the table as:
Reg#
Name
SireReg#
SireName
DamReg#
DamName

Your data would then already be in the order you want it.

In addition, you would be able to check on the foals of each individual sire and dam.

If I understand correctly you are going to have to select your table 3 times into the query grid join sire from 1st to reg in second and dam in first to reg in 3rd then select reg and name for horse from table1 reg and name for sire from table1_1 and reg and name of dam from table1_2, for the sake of this example I have used table1 as the original table name. The _1 etc is added by ACCESS.

Brian

you want a self join

heres an example - sorry, dont know the names of the dams, but you'll see what happens


horses dbs attached

Horseguy
Gemma's sample DB is exactly what I was saying and will probably be easier to follow.

Brian

Thanks Guys.

I knew there had to be a simple way to do this but could not figure it out. This is exactly what I was looking for.

For some reason the query is only returning 8 records instead of 9. It is the 7th record not being found. Any ideas why?

The Dam of the 7th record is not in the Registration list.

^