Verb Mapper (Settings/Verb Mapper tab)

The Verb Mapper is used to assign "action words" to the links between tables, or to assign several special purpose keywords associating one field with another. Verbs associated with table joins can help resolve questions that don't explicitly mention fields, particularly "wh-" type questions. For instance,"Which suppliers sell seafood?" is clear to Access ELF. But while "Who sells seafood?" means the same thing to us, Access ELF has no built-in way of knowing that "sell" implies "supplier". To fix this, we must associate "sell" with the link between Suppliers and Products.

In this case the relationship between Suppliers and Products is direct, so the entry should appear by default on the Verb Mapper window. Scroll through the entries using the Next Record arrow until Suppliers is shown on one side and Products on the other. To enable this association, type the word sell into the verb box. Finalize this choice by closing the window, scrolling away from the record or clicking on the Comments field.

For indirect relationships, such as between Customers and Products, we'd need to add an entry by clicking the New Record button (arrow+star). This opens a dialog offering pairs of tables; pairing a table with itself is both legal and often desirable. To associate "buy" with the link between Customers and Products, we'd select these tables, adding a new record, and type in "buy" just as in the previous example. Records added in this way can be removed using the Delete button. You may also add additional entries for two tables that are already linked. This allows you to specify link fields in place of the join fields which may show by default. To select link fields, click once on one field name on each side of the relationship.

The drop-down box for the Verb entry also offers three other choices, imply,govern and drill. These keywords can be used to activate special features of the Access ELF program. Imply connects two fields, from either the same or different tables, so that when one appears in the response, the other will also. For instance, after using the verb imply to connect the Products (ProductName) and Categories (CategoryName) tables, Access ELF would respond to "Show the products." not only with the name of each product, but also its category. Similarly we could imply a relationship between the CategoryName and its Description field. Now a question such as "Show the categories of products ordered by German customers." would display not only the CategoryName field, but its Description as well.

The govern keyword is used to establish hierarchical relationships, such as that between a supervisor and his subordinates, or between a parts-assembly module and its subcomponents. This is done by connecting the two related fields (eg. [Reports To] and EmployeeID), which must necessarily be fields of the same table. Once this hierarchy status has been established, Access ELF provides powerful tools for displaying not only the hierarchies themselves, but calculations performed over these hierarchies. We can show for each employee, for instance, the most recent hire date, considering only his own and those of the employees that work for him. Or we could sum the quarterly earnings for the organization, crediting each employee with his own sales plus those of his subordinates. See the Analysis Script and Worksheet topics for more details.

Finally, the drill keyword adds drill-down capabilities to the Worksheet response style, by introducing a relationship between two fields. The linked fields must come from the same table. A typical use of drill would be to establish County drill Region, so that double-clicking on the country lets see the current query broken out by region. A drill relationship can be set up in either direction, or both. Access ELF takes care of figuring out whether you're drilling "up" or "down" for any given double-click.

Let's take the most basic example. We'll link the Customer's Country and Region by adding a Verb Map entry relating Customer to itself. Click Country in the left panel and Region in the right, then select drill from the drop-down verb list. Touch the Flip button so that Region and Country swap places, and again select drill as the verb.

With response-style set to Worksheet, we'll ask this test question: "Show the country of each customer." The display includes the Country, CustomerID and CompanyName. Now double-click the word Brazil. You'll be prompted with the following message box:
Drill-down on Country Brazil to Region? (Yes)
OR Drill-down on all Country to Region? (No)
OR Cancel.

Choosing Yes will rerun the query, adding Region to the displayed fields but limiting the list to the Brazilian entries. Choosing No will add Region, without selecting a particular country.

Because we've defined the drill link in both directions, we could have done the mirror image operation if the question had been "Show the region for each customer." Let's say we notice the Quebec entry and want to focus on Canada as a group. Double-clicking Quebec brings up this prompt:
Drill-up on Region Quebec to Country? (Yes)
OR Drill-up on each Region to Country? (No)
OR Cancel.

Choosing the first option leaves only the Canadian customers, while choosing no expands the listing of all 91 customers to show their country of origin.

This drill-down and drill-up capability is at its best when used with summing queries. Starting with "Show the total quantity of seafood for each country in 1996" we get a list of the dozen or so countries that were our seafood consumers in the given year. We can break that down by geographic region by double-clicking one of the Country cells. For example, if we're interested in breaking out the results for America, we'll double-click USA. But if we want all national totals broken out further by region, it doesn't matter which country we choose -- we'll then follow up by answering "Drill-down on all Country to Region (No)."

If you've defined both the Country drill Region link and the Region drill Country link, you might suppose that alternately double-clicking the Country and Region fields will take you back and forth between views of the data. That's not exactly how it works. Actually, you may have several choices for each drill up/down operation, depending on whether the current worksheet has been narrowed or expanded. The prompts should help explain your options at each step along the way.