Access ELF Phrase Editor Window

The Phrase Editor is where you can define substitutions for words or phrases typed-in as part of a question. This is often the easiest way to improve the responsiveness of your interface.

Every industry has special terms, synonyms which won't be in any general purpose dictionary. Of course, many databases are inherited from the days when field names were limited to eight characters. (Old habits die hard, so people still tend to create fields named MTX_CRT -- whatever that means!) This is where you can teach the system whether people will be typing "matrix cartridges" or "miotoxin creatonin level" when they really mean the MTX_CRT field.

In fact, that's how we've labeled the columns of our Phrase Editor:
When I Type:     and     I Really Mean:

The important thing to remember about Phrase Definitions is that they're not optional -- if you define a substitution, Access ELF will do it every time. In general, words can have different meanings and be used in different ways, as nouns, verbs etc. So in general Access ELF tries all different combinations when searching for the right translation of a question. This doesn't apply in the case of Phrase substitutions, which work like "macros" in a programming language. Only the I Really Mean: part of a definition is considered at all, the When I Type: pattern is thrown away.

So if a travel agent were to define "book" as "purchase", this would allow her to type
Which clients have booked Hawaiian tours, and have it translated exactly as Which clients have purchased Hawaiian tours. But now imagine that the database also contained fields called Book Date and Departure Date. If we ask On what date did Smith book a Hawaiian tour? the system will translate it into On what date did Smith purchase a Hawaiian tour? Although "purchase" might help clarify which tables were implied (if "purchase" were defined via the Verb Mapper), we've now lost the obvious connection between "book" and "Book Date" -- and Access ELF might mistakenly pick the Departure Date for display.

The lesson is to avoid using the Phrase Definition facility with single words which have many meanings or associations. One alternative to the Phrase definition technique is to define words as synonyms of each other; for instance, the verb "book" could be defined as a synonym for the verb "purchase"; this would leave Access ELF free to interpret "book" either as a noun or a verb, and wouldn't block any of the associations it had generated on its own. For more details on Synonyms, see the Lexicon Lookup topic.

