Northwind Tutorial

This example shows how simple an Access ELF installation can be when working from a well-structured database whose fields and tables are already named with meaningful English words.

Your installation may not be this easy, but it shows how important it is to begin with a "clean" database, one that has relationships between the tables well-defined, and one whose constituents are already referred to by names that people will recognize, and use naturally in asking questions. Remember, in the olden days of desktop databases, everything had to be named with 8-character identifiers. But today there's no reason not to use clear, simple English to name your database structures.

Step 1: Start Access ELF from the Northwind Database

To begin, open the Northwind database. Close the Main Switchboard and select Access ELF from the list of available add-ins. If you need help in installing Access ELF, see the installation instructions.

Step 2: Run the Analyzer on Northwind

From the Access ELF toolbar, click the A (Analyze) button.

You'll see that the default name given to the View (another name for a query interface), is the same as the database name, in this case Northwind. The Access ELF Analysis window also shows the location that interface subdirectories are stored; for instance, running this analysis will create a NORTHWIND.ELF subdirectory in the location shown.

Click the Analyze Queries checkbox, so that Access ELF will be able to include any of the predefined queries that meet its criteria. We'll go over these shortly.

You can click either Express or Custom to run the Analysis. The Custom button leads you into a dialog that lets you modify certain options. We're going to leave all the options as preset, but let's click the Custom button anyway, just so we can see what selections Access ELF has made for us. Out of the list of possible tables and queries, we see that Access ELF has chosen to include all the main tables (Categories, Customers, Employees, [Order Details], Orders, Products, Shippers and Suppliers) as well as two Queries [Order Subtotals] and Ten Most Expensive Products. In addition, there are other queries which may be included at your option, but are initially unselected. For instance, the Invoices and [Order Details Extended] queries are also candidates. Click the Show Selected Only box to get a view of only the currently selected data sets.

Queries which have parameters are never included and only Select or Crosstab queries are permitted. Queries which reference other queries are disallowed. In addition, to be a candidate a query must either perform a math operation on some data, or "alias" (rename) a data field. If a query meets these conditions, it's included in the list of selectable queries, and in addition, if the query uses data from only a single table, it's included in the Analysis by default. So for instance [Order Subtotals] does a math operation to get the sum which it defines as the Subtotal field -- Subtotal: Sum(CCur([UnitPrice]*[Quantity]*(1-[Discount])/100)*100). It uses only data from [Order Details] so it's included by default.

The [Order Details Extended] query also performs a math operation (the same one!), to define the [Extended Price] field -- ExtendedPrice: CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100. But since it uses information from both [Order Details] and Products, it's not included by default. However, it's a very good candidate for inclusion, and in fact we include this query in our Web demo, to help answer questions about the "sales" (meaning the amount of money generated by sales) of different products.

The Ten Most Expensive Products query might not be such a good choice, because after all, now that you have Access ELF you can ask this kind of query on the fly, for any number you choose. But for now let's leave it in and see how Access ELF performs using its default choices. Click Accept. A progress meter appears in the upper right. On the status bar you will see four separate mini-meters in sequence: Pass 1 of 3, followed by a "Compiling lexicon:" meter, followed by Pass 2 and Pass 3. These "passes" create the three separate layers of our interface which combine to do translation between English and SQL. Later, for those interested, we'll take a look at the intermediate results of these layers (but most of the time this just works in the background).

The Analysis should take about two minutes on a 200 MHz machine. The "Ready to respond" message appears when you're good to go!

Step 3: Ask a simple question

To make sure that everything has worked, we'll ask a basic question. Type:
How many employees are there?
and click the Respond button with the mouse. Some stuff flashes by on the status bar and then Access ELF immediately opens a datasheet with the answer to your query. It's captioned elfQ2: How many employees are there? and has a column header Count_Of EmployeeID, with the answer 9 in the one and only row.

If you click the SQL button on the Toolbar, you'll see the translation:
SELECT DISTINCT Employees.EmployeeID , Employees.LastName FROM Employees ; SELECT elfQCount ( "EmployeeID" , "[elfQ1]" ) AS [Count_Of EmployeeID] FROM elfRow in 'C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\ELF32.MDA' ;

