Relationships (Custom Analysis window)

The "Relationships from:" panel presents several methods for specifying relationships between tables within your database.

For further information on why and whether pre-defined relationships are necessary for a given View, see the Relations section of the Custom Analysis help text.

The best and simplest way to define relationships is to use the built-in relationship map utility which is an integral part of Microsoft Access. This is accessed using the Tools/Relationships... menu item of MS Access. When relationships are explicitly defined this way, they immediately become visible to Access ELF.

If you prefer to define relationships implicitly, by embedding this information within the queries used by your application, there may be no Relationship map for Access ELF to look at. In this case, you can use the "Master Query" technique for delineating table relations. With this technique, one query is selected to be used as a basis for understanding the relationships for the entire View. Now, it's quite unlikely that one of your existing queries will fit this bill. In other words, very rarely will a single query incorporate all the table-join information that every possible query generated by an interface will rely on. None of the queries which are shipped with Northwind, for instance, include all eight tables.

This means that in order to use the Master query technique, it's usually necessary to create a new query to serve this purpose. In fact, a query that incorporates joins between every table in a database is usually pretty useless for anything except this specific task. We recommend that you name the query "Master" to avoid confusion.

Here's how a Master query for the Northwind database might look:
SELECT Categories.CategoryID FROM Suppliers INNER JOIN (Shippers INNER JOIN ((Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) INNER JOIN ((Employees INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID) ON Shippers.ShipperID = Orders.ShipVia) ON Suppliers.SupplierID = Products.SupplierID;

Notice that the part between SELECT and FROM is irrelevant; it's necessary to pick something simply to make this legal SQL. The point of the query is the information contained in the JOIN clause. (Of course, it's much easier to design a query like this using the Access Query Designer than by writing SQL by hand.)

If you run this query, you'll see that the results are not meaningful, but it is an economical way to represent the interrelationships between tables, without using the Relationship map. Of course, it's also possible to split the job of describing the relations in a view between the map and a master query -- both options can be selected simultaneously.

When your View includes a query which has multiple relationships, be sure these relationships are available to Access ELF, by using either the Relationship Mapper or the Master Query technique. An example of such a multi-relation query is given in the [Discounted Prices] example of the Phrase Usage Primer.