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.

More on DoCmd OpenForm: Control Form Opening Part Two

Last week I blogged about how to Control Form Opening using the DoCmd OpenForm method of VBA.  I covered the DataMode and OpenArgs parameters in some detail.  Since being able to control form opening is an important part of database customization, I shall continue by looking at two other parameters of this method - namely the VIEW and WHERE parameters.

If we recap briefly on what we learnt last week, you may remember that we used DoCmd OpenForm to control whether the form was opened ready to add a new record, or edit an existing record. A label caption was also set according the value passed to the form in the statement's OpenArgs parameter. This time we are going to try something slightly different.  We are going to create a command button, which when pressed by the user, opens that same form in DATASHEET VIEW as well as displaying records matching a given criteria. The end result will be a datasheet list of all employees where the Position field contains the value of "Manager".

Let's remind ourselves of the syntax for DoCmd OpenForm:

DoCmd.OpenForm "FormName", View, "FilterName", "WhereCondition",  DataMode, WindowMode, "OpenArgs"

As you may remember, only the FormName parameter is required, all others being optional.  Just remember to include a comma if any of the parameters are not used (unless they comes after  the last used parameter, in which case they are not needed).  So let's look at the specific syntax we need in order to achieve the task of producing a list of Managers in Datasheet View:

DoCmd.OpenForm "frmEmployees",  acFormDS, , "position = 'manager'"

As you can see, the VIEW parameter has been set to acFormDS.  This value simply indicates that we want the form to open in Datasheet View. When you come to enter this yourself, you will see that intellisense opens a drop down list of valid parameter values:

Figure 1: Intellisense displaying a list of valid parameter values for VIEW.

The next used parameter is the WhereCondition.  This is basically an SQL WHERE clause, but without the  WHERE keyword (see my Introduction to Access SQL for more information about SQL WHERE).  You may also think of this as being like the criteria you enter in the Criteria Row of a Query Design Grid. When used as a OpenForm parameter,  just remember to enclose it within quotation marks, as it is a string value - ie:

"position = 'manager'"

Now that we have grasped the particular syntax for DoCmd OpenForm, we are going to add a command button to the Switchboard form we created last week, and add the following code to its OnClick Event:

Private Sub ctlManagers_Click()
    DoCmd.OpenForm "frmEmployees", acFormDS, , "position = 'manager'"
    DoCmd.Close acForm, Me.Name    
End Sub


NB the DoCmd.Close statement simply closes the switchboard so it does not get in the way once our employees form opens.

Our Switchboard should now look like this:

Figure 2: The new updated switchboard from last week.
The code above it is written behind the ctlManagers button's (top) OnClick Event.

Here is the result when the user clicks the ctlManagers button (captioned "List Managers"):

Figure 3: frmEmployees displayed in Datasheet View with WhereCondition applied.

As you can see, rather than our employees form being displayed in standard Form View, passing the acFormDS as the View parameter has instead opened frmEmployees as a Datasheet similar to a table or query; and out of a list of 22 employees, passing "position = 'manager'" as the  WhereCondition  parameter has provided a list of the two employees who have "Manager" as their Position.  

If you have not already created this database for yourself, you might like to download the completed solution and experiment by changing, amongst others, the WhereCondition, to see the versatility of DoCmd OpenForm for yourself.

Control Form Opening: DataMode, OpenArgs, and the DoCmd.OpenForm Method

Lets imagine we have an employees form to add and edit our employee records.  The form is accessed via the database switchboard which contains a couple of Command Buttons.  The user clicks the NEW EMPLOYEE button and the form opens in DATA ENTRY MODE; that is, as a blank form ready for a new record to be entered.  If, however, he or she clicks the EDIT EMPLOYEE button, the form opens in FORM EDIT MODE; at an actual employee record ready to be edited as required.  What's more, the form contains a label displaying a caption inviting the user to add a new employee, or edit an existing employee record depending on which button was pressed on the switchboard.

Figure1 (above): The Switchboard with Command Buttons
to open frmEmployees.

Figure 2: Form Design for frmEmployees. 
Note the label containing the text "Instructions": this text is
dynamically reset as the form opens.

Since we have learnt some basic VBA programming skills at the end of last year, lets look at how we can do this using the DoCmd.OpenForm method.   As we shall soon see, this method passes a number of parameters to the opening form, determining various aspects of how we want it to function.  DataMode and OpenArgs are the two parameters we shall be using to accomplish the task set out in the above mentioned scenario.

DoCmd.OpenForm"formName",View,"FilterName","WhereCondition",DataMode, WindowMode,"OpenArgs"

Figure 3: The IntelliSense feature of the VBA Editor will
help when you come to enter the DoCmd.OpenForm parameters.

Here is an outline of the tasks involved:

1. Create the Switchboard Form with two Command buttons - ctlNew and ctlEdit.
2. Add code to each of the command buttons which opens frmEmployees in the desired DataMode and with the corresponding label caption for adding or editing.
3. Create frmEmployees along with its underlying data source (ie an employees table).  The form should have a label control (lblInstuctions), and text boxes/combo boxes for each of the fields.  NB for the purposes of this exercise it does not particularly matter what the fields are.
4. Add the relevant code to the OnOpen Event of frmEmployees.  This is going to read the value of the forms OpenArgs Property, which is set when the user clicks one of the two command buttons on the switchboard.  Then, depending on what that value is, display the appropriate caption for lblInstructions.

(Download the complete solution here)

Let's go through stage two and stage four in more detail.  

To begin with lets look at the code used behind each of the two command buttons on frmSwitchboard. These statements are executed behind each command button's OnClick Event: 

Stage Two Code:

Private Sub ctlEdit_Click()

    DoCmd.OpenForm "frmEmployees", , , , acFormEdit, , "Edit"   

