MS-Access Tips for Serious Users

Provided by Allen Browne, allenbrowne.com

Self Joins

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:

1. Drag three copies of tblHorses onto a new query. For your own sanity, select tblHorses_1 and change its alias property to Sire in the Properties window. Alias tblHorses_2 as Dam.
2. Drag the SireID field from tblHorses to the ID field in Sire. Since we want the family tree even if some entries are missing, this needs to be an outer join, so double-click the line that defines the join and select 2 in the dialog box.
3. Repeat step 2 to create an outer join between DamID in tblHorses and ID in Dam.
4. Now drag four more copies of tblHorses into the query window, and alias them with names like SiresSire, SiresDam, DamsSire, and DamsDam.
5. Create outer joins between these four tables, and the appropriate fields in Sire and Dam.
6. Repeat steps 4 and 5 with eight more copies of the table for the next generation.
7. Drag the desired output fields from these tables into the query grid, and your query is ready to view.

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;