Self-Joining Queries in Microsoft Access | Database Solutions for Microsoft Access
All, I would like to implement a recursive relationship in a table with referential integrity enforced and cascade update turned on. For example, a. I thought that a recursive relationship between pay apps would be appropriate here instead of having an [AppNumber] field. I'm having trouble. You can display an employee-supervisor hierarchical relationship, also known as a recursive relationship, in Access with a select query that uses a self-join to.
Now, open tblEmployees in This table, which is shown in Figurecontains a primary key, EmployeeID, and the usual name and address fields.
Now run the query qryEmployeeSupervisors1. This query uses a self-join to display a recursive relationship between employee and supervisor; its datasheet lists each employee and his or her immediate supervisor see Figure Output of qryEmployeeSupervisors1 Discussion You can always model an employee-supervisor relationship as two tables in the database. Put all supervised employees in one table and supervisors in a second table.
Then create a regular select query to list out all employees and their supervisors. This design, however, forces you to duplicate the structure of the employee table. It also means that you must pull data from two tables to create a list of all employees in the company.
Finally, this design makes it difficult to model a situation in which employee A supervises employee B, who supervises employee C.
Access Cookbook by Paul Litwin, Ken Getz, Andy Baron
A better solution is to store both the descriptive employee information and the information that defines the employee-supervisor hierarchy in one table.
You can view the employee-supervisor relationship using a self-join query. You can create a self-join query by adding a table to the query twice and joining a field in the first copy of the table to a different field in the second copy of the table. The key to a self-join query lies in first having a table that is designed to store the information for the recursive relationship. The sample query qryEmployeeSupervisors1 displays the employee-supervisor relationship to one level.
That is, it shows each employee and his or her immediate supervisor. The design of qryEmployeeSupervisors3 is shown in Figure ; the output is shown in Figure Output of qryEmployeeSupervisors3 You can use the Access Relationships dialog to enforce referential integrity for recursive relationships. Join the two copies of the table together as if you were creating a self-join query.
Reinforce Recursive Relationship
The lngID field and the lngSupervisorID fields are of the same data type and contain the same data across different records: How do we relate Date within a single Table? To Create a Self-Join Open a query in Design View Click on the Show Table button Add the table to your query so that it appears twice Join the related fields Complete the query design by adding the fields that you want to display in the grid Using an Alias in the Query When adding the same database table to the query design grid multiple times, it is a good idea if you change the name of one of the tables.
When you assign an alias to a table in the query design grid, you are not renaming the underlying table. Creating a Self Join Query If you consider the above example, where we would need to design a query to display the Supervisors names for each Employee in our database.
In order for us to design a query that will display the Supervisor name for each Employee held in the database table we will need to relate the lngEmpID field and the lngSupervisorID field. In a new query design grid we will add the tblSupervisors table twice as shown below: You will see that this assigns the same table name again, followed by an underscore and number. We will assign an alias to the second table by right-clicking on the second table and choosing to display the properties menu.
In the Alias property box, type in the new name that you will alias the table with and close the property dialog as shown below: We can now create the self-join based upon the lngSupervisorID from the Supervisors table and the lngEmpID from the newly named Managers table by dragging one field to the other: We can now add the required fields to the query design grid. In this example, we are going to add the strFirstName and the strLastName fields from the tblSupervisors field list and the strLastName from the Managers field list.
We should now change the caption of the strLastName field that will display the data from the Managers table as at present we have two strLastName fields. In the design grid, we shall right-click on the strLastName field from the Managers table and choose Properties.