Access ELF Custom Analysis Window

This window is the starting point for Custom Analysis sessions. Here you can select the tables and queries which will participate in the definition of this interface (View).

You have a number of options on this screen for sorting and displaying the Data Sets which are available for use within this interface. For instance, you can click on the heading of the columns on this display, to sort the record sets in various orders while deciding which should be included; by name, by type (Table/Query) etc. Clicking the header a second time returns the list to its default order, which is alphabetically by name.

To limit the list of candidates to only those you (or the software) have already selected, check the "Show selected only" box. Buttons have been provided to check all ("Select All") or uncheck all ("Select None") for ease-of-use. With "Show selected only" checked, once you have unchecked a table or query, you can cause it to drop out of the display by resorting the list, ie clicking the Data Set column header.

More details on this window:

Exclude fields
Dictionary size
Use field descriptions
Save analysis
Buttons

The remainder of this page documents the "Has relations" columns and the "Relationships from:" panel.

Remember that you can build many different views of a single database, and trigger them at an appropriate point by adding a single line of code. For more details on Views, see the Views: Settings Tab 3 topic.

It's possible to define interfaces on unrelated tables in some cases. Access ELF should still work on unrelated tables assuming that queries won't need to reference more than one table at a time. If the tables have implicit relationships, like the relational tables in Northwind, the keys have to be set via the Edit Relationships window in Access. This allows the necessary joins to be automatically generated. For instance, if you ask "which employees sold beverages", the path from EMPLOYEE table to CATEGORY table requires accessing fields of the ORDERS and ORDER DETAILS tables.

By default, ELF will use the built-in Relationship collection of MS Access to determine how tables are related. Keys and foreign keys, the links tying the tables together, are also read from the Relationship map, providing the details Access ELF needs to build queries which reference interrelated tables.

An alternate way of providing information about the relationships between data sets is to use a "Master Query". In fact, a Master Query be used in place of, or in addition to, the Relationship collection. Any JOINS contained in the Master Query will be added to Access ELF's internal store of acceptable links.

If you don't want to have relationships set in your application, but still expect ELF to make the proper connections, you have two choices. First, you can set the relationships prior to running the Analysis, and delete them afterwards. ELF keeps its own table, so you don't need the Relationships collection at run-time. Second, you can enter relationships directly into the ELF system tables by using the JOINS button on the View Analysis editor. For each relationship, there should be two rows:
Function: JOIN Type: C Arg 1: TableName1 Arg 2: TableName2 Result: KeyField
Function: JOIN Type: C Arg 1: TableName2 Arg 2: TableName1 Result: ForeignKey
The fieldname in the Result column must be a field of the table in the Argument 1 column. In many cases, the KeyField and ForeignKey have the same name, but if you link two tables for instance on fields BIRTHPLACE<->CITY, make sure the fieldnames match the proper table.

However, we strongly recommend that you use either the Relationship map or the Master Query method to define the database relationships for Access ELF.

A refinement added in version 2.0 is the ability to understand triangular relationships, even including those cases where the Key and Foreign Key fields do not match up. "Triangular" describes the situation where Table A relates to Table B which relates to Table C ...which relates back to Table A. Whenever two of the three tables are used in a query, the third is added automatically, and the proper joins will be enforced. While a relatively rare situation, when it's mishandled this can lead to subtly erroneous reports.

As of version 3.0, Access ELF can handle joins between two tables which are connected by up to 5 different fields (though it's extremely rare to have more than two or three fields define a relationship).