Access ELF Query Worksheet Window

The Worksheet response style consolidates many features of Access ELF into a single view. It provides a convenient way of moving between, viewing, and editing the component SQL statements generated by queries requiring multi-part, sequential SQL responses. It also provides the ability to combine datasheet and graph views via a split-screen technique, as well as more precise customization options for the graphs produced than the stand-alone graph style.

The Worksheet response style is selected by clicking the Worksheet option on the Access ELF query form, or on the Responses tab of the Settings window. By default, Access ELF will automatically close the query window when presenting a response in Worksheet view, because this allows an unobstructed view of the Worksheet window. The Worksheet can be used for query input as well, by typing into the query text box at the top. (In addition, the drop-down feature allows you to easily re-select prior queries from the current session.) However, if you prefer to keep the query window open when running queries in Worksheet mode, you can select this by unchecking "Close Query Window on Opening Worksheet" from the Preferences tab of the Settings window.

When you enter a query that explicitly calls for a graph response while in Worksheet mode, the graph will be opened in either full-screen or split-screen view depending on the selection of the Worksheet Graph option, also on the Preferences tab. Another option which affects the Worksheet display is the "Best Fit" checkbox on the Responses tab; selecting this option causes the column widths to be adjusted to an appropriate width for the column's heading and data -- this option applies to both the Datasheet response and the Datasheet view of the Worksheet response.

It's also possible to switch into Worksheet view after running a query in another view; for example, if you type List the customers that have ordered more than 3 products while in Datasheet view, you'll see only the result of the final SQL statement required to answer this question. In this case, it's called elfQ5, since there are actually five sequential SQL statements required. In order to review the logic of each of the five statements in turn, you can click on the Worksheet icon (pages with descending arrow) on the toolbar, and switch into Worksheet view.

This illustrates the main purpose of the Worksheet response style, to act as a coordinating tool to manage the several SQL statements used in answering a single question. Access ELF can generate up to seven SQL statements for a given query; the seven radio buttons at the top left labelled Q1 through Q7 allow you to select, view and edit these statements individually. At the bottom of the Worksheet are text boxes which display both the SQL itself and a general explanation of the function this particular SQL statement plays in the overall process of selecting (or abstracting) the answer.

The Edit button also allows you to view any of these SQL statements in a standard Access QBE grid. This gives you the option of easily making minor changes to selection criteria, or manually adding display fields. You can also continue the chain of SQL queries by clicking the Add button; this will bring up a new query which selects all the fields of the last SQL statement in the chain; it's up to you to take it from there. While it's not permitted to "drop" SQL statements from the middle of such an SQL chain, you can drop the final SQL statement from any chain, working backward if necessary to where the automated process went off the tracks. The SQL box is fully editable, so that if you're comfortable with writing SQL statements directly as text, you have this option as well.

In split-screen mode, it's also possible get graphical views of the component SQL statements, in cases where the components have "graphable" elements. Otherwise (if there's nothing graphable in the newly-selected statement) the current graph is left unchanged.

There are a great number of purely display options which allow you to customize the Worksheet view. The Footer display can be toggled On/Off to give you an expanded view of the grid or graph. In split-screen view, the sizes of the left (data) and right (graph) panels can be customized by dragging the "floating" radio button along its axis, or clicking to its right or left. (The graph automatically resizes to the allotted space.) You can also select the number of rows to display in two ways. Using the "Restrict rows to:" drop-down box above the display changes the SQL by adding a "TOP N" element -- this affects both the datagrid and the graph. You can also change the number of rows graphed ONLY, by using the All...Top 5...Top 50 drop-down in either BPS (Pinnacle/Bytes-Per-Second) or MS (Microsoft) graph styles. Of course, you can type your own choice, such as Top 17, as well.

The Worksheet itself is sizable; that is, you can re-size by dragging its corners in the typical Windows fashion, although some of the controls may not be visible at certain sizes and screen resolutions.

Clicking the Undupe or Chart labels above the Explanation panel gives users the ability to tailor datagrids (by easily removing duplicate data) or graphs (by selecting value and label columns explicitly).

Clicking the header of any column in the datagrid adds that field to the left panel of the Undupe selector. For example, you could use this to select columns which have multiple instances of the same data. Clicking the Remove Dupes toggle button would then rewrite the currently selected SQL statement to display only a single instance of each data value, along with the first corresponding item in any associated column. Resetting the Remove Dupes toggle restores the original SQL.

So for instance, the query List the customers and products generates a list of 1686 customer/product pairs in the Northwind database. If you were interested in seeing a single example of a product ordered by each customer, you could highlight the CustomerID column (selecting the CustomerID into the left panel), then click Remove Dupes. The result is a list of the 89 Northwind customers with the First() domain function applied, generating a single random example of a product purchased by each customer.

Having the response simultaneously available in datasheet and graph views also allows you to precisely specify the values and labels you want used in the graph. In many cases, Access ELF will correctly select these based on the wording of your query, but it's nice to know that you have the chance to make whatever corrections are required.

As an example, say we type in Graph the unit price and reorder level for seafood products from suppliers in the USA. Access ELF decides to graph this by using the SupplierID, ProductName and the supplier's CompanyName as the label for each graphed value. You might not be interested in the individual supplier. In that case, you could clear the selections and click the header for the product name. This adds the ProductName field to the right (label) panel.

Now you have a list of the labels you want Access ELF to use for this graph in the right panel. Since Access ELF correctly chose the proper values to graph, you don't need to explicitly select these, although you also have that option. Clicking the Graph Selected Columns button will now cause the graph to be redrawn, using only the label you've selected.

