IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Provided by Allen Browne, firstname.lastname@example.org
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|
This is a cached tutorial, reproduced with permission.
Have your say - comment on this article.
What did you think of 'Self Joins'?
No comments yet.
Why not be the first to comment on this article?!
Have your say...