End Sub

Private Sub ctlNew_Click()    

    DoCmd.OpenForm "frmEmployees", , , , acFormAdd, , "Add"  

End Sub

As you can see, the DoCmd.OpenForm method has a total of seven parameters, but only the first of these is required (as opposed to optional).  Not suprisingly the required parameter is the name of the form to be opened, which in our case is "frmEmployees".  In addition to the form name we also set the DataMode and OpenArgs parameters.  For our scenario, DataMode determines whether the form is going to be opened in DataEntry (acFormAdd) or Edit (acFormEdit) mode.  It is the fifth parameter in the list. The  OpenArgs parameter, on the other hand, is seventh in the list.  This is simply a string value which will set the opening form's OpenArgs property.  Once the form has opened, we are going to read the value of this property to determine which message we are going to display in the label caption shown in figure two above. As such we are going to pass a string value which indicates whether we are adding or editing the employee record.  Please note that the text of the string value is entirely arbitrary.  We just need to choose a unique value in order to differentiate between adding and editing as the form opens.  

Here is the code we need to enter behind the frmEmployees OnOpen Event:

Stage Four Code:

Private Sub Form_Open(Cancel As Integer)

    If Me.OpenArgs = "Edit" Then

        lblInstructions.Caption = "Please Edit Employee Record"

    ElseIf Me.OpenArgs = "Add" Then

        lblInstructions.Caption = "Please Add New Employee Record"

    End If

End Sub

As you can see this code reads the value stored  in the opening form's OpenArgs property and uses a If ... Then ... Else Statement to determine whether our instructions label should display information pertaining to Editing or Adding an Employee Record.  

Figure 4: frmEmployees for Editing.

Figure 5:  frmEmployees for Adding (DataEntry).

As you can see, the screenshot in Figure 4 shows what our form looks like when it opens for Editing, and that in Figure 5 shows what it looks like for Adding. So despite being exactly the same form, we have dynamically controlled how it looks and functions by setting the DataMode and OpenArgs parameters of the DoCmd.OpenForm method.

Creating a Form for a Many to Many Relationship

I was recently asked how to create a form to input order management data where there is an underlying Many to Many Relationship in place.  There are, of course, many ways of dealing with this common scenario, but the particular solution I suggested involved creating an Order Form, with an Order Details subform.  When the user enters the Order Details data on the subform, he or she is able to select a Product Item from a Combo Box list. The Row Source used for the Combo Box list is data drawn from the Products table.  Hence we have all sides of the Many to Many Relationship represented on a single form.

Figure 1 (Above): The Many to Many Form; the solution I suggested
in answer to dcodding's question.

 Figure 2: The underlying Many to Many
Relationship Structure, upon which the form is based.

It is interesting to note where each of the fields from the Order Form is located in the underlying Many to Many Relationship structure.  So let me briefly take you through this.

The main section of the order form has three fields derived from tblOrder, the left hand table in figure 2 above.  These are the OrderId and OrderDate fields.  In addition to this we also have the CustomerId field which is the foreign key from tblCustomer (a table which is not part of the Many to Many Relationship, therefore not shown in Figure 2).

The relationship between the main section of the Order Form and it's subform is modelled on the One to Many Relationship between tblOrder and tblOrderDetails. However, because we have an underlying Many to Many Relationship, I have created a query for the Subforms' Record Source which has fields from both tblOrderDetails and tblProducts.  Moreover, the second ProductId field is actually a combo box which uses data from tblProducts as its Row Source.

Lets take a closer look at the subform's design and underlying query:

Figure 3 (Above): The Order Details Subform design grid.

Figure 4: The Query used for the Subform's Record Source.

As you can see, ProductId, ProductId, and Quantity are derived from tblOrderDetails, and CostPerUnit is derived from tblProducts.  ProductId is, of course the foreign key from tblProducts. There is also a Calculated Field (Total) which multiplies the CostPerUnit by Quantity to provide the actual purchase price.

So why then do we have two ProductId's?  The reason for this is that the second ProductId does not actually display the Id number per se. Since this field is represented by a Combo Box control on the subform, the data which is displayed can be different to that which it is bound to.  So in order to make the subform more user friendly, the control is set up to display the product's ItemName from tblProducts whilst being bound to the tblOrderDetails.ProductId field. This is done by setting the combo box Control Source property to ProductId,  and its Row Source property to tblProducts. As such, using the Combo Box in this way has enabled the subform to obtain information from the products table on the right hand side of the Many to Many Relationship. For detailed information on how to do work with Combo Boxes in this way, you might like to see my post on Customizing an Access Combo Box.

So what happens when the user comes to enter data in the subform? If  the user knows the productId number for the Order Item in question, he or she will enter that number in the first column of the subform.  Since this is the foreign key for the Products table, doing so will 'bring' any other subform field derived from tblProducts along with it.  In other words once the user enters a productId, data in the CostPerUnit field for that product record is displayed in the appropriate field of the subform.  In addition to this, the ItemName for the product is displayed in the Combo Box control, since its Row Source is bound to ItemName in tblProducts.  If, on the other hand, the user does not know the ProductId, he/she may leave the first field blank, and select a value from the Combo Box drop down list.  Since the Combo Box Control Source is bound to ProductId of tblOrderDetails, and its Row Source, as we know, is bound to ItemName of tblProducts, this is effectively the same as entering the ProductId number directly.  What's more, data from tblProducts is 'brought' over to the subform as before.  Hence, the first productId field of the subform is filled in automatically.

We see, therefore, that this relatively simple and user friendly form not only represents all tables in this Many to Many Relationship, but also provides a practical example of how the Many to Many Relationship works in action.