Query
Query is the essential function provided by the Access ELF program, which all the other facilities are designed to support. We use "query" to mean the process of typing in a question about the information in your database, and its translation into the "native" Access data language (SQL); it also includes the automatic formatting of the answer in any of several response styles including datasheet, form, worksheet or graph.
The Basics
In general, all query styles are requests for information of one form or another. They include asking direct questions, giving commands to list or display certain data, naming or describing the data set you're interested in, and using key phrases to trigger special formatting or graphing features. Syntax which is NOT supported by the program includes any statement designed to change the data in your tables, or to change the structure of a table within the database.
So for instance, Access ELF permits direct questions such as:
Which customers bought seafood last month?
or statements like:
Show the customers that bought seafood last month.
as well as descriptions of the required data:
Customers that bought seafood last month.
Finally, a simple example of a data formatting keyword is "graph", as in:
Graph the customers that bought seafood each month.
Updating data using queries
The answers returned may or may not be "updateable" recordsets; this depends on the complexity of the query and the relationships between the database tables. Access ELF does not enforce a "read-only" property on the datasets it returns, so you should be aware that writing over the entries in the returned datasheet will change the underlying data; unless Microsoft Access itself prevents you, which it usually does by beeping and displaying a "This Recordset is not updateable." message. For instance, if you type in Show the number of customers that bought seafood each month. and try to type an "8" over a "7" for the month of June, Access will not permit this because of its data-integrity rules -- the sum must match up with the order details. Sometimes the reasons why Access objects to a change are less clear, for instance if you try to update data from tables participating in a one-to-many relationship.Access ELF does not directly support changing data by using English statements because we believe that -- while English query tools are coming into their own as useful, even essential data access instruments -- they are not yet reliable enough to trust with the task of mass changes to your database. On the other hand, we don't block its use as an update tool, for instance by enforcing a "snapshot" property on the datasets returned. In some cases, it might be useful to combine the reporting and updating features, for example by listing the records satisfying some condition (employees in Washington) then updating some field (eg. changing [Reports To] from 2 to 3, so that everyone who reported to Fuller now reports to Leverling).
Although Access ELF does not deliberately prohibit this, it often generates recordsets that aren't updateable, due to use of the DISTINCT keyword. While this guarantees that there won't be any redundant information reported, it also prevents updates. You can override this by switching a query into design view, right-clicking on the QBE (Query-By-Example) grid, and setting Unique Values to No.
Viewing Samples
One way to become familiar with the many permissible styles of query is to use the built-in Samples listing, which will show examples from Northwind and several other databases which match the query currently entered into the Query window. For instance, if we type Show Reports To for employees in Washington, then click Edit Query (Eraser) on the Toolbar, and click Samples, we'll see a list including:show photos of employees hired during 1991
show the ship date and subtotals for all orders since March of 1991
give the copyright dates for Ernest Hemingway's books
how many of Hemingway's books were published prior to 1960?
compare sum of subtotals showing customer name across and employee down
average together all the prices of Hemingway's books
graph the sum of subtotals for seafood against beverages
which customers have placed exactly 6 orders for seafood since 1991
show alphabetical listing of customers
Employees who live neither in the UK nor in Seattle
This lister uses a very simple word-counting method to try to find examples that are "like" the query you've typed in. It's not an exact science; for instance here the queries about "Hemingway" and including "against" probably result from the "in" part of "employees in Washington". The real purpose is simply to expose you to some of the possible constructions that you might use, without overwhelming you by "dumping" all of our samples. Of course, if you want to view a more complete list, you can click the Assorted Samples button, which shows the example list without applying a filter.
Editing a Query (fixing Access ELF's mistakes)
Before we talk about the results of a query, namely the response, let's go over what you can do if the translation from English to SQL doesn't generate the results you expect. Although it's quite hard to write SQL from scratch, it's often much easier to note and correct flaws in SQL that's generated for you. If you are posting these query translations to a large database, it may be prudent to do a "sanity-check" on the SQL, a quick scan that checks whether the code seems to include all the right elements in the right place.
We might type in, for example, Show the dates for workers in Washington, intending to view information about their dates of employment. Either by reviewing the SQL or by looking at the columns returned, we see that Access ELF has interpreted "date" to mean "Birth Date" rather than "Hire Date". By clicking on the Edit Query button, then selecting the "What's Wrong" drop-down box, we can get a look at some of the basic reasoning Access ELF applied to this translation. First, "worker=>Employee" -- this seems correct. "Employee=>Employee ID" -- well, OK. 'WA' as data -- looks about right. [Birth Date] as field -- not what we were expecting.
There are actually two ways to fix this mistake, aside from the obvious one of changing the query to read "Hire Date" (for instance, you may not immediately know the name of the field that provides hiring date information). Simply select [Birth Date] from the "What's Wrong" drop-down and click Try again -- ELF will retranslate the query using every option it can think of without interpreting "date" as [Birth Date]. In this case, the next best translation gives you what you actually want, the [Hire Date]. Another way to accomplish the same thing would be to click the Employees table in the "What's Missing" list, select [Hire Date] and click the [Add to "missing" list] button. In some cases, it may help to combine these two approaches.
In other cases the Edit Query window won't help much, if at all. (We hope that this is just a "work-around" until the basic system recognizes most queries accurately, and until more customization wizards are in place to help you tailor interfaces to eliminate these mis-translations.) In these cases, you still have two choices before you revert back to the "roll-up-your-sleeves-and-write-SQL" alternative. If the error is straight-forward enough, you may be able to patch the SQL by making changes directly into the SQL window, then clicking Execute. In fact, you could do that here, by typing the word "Hire" over "Birth". But sometimes there's no simple, obvious change to get from where you are to where you want to be.
Let's say we've typed "Reports" instead of "Reports To" in the example above:
show the Reports field for employees in Washington
If we run this query, we'll see all kinds of information unrelated to the question.
The first records starts out: "Soft drinks, coffees, teas, beer, and ale". Checking the SQL,
we see references to Categories.Description and Categories.[Category Name]. What's going on?
In this case, Access ELF has changed "Report" to "Description", and "field" to "Province" then to "Region". In fact, both of these are pretty reasonable, given that the dictionary definition of "REPORT" is "history;statement;account;DESCRIPTION" etc. "FIELD" is defined as "world;area;department;subject;territory;PROVINCE", and "PROVINCE" is "world;AREA" etc. while one of the fields that Washington comes from, "Region", also has a very similar definition, "AREA;neighborhood" etc.
While this is some comfort to us as designers of the system, it doesn't help you -- the user -- get the information you're after. And there are just too many errors in the "What's Wrong" list; checking off any combination doesn't seem to help.
Still, you can use the information in the "What's Wrong" listing of possible candidates to
help you modify the original query. It's not hard to see that "Report" is being interpreted in
a different way than you meant it, in this case. Now you can be more specific, typing "Reports
To", instead of just "Reports". If you're administering a natural language interface for others,
you can use this kind of error to help suggest additions to your list of Phrase substitutions,
or possibilities for the verbs included via the Verb Mapper. In this case, adding
When I Type: report
I Really Mean: report to does the trick.
Response Styles
The style of response is selectable from the Query window by clicking on Datasheet, Form, Worksheet or Graph. If you choose Datasheet (from the Query window), you're actually selecting either Datasheet or "Best Guess", whichever was picked last from the Responses tab of the Selectable Settings window. ("Best Guess" works exactly like Datasheet unless your query contains a reference to an OLE object, such as a picture -- in that case, it displays the answer in a form.) If you choose Form (from the Query window), you're selecting whichever style of Form response was selected last, including Autoform, Custom, or the application form currently selected from the My Response Style group with the "Use As Active Form" check box.
The Worksheet style is the newest Response style; we'll talk about it first because it also relates to the topic of verifying and correcting errant query translations.
While the basic "scan the SQL" method works for simpler types of queries, Access ELF has the
ability to generate extremely complex sequences of SQL, to answer considerably complicated
questions. In fact, Access ELF can generate up to seven sequential SQL statements in response
to a single question. While we can't come up with any examples offhand that require the full
seven, here's an example that takes six:
Show the percentage of customers that have ordered every Produce product.
To do this, Access ELF creates a list of the customers that have purchased products from the Product category, then counts the number of distinct Produce products each customer has ordered. It weeds out those that have purchased fewer than the total number of products in the Produce category, then returns a count of the remaining records, as a percentage of the total number of customers.
In this case, Access ELF performs flawlessly, but you can immediately see that along with the capability for such responses, Access ELF has the potential to create complex gibberish. In fact, if only a single symbol in the entire 42-line SQL sequence is out of place, the answer could be meaningless or incorrect. This is why it's important that the SQL generated be verifiable in a step-by-step manner. To this end we've created the Worksheet response style, which is sort of a QBE grid on steroids, for dealing with the multiple SQL statements that Access ELF can come up with.
The Worksheet shows you the intermediate datasets that chain together to form the eventual answer, as well as the individual statements of the SQL chain. An Explanation box shows a precis of the approximate logic that Access ELF is using to cobble together the answer to your query.
For this example, the Q1 explanation is not too illuminating -- this is just the source
for the eventual count of all customers, for the denominator of the percent. (Note that
Access ELF interprets percentage of customers to mean "percentage of customers who have placed
at least one order", which is why it doesn't simply use the Customer table for this calculation.)
Starting with the second SQL statement, Q2, the Explanation is more interesting:
Query assignment, show records associated with each and every one of a filtered set.
Step 1: Select records meeting the filter conditions.
The "records associated" are the customers, and the "filtered set" is the set of products
filtered by belonging to the Produce category.
The Q3 explanation reads:
Collect "associates" (associated with at least one of the Step 1 records).
In this step the customers that have ordered Produce are listed along with the Produce item.
The Q4 explanation reads:
Filter associates, retaining only those associated with EVERY record in Step 1; this is done by
making sure that the count of distinct records for the associate is equal to the total count of
distinct (unique) records in Step 1.
The corresponding SQL uses a clause:
HAVING count ( [elfQ3].ProductName ) = elfUCount ( "ProductName" , "[elfQ2]" );
to effect this.
The Q5 explanation reads:
Show a complete answer including the fields used to filter the original records, the associate records,
and any key information to help identify the reasons for the associations.
Actually, this is kind of wasteful, since in the case of a percentage query the only important
data is the final, single number. But sometimes Access ELF can't determine what is strictly required
until it reaches the end of its computation.
The Q6 explanation reads:
Display the ratio of the selected set (the previous query) to the full set (the first query) as a percentage.
(Note that the ratios are calculated from the count of distinct entries in the selected field, ignoring duplicates.)
Other natural language systems sometimes use what's called an "echo" approach to verifying the correctness of generated SQL. For instance, English Wizard/EasyAsk will generate a restatement in a kind of pidgin English, targeting something between the accuracy of SQL and the clarity of English. There are several problems with this approach. First, if you try to be as specific in the Echo as in the SQL itself, you may confuse less technical users -- the very user this feature is designed for. If you implement this feature imprecisely, you may also wind up obscuring rather than clarifying. For example, Linguistic Technology Corporation's products often generate a Restatement that has no discernible relation to either the SQL or the English input, leaving the user completely baffled. Finally, even an accurate restatement of a complicated question doesn't guarantee that the SQL generated is actually correct!
For specific comparisons between ELF Software and other natural language products in the market, please visit the References page of our Web site.
Worksheet mode has proved to be enormously popular, in part because of all the other features which are not related to SQL verification. We now offer a split-screen view of both the datasheet and graph, your choice of two Microsoft graph styles, along with the popular Pinnacle-BPS graph control. The sliding radio button at the top controls the dimensions of the two panes -- either slide it along with the mouse, or click to its left or right, to change the setting.
As with the graph pop-up forms (Graph Style Response) the Microsoft Graph can be double-clicked to edit its properties, and we've added additional sort and filter properties to help you get exactly the image you're looking for. The Pinnacle-BPS graph features attractive options for displaying sets with more than one numeric field in full 3-D (Z-clustered), especially useful for the results of crosstab queries. (Crosstabs are generated using special keywords; eg. Compare the sum of subtotals showing the customer name across and month of order date down during 1994). In addition, the Worksheet footer offers several additional customization features. One is the ability to graph only selected fields. To use this option, open the Chart pane and highlight a numeric column by clicking its column header. This adds it to the Selected list. You can add as many numeric columns as you like, then click Graph Selected Columns. In fact, you can combine select either numeric or text (or numeric key) entries into the paired value/label list, to explicitly determine both the chart series and captions for a given graph.
The other new option answers the question we frequently see posted to message boards, how do I get a list of the unique entries in a column? Simply select the column with duplicates (using the same highlight method), then click the Remove Dupes toggle button. The SQL will be rewritten to show only the first value of each given item for that column. Unclick the toggle button to recall the original, unfiltered set.
There's also a powerful Calendar display option. which is actively tied to any date-tagged data in your Worksheet.
Let's look at a couple of examples of these powerful new features in action. In our previous release, we took the question "Show the total quantity and average discount for seafood products." and showed how it could be constructed from Access ELF's response to separate quantity and discount questions. As Access ELF improves, it gets harder to find tough examples -- that one can now be answered just by typing the question. One problem does remain, though, with this type of "double aggregate" query; see multiple aggregates. We'll use that problem query: "Show the total Subtotal and total ExtendedPrice for each country" to demonstrate the editing features of the Worksheet. This example requires setting up a View using both the [Order Subtotals] and [Order Details Extended] queries. If you'd rather just review the simpler example from version 3.0, see the Exercises page.
As described in the FAQ entry linked above, the trouble with this question is that it requires us to view the [Order Details] table at two different "aggregation levels" at one time. The [Extended Price] field defines a calculation showing the revenue from each line item -- the actual money received based on quantity and price minus discounts. We can ask for sums of these values over a group (say, by country) with no trouble. On the other hand, the [Order Subtotals] query defines a calculation for the total receipts for an order (not a line item of that order) based on the same calculation. So, in fact the totals for [Extended Price] and [Order Subtotals] would have to be the same when summed by country -- since every item in the order is ordered by the same customer (from the same country). Unfortunately, the fact that they're really the same thing doesn't make it easier, it makes it harder. Access ELF can't yet figure out how to add one up by country and the other by both order and country -- at the same time. So it gets mismatching sums.
We can break down the problem, just as we did in the past with "Show the total quantity and average discount for seafood products." Here's how it's done. Enter "Show the total Extended Price for each country." Access ELF generates a two-part SQL statement to answer this question. Use the Save button to store this pair of related SQL statements. Use any name, for instance MyQuery. Now change the question to "Show the total Subtotal for each country." (Later you can define "sales" to mean "total subtotal" if you don't like this wording.) Once again Access ELF generates the correct (and identical) column of figures. To get a query which displays them both at once, press the Add button. Right-click next to the elfQ2 box and click Show Table. From the Query tab select MyQuery2 (the answer to the Extended Price question). Link the Country fields of both boxes by dragging one to the other. Now double-click the [sum of Subtotal] field, adding it into the selected field list. Close and save, and your worksheet will now have two identical columns, one of Extended Prices (line items summed by country) and the other of Subtotals (line-item subtotals summed by country).
Let's look at just a few of the other features of the Worksheet. For documentation of each and every Worksheet control, see the What's This page listings under Worksheet.
While working with large recordsets in Worksheet mode, it can sometimes be useful to restrict the number of rows being processed until you're happy with the final result. The Restrict rows to
drop-down box lets you choose (or type in) a value for the maximum number of records to display. This is especially useful when creating graphs, which can take some time to display. Once the graph accurately reflects the values you want for a representative sample, click "all" to view the whole set. Since this TOP N feature is individually selectable for each query in the chain, remember to reset this value to "all" for each query you've restricted.The whole Worksheet is fully resizeable, and should give acceptable displays even on 640x480 resolution screens, although it is designed for use with 800x600 and higher. The remaining options are the Footer checkbox, which obviously hides or displays all the controls below the datasheet and graphs, allowing an expanded view of the information. The graphics controls for the Microsoft Graph 5, Microsoft Graph 6, and Pinnacle-BPS graph engines will be discussed in the What's This pages. The drop-down box displaying the current query also lists the previous queries from this working session. You can click one to recall it (or type in a new query), and then click Respond to run the query and return immediately to the Worksheet.
Incidentally, it is possible to have more than one graph active at a time, now that there are two styles of graph response, the Worksheet graph and the stand-alone graph. To do this, first bring up the stand-alone graph, by selecting Graph style and entering something like "show the number of employees in each city" (or, by selecting Datasheet and typing "graph the number of employees in each city"). Next click the Worksheet button on the toolbar. The Worksheet will come up in Datasheet mode, but you can switch to Graph or Split styles and make changes to the two graphs independently, for instance viewing one as a bar chart and one as a pie chart. You can also manage to have graphs of two different data sets displayed simultaneously. Once you have a graph you're satisfied with in the Worksheet, you can open the Settings window, change to Graph style, pop open the SQL window, and enter (or cut-and-paste) the SQL for another graph, then click Execute. Be aware that once you've done this, you've overwritten the SQL used to display the original (Worksheet) graph so if you cause this graph to refresh (by making any changes to the Worksheet options, such as full/split view) the Worksheet graph will redraw from the same dataset as the pop-up graph.
Last Updated: March, 2002