For more specific words or phrases, though, nothing beats the Phrase Editor for quickly getting from "what you say" to "what you mean". Let's review some possible uses. First we'll look at the simplest case, called "lexical phrases". (You'll learn about the difference between lexical phrases and string phrases later.)

When I Type: paperback     I Really Mean: not hardcover
This would be applicable if there were a Yes/No field called Hardcover (for instance, in a table of books). Then Access ELF would be able to answer questions like "Which publishers do not produce paperbacks?" or even "Which titles are available in both paperback and hardcover?"
Another similar example would be
When I Type: active     I Really Mean: not discontinued

When I Type: Northwind     I Really Mean: NWND
You can make sure that anything which is abbreviated in your database can be understood in either short or long forms. (This example is a holdover from Access 2.0 when Northwind was called NWND because of the 8 character limit on MDB names.)

When I Type: catalog information     I Really Mean: Category Name, Description, Picture, Product Name, Product ID, Quantity Per Unit, Unit Price
You can also abbreviate a long list of column names by a snappy substitute.

When I Type: salesperson     I Really Mean: employee
There are lots of things that Access ELF doesn't understand. Help it along by adding some common-sense information that it can't learn from its own dictionary.

You can also cascade these definitions, for example:
When I Type: male     I Really Mean: sex = M
When I Type: bachelor     I Really Mean: male and single

Finally, you can use this feature to give numerical meanings to expressions:

When I Type: college     I Really Mean: education >= 16
When I Type: high school     I Really Mean: education between 12 and 15

Although lexical phrase substitutions do work like macros, Access ELF is smart enough to detect words which are part of compound constructions, and won't replace them. For example, you can define:
When I Type: England     I Really Mean: UK
This will change "Show me suppliers from England" to "Show me suppliers from UK". But "Which products are from New England?" does not become "Which products are from New UK?"

If you have some programming skills, you might want to skip over this next part and go right to the Phrase Scripting section. With Phrase scripts, you don't need to rely on confusing string replacement rules -- you can define replacements that exactly suit your application, using your own VBScript or JScript functions.

String replacement:

While lexical phrase substitution is quite powerful, since it stems verbs and nouns prior to performing replacement, it's not the right tool for every job. There's another type of phrase replacement, which acts immediately before the lexical phrase facility. If you enclose the trigger (When I Type) part of a phrase rule in square brackets or double-quotes, the phrase (I Really Mean) part will be substituted for an exactly matching string. For example:

When I Type: [buy]     I Really Mean: order
would change the typed question: "Which customers buy seafood?" into: "Which customers order seafood?"

One difference between this method and the method described above is that this rule won't change "bought" (the past form of "buy") into "ordered". It works on character patterns, not words.

When I Type: [sales]     I Really Mean: Extended Price
would change the typed question: "Show the total sales for each product." into: "Show the total Extended Price for each product."

String replacements only work on strings that match at the beginning, so that
When I Type: [ale]     I Really Mean: [beverage]
will change the question "Which products are ales?" into "Which products are beverages?" But it won't have any effect on "Which category is Queso Cabrales?" Specifically, it won't incorrectly change this into "Which category is Queso Cabrbeverages?" (See this note for details about trailing spaces.)

In the real world, though, we'll want to make use of much more complicated substitutions. For example, when the word "sales" appears, we may want it to be ignored if used in combination with the word "quarterly", changed to "Extended Price" if followed by the word "product", but changed to "Subtotal" if followed by the word "order". The string substitution rules allow you to describe rules with this kind of if/then logic.

With string replacement, you can specify a conditional expression rather than just a replacement string. The conditional syntax is

When I Type: [example text to replace] (this is called the trigger)
I Really Mean: [ replacement text A | replacement text B ] OP context phrase
or
I Really Mean: [ replacement text A | replacement text B ] OP context phrase OP blocking phrase

where OP is < or > or *

< means, replace the trigger text by replacement text A if the context phrase is found in the query BEFORE the trigger, otherwise replace it by replacement text B.
> means, replace the trigger text by replacement text A if the context phrase is found in the query AFTER the trigger, otherwise replace it by replacement text B.
* means, replace the trigger text by replacement text A if the context phrase is found ANYWHERE in the query, otherwise replace it by replacement text B.

If the OP blocking phrase variant is used, it means, DO NOT replace the trigger text at all if the blocking phrase is found BEFORE ( < ) AFTER ( > ) or ANYWHERE ( * ) in the query.

If the replacement text selected is identical to the trigger text, the substitution is ignored, so another substitution further down the list can still be performed. This explains why string triggers can be denoted by using either square brackets or quote marks. To insert a second definition of the same trigger phrase, you must change the bracketing method. To control which rule acts first, add ordering strings to the Key column (such as "A" and "B").

The context-phrase and/or the blocking phrase can also be a list of phrases, enclosed in parens and separated by commas.

When I Type: "sales"     I Really Mean: [sales | total subtotal] < list
When I Type: [sales]     I Really Mean: [ Subtotal | ExtendedCharge ] > (product,order)

For more details on context-sensitive phrase rules, including advanced topics like creating "data-aware" substitutions, please see the Intermediate and Advanced sections of the Phrase Usage Primer.

It's important to understand that this technique will work only for calculations that reference symbols which Access ELF can interpret, like field names and mathematical symbols. For more complicated definitions, such as those referencing built-in library functions or your own Access Basic code, you'll need to add "Functions" to the ScriptName field, and re-run the analysis. See the Functions section of the Phrase Usage Primer for details. (The advantages of using this simpler method is that the definitions apply immediately as you type them into the Phrase window, and that fields from more than one table can be referenced. The advantage of the ScriptName=Functions method is that Access Basic functions and expressions can be included.)