When looking at Steven's record, one would see three names in the subform, those for whom his PID is selected in the "reports to" field. Conversely, the Employees form could have a subform showing subordinate employees for a manager, the obvious way to display a one-to-many relationship. A combo box is the most obvious way to display a many-to-one relationship in the interface.
The field "reports to" will often be displayed as a combo box, translating the number to a full name. This would be a cycle, a condition that can cause problems later. But there is no simple way to prevent Nancy from becoming Andrew's supervisor, in turn being already Nancy's supervisor. The table validation rules could prohibit an employee to be his or her own manager by forcing the field "reports to" to be different from "PID". PID Full Name Reports To - 1 Davolio, Nancy 2 2 Fuller, Andrew 3 Leverling, Janet 2 4 Peacock, Margaret 2 5 Buchanan, Steven 2 6 Suyama, Michael 5 7 King, Robert 5 8 Callahan, Laura 2 9 Dodsworth, Anne 5Įverybody reports either to Andrew (the boss) or to Steven (a manager). The data, seen in a query that reconstructs the full names, looks like this: The table most often used to introduce self-joins in Access is the old Employees table from the "Northwind Traders" demo database. Anyway, you can safely start reading without it. If it is, small indications in brackets and italics like below, designate a specific database object. The article should be readable without it it was at least written assuming the database wasn't opened in parallel. Many techniques are easier to understand using an actual database, so a demo file is attached. When the tables are large, these functions should be of the "fast table lookup" variety, making use of the index(es) defined on the table(s), or using specially constructed indexes. This article will explore what can be done with such data, and show that specialised functions might be needed for large or complex trees. They are treated just like any other one-to-many relationships, and no special SQL commands are available to navigate hierarchies.
The supervisor can in turn have his or her own supervisor, creating a hierarchy (and potentially cycles).Īccess, or rather the "Jet Engine", has little to offer to manage hierarchies. For example, a field "reports to" in the Employees table selects another Employee as supervisor. Some relationships are so called self-joins, where both sides are in fact the same table. In all cases, the left and right side are different tables. A Product is assigned to a Category (a many-to-one relationship), Suppliers are found for Products (a many-to-many relationship), Products are sold to Customers (an indirect many-to-many relationship, via Orders and Details). In a relational database, most relations are between two diffent types of objects.
0 kommentar(er)
