• Choose a category
• All
• Classic-Fiction

By Root 1863 0

displays the employee-supervisor relationship to one level. That is, it shows each employee and his or her immediate supervisor. But you aren't limited to displaying one level of the hierarchy—the sample query qryEmployeeSupervisors3 displays three levels of the employee-supervisor relationship using four copies of tblEmployees and three left outer joins. The design of qryEmployeeSupervisors3 is shown in Figure 1-38; the output is shown in Figure 1-39.

Figure 1-38. qryEmployeeSupervisors3 shows three levels of the employee-supervisor relationship

Figure 1-39. Output of qryEmployeeSupervisors3

You can use the Access Relationships dialog to enforce referential integrity for recursive relationships. Select Tools → Relationships to display the Relationships dialog and add two copies of the table with the recursive relationship. Join the two copies of the table together as if you were creating a self-join query. Choose to establish referential integrity, optionally checking the cascading updates and deletes checkboxes. Click on Create to create the new relationship. Now when you enter a value for SupervisorID, Access will prevent you from entering any reference to an EmployeeID that doesn't already exist.

Although the sample database uses an employee-supervisor relationship example, you can use the techniques discussed in this solution to model other types of hierarchical relationships. This will work, however, only if each "child" record has only one "parent." In this example, each employee has only one supervisor. For hierarchies in which one child can have many parents—such as parts and assemblies in a bill of materials database—a separate table is needed to contain the multiple records needed for each child, each one specifying a different parent.

1.14. Create a Query That Uses Case-Sensitive Criteria

Problem

You have a table of words, some of which appear multiple times. Each instance of these words is spelled using a different combination of upper- and lowercase. You'd like to create a query that finds exact matches using case-sensitive criteria, but no matter what you type into the criteria for the query, Access always returns all instances of the same word, disregarding each instance's case. Is there any way to create a query that can select records based on case-sensitive criteria?

Solution

Access normally performs case-insensitive string comparisons. You can use the Option Compare Binary statement in the declarations section of a module to force VBA to make string comparisons that are case-sensitive within the bounds of that module, but this affects only string comparisons made in a VBA module, not comparisons made by the Jet engine. Thus, even when you run the query from a VBA Option Compare Binary procedure, any comparisons made in the query are case-insensitive. The problem is that the Jet engine doesn't know how to make case-sensitive string comparisons using any of the standard query operators. Fortunately, you can create your own case-sensitive string-comparison function in an Option Compare Binary module and call this function from the query. This solution shows you how to create the VBA function and how to use it to perform case-sensitive searches.

To use this technique in your own database, follow these steps:

Import the basExactMatch module from 01-14.MDB into your database.

Create a query for which you wish to perform a case-sensitive search. Add all the desired fields in the query grid.

Create a computed field in the query grid that references the acbExactMatch function found in basExactMatch. For example, if you wish to compare the Word field with a user-entered parameter, create a field like that shown in Table 1-7.

You can also use a hard-coded string instead of a parameter. We used a parameter in the qryWordCS query, shown in design view in Figure 1-40.

Table 1-7. Settings for the acbExactMatch field

Attribute

Value

Field

acbExactMatch([Word], [Enter word])

Table

(Blank)

Sort

(Blank)

Show

(Unchecked)

Criteria

-1

Figure 1-40. qryWordCS