Provided by Allen Browne.
Sometimes a field contains data which refers to another record in the same table. For example, employees may have a field called "Supervisor" containing the EmployeeID of the person who is their supervisor. To find out the supervisor's name, the table must look itself up.
To ensure referential integrity, Access needs to know that only valid EmployeeIDs are allowed in the Supervisor field. This is achieved by dragging two copies of the Employees table into the Relationships screen, and then dragging SupervisorID from one onto EmployeeID in the other. You have just defined a self join.
You will become quite accustomed to working with self-joins if you are asked to develop a report for printing pedigrees. The parents of a horse are themselves horses, and so will have their own records in the table of horses. A SireID field and a DamID field will each refer to different records in the same table. To define these two self-joins requires three copies of the table in the "Relationships" window. Now a full pedigree can be traced within a single table.
Here are the steps to develop the query for the pedigree report:
Your query should end up like this:
And just in case you wish to create this query by copying the SQL, here it is:
SELECT DISTINCTROW TblHorses.Name, Sire.Name, Dam.Name, SiresSire.Name, SiresDam.Name, DamsSire.Name, DamsDam.Name, SiresSiresSire.Name, SiresSiresDam.Name, SiresDamsSire.Name, SiresDamsDam.Name, DamsSiresSire.Name, DamsSiresDam.Name, DamsDamsSire.Name, DamsDamsDam.Name FROM (((((((((((((TblHorses LEFT JOIN TblHorses AS Sire ON TblHorses.SireID = Sire.ID) LEFT JOIN TblHorses AS Dam ON TblHorses.DamID = Dam.ID) LEFT JOIN TblHorses AS SiresSire ON Sire.SireID = SiresSire.ID) LEFT JOIN TblHorses AS SiresDam ON Sire.DamID = SiresDam.ID) LEFT JOIN TblHorses AS DamsSire ON Dam.SireID = DamsSire.ID) LEFT JOIN TblHorses AS DamsDam ON Dam.DamID = DamsDam.ID) LEFT JOIN TblHorses AS SiresSiresSire ON SiresSire.SireID = SiresSiresSire.ID) LEFT JOIN TblHorses AS SiresSiresDam ON SiresSire.DamID = SiresSiresDam.ID) LEFT JOIN TblHorses AS SiresDamsSire ON SiresDam.SireID = SiresDamsSire.ID) LEFT JOIN TblHorses AS SiresDamsDam ON SiresDam.DamID = SiresDamsDam.ID) LEFT JOIN TblHorses AS DamsSiresSire ON DamsSire.SireID = DamsSiresSire.ID) LEFT JOIN TblHorses AS DamsSiresDam ON DamsSire.DamID = DamsSiresDam.ID) LEFT JOIN TblHorses AS DamsDamsSire ON DamsDam.SireID = DamsDamsSire.ID) LEFT JOIN TblHorses AS DamsDamsDam ON DamsDam.DamID = DamsDamsDam.ID ORDER BY TblHorses.Name;
|Home||Index of tips||Top|