There are a few mysterious features about this SQL worth explaining. First, why are there two SQL statements. The answer to this is that Jet SQL doesn't support a COUNT DISTINCT operator. Now sometimes you want to see the number of different thingies, and sometimes you don't. If there's any doubt, Access ELF will ask you which you mean. For instance, if you were looking at the Order Details table and asked about the number of products, you might want to exclude duplicates (this would tell you how many products had sold at least one unit). Sometimes there's no difference between one and the other. For instance, in the Employees table, there are no duplicate employees. The second SQL statement uses the elfQCount function to count up both the total number of employees and the number of different employees, then it compares them. (If they're the same, it doesn't bother you by asking which you mean!) Now, because the answer to this question is a function result, not pure SQL, we need to fool Access into thinking we're getting data out of a table, not out of thin air. That's the purpose of the reference to elfRow in the Access ELF library file. elfRow is table with a single row -- this guarantees that the result from elfQCount is reported exactly once.

As usual when we explain the mechanics of our program, someone will note that it's not the most efficient process in the world to calculate both answers, and then throw one away. Very true. Our software is often inefficient, all with the aim of making it quick and easy for you to get answers.

Step 4: Prove you're smarter than the software

We're no different from anybody else, the very next thing we do is try to break the system. (You have no idea how many times people type "What is the meaning of life?" into our Web demos.) We'll type in: How many employees report to someone who has sold more units of tofu than they did? The first thing that happens is that a Spelling Correction dialog box appears with the word "someone", since this happens not to appear in the dictionary. Or more precisely, "someone" appears in the dictionary as "r" (or Pronoun) -- see the Moby Dictionary and POS Key topics for more on this -- and pronouns are not handled. (This could lead us into a discussion of the anaphora, or "referring back to last question", feature we're working on for the next release, but let's skip over that!)

If we use the spell checker to change "someone" to "employee", then Access ELF responds in about a minute that it cannot understand the words "report", "sell" and "unit". This will only happen the first time that these words are used in a query which Access ELF cannot interpret. This is intended to help you figure out what the stumbling block is in the interpretation, but it doesn't necessarily mean that these words need manual definition. For instance, we can immediately ask How many units did each product sell? and get a listing of the sum of the quantities sold (from Order Details) grouped by product. In other words, Access ELF has just understood the meaning of "unit" relating it to "quantity" as well as "sell" (either by relating it to [Order Details], or at least not relating it strongly to something which leads to an incorrect answer). So the meaning of the previous error message is that in the context of the given question Access ELF was unable to understand the listed words.

Step 5: Prove how powerful Access ELF really is

If we really did want to know the answer to such a complicated question, we're not at a standstill. With previous releases of Access ELF, either you got the answer or you were pretty much stuck. But with the new Worksheet mode built into release 2.0, you can build from a simpler query up to a more complex one. For instance, let's get our footing by asking a more basic question: show total quantity of tofu for each employee Once we see that Access ELF can handle this query, we can try: show total quantity of tofu for each employee, sorting by ReportsTo

At this point we can switch to Worksheet response style. This has many nifty new features, but the most important is the ability to edit these kinds of sequenced SQL statements. Click the Edit button and the current query is loaded into an MS Access Query-By-Example grid. Now we can change it, for instance by right-clicking, selecting Add Table, and double-clicking the Employees table. Access automatically creates a link on the EmployeeID field -- but that's NOT what we want here! Delete the link and instead add a link from the ReportsTo field of elfQ1 to the EmployeeID field of Employees. (To make sure that people with no boss also appear, change this link type to "Include ALL records from elfQ1".) Now double-click the LastName field of Employees, then change the Field row from Lastname to Supervisor: LastName. Close and save the changes. Now at a glance we can see who sold more or less tofu than their supervisor.

Just for reference, this grid uses two sequential SQL statements, more complex than anything we're likely to create on our own:

SELECT [Order Details].Quantity, Employees.EmployeeID, Products.ProductName, Employees.ReportsTo, [Order Details].OrderID, Employees.LastName FROM ((Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) INNER JOIN Products ON [Order Details].ProductID = Products.ProductID WHERE ( ( ( Products.ProductName LIKE "Tofu*" or Products.ProductName LIKE "*[!A-Z0-9]Tofu*" ) ) ) ORDER BY Employees.ReportsTo;

SELECT Sum(elfQ1.Quantity) AS [sum of Quantity], elfQ1.LastName, elfQ1.ReportsTo, elfQ1.EmployeeID, Employees.LastName AS Supervisor FROM elfQ1 INNER JOIN Employees ON elfQ1.ReportsTo = Employees.EmployeeID GROUP BY elfQ1.LastName, elfQ1.ReportsTo, elfQ1.EmployeeID, Employees.LastName ORDER BY elfQ1.ReportsTo;

And yet it was created in just a few seconds using plain English and some common sense to make elementary changes to what the interface could produce by itself.