"Tables used in query joins forms natural data relationships adding depth to reports. Let's get connected!.."

Articles on methodologies, designing and planning that database can be found by reviewing various articles starting with the database normalisation process which guides you through what rules should be considered in order to reach that true relational database!

Then take a look at some planning techniques and reverse engineering processes to keep to the key points and the focus of your database design.

The final article as a pre-requisite reference to this article is to understand the relationships between tables/queries and the types of links available so you know how query joins will behave.

A query can be based on a single table or another query but you only have the fields from the one source file available and restrict how you report. You could use functions to calculate links to other tables or queries but it's clumsy, puts a large memory overhead (performance issues) in that report and is generally frowned upon!

By using query joins properly, it will provide a far more powerful way to report and the following examples help to illustrate how to create a join and change the properties where applicable.

It will impact the results (recordset) if a 'stand-alone' source file is detected. Therefore, the golden rule here is to always make a join across all tables/queries.

Note: There a few instances where a 'stand-alone' table or query can be considered but is generally due to a design flaw or handling some work-around for the more advanced query. I will cover an example at the end of this article.

The example scenario here (to illustrate a join) is that we have Customers (table 1) who place one or more Orders (table 2) which has a relationship link between them referred to as the Customer ID field. Each order can contain one or more items/products from the Order Details (table 3) and is related to the 'Orders' table by a common field (Order ID). Finally, we have a list of Products (table 4) which is used more than once across one or more orders and is linked to the 'Order Details' table by a common field (Product ID).

The report that we are after wants to show all 'UK' based customers who ordered products in 2008 showing each product ordered as a summary in both the number of units and revenue ordered. This will land up being a groups and totals query to summarise the quantity and revenue for each product and customer.

All tables and queries must have a common field between the source files that will be joined. This may require additional tables or queries even if it's not going to be used directly in the query but still sits in the background as an interceding table/query join.

Step 2: Confirm the joins between the source files and either connect them up if not joined or modify the join if it is the wrong relationship (though this should not really be the case if your database is well designed!).

To join between two fields, single click and drag 'n' drop to the corresponding matching field in the other table or query. This creates a relationship.

If you need to modify a join or create new one, either delete the original join place placing the tip of the mouse pointer anywhere on the join to highlight and then press the DEL/Delete key from the keyboard or double-click anywhere on the join and view the Join Properties screen.

The fields for this example are:

Back in 'Design View' mode, set any sorting and move onto the next step to set criteria.

Step 4: Set the criteria for each required field(s) and keep checking the recordset is producing the correct logical results.

In this case, we would like to locate only the 'UK' customers for all orders issued in 2008. Therefore, two fields are required; 'Country' and 'Order Date'.

In this new row, I need to change the following correctly in order to display the correct output:

Note: The Where clause is automatically hidden and can not be displayed as part of the output. To learn more about this type of query, please review Groups and Totals queries.

Any 'stand-alone' table/quriy acts as a product (a multiple of) against another table/query (i.e. all records is table A is multiplied by all the records in table B).

A query generally does not display both detail and summary output layouts together (you tend to rely on a report or a form!) unless you relax the joining rules - in other words, have no joins!

This next example requires a temporary table to cause a duplicate effect as we would like to list all customers’ revenue by product for orders issued in 2008 and also have a product total value (revenue and quantity) too as a separate record mixed within the details.

You therefore, need to create a simple temporary table for this query join with a field and two records (one for each output layout - detail versus summary).

I then modified the 'Company Name' field to an expression using the IIF function to test if the temp value is equal to 1 and set it as the field 'Company Name' otherwise show the narrative as "zz Total of Product" (because the value is equal to 2).

Note: Notice the narrative starts with a couple of "zz" characters so that the order of these fields is grouped together and at the end of the recordset - it has no other benefit.

The expression modified for the 'Company Name' field now looks like:

CompanyName:IIf([TempField]=1,[Company Name],"zz Total of Product")