To remove a field from either panel, double-click it. When you've removed all the listed fields, Access ELF reverts back to its default judgement about which values and labels to graph.

We've designed Access ELF to be compatible with three of the most popular graph engines in use today, Microsoft Graph, Microsoft Chart, and the Pinnacle/BPS Graph control that ships with all versions of Microsoft's Visual Basic. These graph controls have somewhat different virtues and different customization features.

The Microsoft control can be fully customized by double-clicking on the graph itself. This takes you right into the Microsoft Graph program, with all of its many customization options. For instance, you can select bar or pie charts, or even the doughnut, cylinder, pyramid styles etc. You can move, resize or select fonts for the legends and labels. To exit MS Graph, click on the Worksheet -- this takes you back into Microsoft Access. (Do not select either Close or Exit from the Graph menu, as this will Close the Worksheet or Exit Microsoft Access entirely.)

In addition to the built-in customization options, we provide several graph-specific options which in effect change the SQL statement being graphed. The Top N and Sort drop-down boxes let you specify a maximum number of rows to graph, and permit Ascending, Descending, A-Z and Z-A sort orders. The Filter box lets you specify a condition to apply, to limit the number of rows displayed. Several examples are provided, including V>0 and L Like "[A-Z]*". Instead of requiring you to type a field name, Access ELF accepts references to either "V" (for value) or "L" (for label). So for instance the first example limits the rows displayed to those that have graphed values greater than 0; the second example limits the rows displayed to those whose labels begin with an alphabetic character. If more than one value is graphed, you can use V2, V3 etc. to refer to these values in a filter condition. There is never more than a single label per row; rows which are labelled with multiple fields (such as the ProductName and SupplierID example above) are actually single, concatanated labels.

The Scale Font checkbox determines how the fonts displayed on the Microsoft graphs will display when they are resized. Scaling fonts can result in displays that are too large or too small depending on the graph size selected. Similarly, the Clip, Scale and Zoom selections determine how the graph image will be scaled (or cropped) during resize operations. "Scale" is the default, and resizes the image to both vertical and horizontal dimensions.

The Microsoft Chart control also has many customization features, documented in the What's This pages.

Because the Pinnacle-BPS graph isn't interactive, we've provided a more complete set of options to programmatically change display characteristics of the BPS graph. In addition to the Top N, Sort and Filter selections, you can also select the Graph Type (eg Bar, Area, Pie etc) and Graph Style (Horizontal, Vertical). Note that Z-Clustered graph styles are meaningful only for graphs which have more than a single numeric value to graph; in effect, these are 3-D graphs. As such, they're most effective for the crosstab graphs which are produced by queries beginning "Compare the sum/average/count..." etc.

Also only enabled when working with graphs that have multiple value fields is the Legend Filter drop-down box. This drop-down also has two built-in examples, [A-Z]* and [0-9]*. When there is more than one value being graphed, the Legend is a supplemental display (rather than a toggle between displaying the labels along the axis or in a separate box). In this case, you can choose to look at a cross-section of the data by filtering on the legend; the two examples display sections whose legends begin with, in one case, alphabetic characters or, in the other, numerals. A more common use of this option would be to display a 3D crosstab graph section by section, using [A-M]*, [N-Z]* as one simple example. Note that the pattern portion of the filter ONLY should be used in the Legend filter drop-down box.

For single-valued graphs, the Legend checkbox switches between axis labels and a legend display. For multi-value graphs, the legend checkbox toggles the legend on/off and does not change the label display.

The Zoom checkbox changes the scale of the graphed display from the default 0 through highest value, to a display scaled from the lowest to the highest values. If the lowest value is in the "relative range" of zero, checking this box may not have any effect on the display.

In addition, the More Graph Options window provides a method for exporting an image of the graph to the printer, to a file, or to the Windows clipboard, where it can be transferred to other programs. The Export types are .WMF or .BMP format. The .JPG format, which is required for Web page displays, is also supported, but requires a special, downloadable version of the Access ELF library file ACCELF.DLL. Please consult ELF Software if you need to export graph images for use with HTML documents.

With the Organizational Chart link, you can display hierarchies in the Worksheet, once you've used the Verb Mapper to let ELF know about hierarchical relationships. These relationships include organizational structure (who governs who) or parts-dependencies. To activate this feature, add an entry to the Verb Mapper by using the Add New record button (arrow and star). Choose the same table on both sides of the relationship. Click a field on the left, for instance [Employee ID], and a field on the right, for instance, [Reports To].

Now you can use the Org Chart link on the Worksheet to get a tree display. (Or use "as a hierarchy" in your question, to display records in hierarchical order.) Once you've generated the tree, it can also be displayed in triple-view mode, split-screen with the grid and graph simultaneously.

Let's say you'd like to add a field into the tree display, for instance to display each employee with their own birthday . To do this, ask a question that includes the employees and their birthdates, such as "Show the employees and their birthdates." Now click the Org Chart link and use the controls on the Select Aggregate Operator window. Click the column you want to display, then choose the "First" option from the list of aggregate operators. Since everyone is considered the "first" node in their own sub-tree, this will show the selected field (BirthDate) for each employee.

Other uses for this type of operation would be to see the most expensive part, along each branch of a component subassembly (parts explosion) database. Or the next event in each branch of database linking related events. (Usually the date precedence would be built into the hierarchy; in other words, events depend on (or are "ruled" by) the event that must precede them. So here we've got a quick way of validating such a hierarchy, to make absolutely sure that no event depends on one that happens after it.)