Using Calculated Fields in Queries

Queries play an important part (in relational database design) in pulling information from different tables together. Consider the Many to Many relationship at the centre of an Order Management System.  Here we have three tables - Orders, Order Details and Products.  When we come to create the record source for the order items section of the invoice report,  it is necessary to get data regarding an order items quantity from the Order Details table, and that of product cost per unit from the Products Table. Then it is the OrderId from the Orders Table which groups all Order Details records into a single order.

Figure 1 (above):  A simplified example of a Many to Many Relationship from an Order Management Database.

Figure 2: A sample Invoice Report taken from one of my Order Management Database designs.
The Order Items Section is located in the middle of the report.  CostPerUnit is
displayed here as Unit Cost and Amount as Total.  This  section contains
details of each individual order item which, taken together,
makes up the whole order.

As well as bringing information together in this way, query's play another important role in as much as they are commonly used to calculate and process information from these tables.  This may be done by means of a Calculated Field.  This is basically a new query column, the values of which being derived from some calculation or expression.  As such, we are effectively creating new data for our database.  So why is this important?  Well, consider the Order Management Database Invoice mentioned earlier.  As already stated, the Order Details table contains data pertaining to an order items quantity, and the Products table to an items Cost Per Unit.  There is nowhere in a well designed ('normalised') database where we store the total of quantity*cost per unit. It is considered good practice to calculate this information (so we avoid storing unnecessary data which uses extra memory, and makes the database less efficient and user friendly).  The Calculated Field used in a query is a common way of doing this.

Creating a calculated field is really quite easy.  Rather selecting a table field on the top row of the query design grid, we manually type the name (or alias), followed by a colon, then followed by the calculation or expression.  So if we wanted to create a calculated field to process quantity * cost per unit, we would use this syntax:

Amount: [Quantity]*[CostPerUnit]

I should point out that the alias used here - ie Amount - is entirely arbitrary text chosen by the database developer. It is, however, good practice to make it something meaningful in order to make it easier for the developer to understand at a later point in time.

Let look at how this fits in with the rest of the query:

Figure 3: Query with Calculated Field on the right.

As you can see the query contains a mixture of fields from all three tables involved in the many to many relationship.  The Calculated Field has been added to the column on the right.  You may have noticed that the calculated field looks slightly more complicated than the syntax I wrote earlier in the post.  This is because we are also specifying the table name as well as the field name.  As such:

[tblOrderDetails].[Quantity] 

... refers to the Quantity field of tblOrderDetails, and:

 [tblProducts].[CostPerUnit] 

... refers to the CostPerUnit field of tblProducts.

It is essential to write the full reference in this way if any tables or sub-queries involved in the query contain duplicated field names.  Although this is not the case in this example, it does no harm to get into the habit of writing the full syntax anyway.

And this is the output from the query showing the result of the calculated field on the right:

Figure 4: The Query Output.

Since this query has an ID field corresponding to tblOrder.OrderId, it can now be used as the record source for the Order Items section of an Invoice Report or Orders Subform.  To do this the LINK MASTER FIELDS property of the subform is set to ID and the LINK CHILD FIELDS is set to OrderId.  There is then the matter of creating the record source for the main section of the invoice report or orders form (this also involves creating Calculated Fields to get the Order Totals).  Unfortunately this goes beyond the scope of this particular post.