Using Conditional Formatting on an Exam Results Report

Conditional Formatting may be applied to Text Box and Combo Box controls on Access Forms and Reports.   It allows us to format the data contained in a particular Text or Combo Box, according to value of that same data.  For example we can create Conditional Formatting for a text box to display a currency value, which when less than 0, will display that value in a red font, instead of the normal black.

Moreover, as well as formatting the Text or Combo Box according to the value contained in that same control, we can also format the Text or Combo box according to the value contained in another field of the same record.  So, for example, we can display a Text Box containing a CompanyName in bold font if the value of its AnnualTurnover field is greater than or equal to, say,  £100,000.  The great thing is, the data contained in the AnnualTurnover field does not necessarily have to be displayed on the form or report, as long as it is stored in the form or reports underlying Record Source.  In order to do this, we would enter an expression for the condition.

So let's take a look at how we go about applying Conditional Formatting using the example of a Student Exam Results Report.  The report applies conditional formatting based on the students exam result.  Students records with a result above 74 % are displayed in green, and those below 40 %, in red. The formatting for those in between is set to blue by default. To enhance clarity, I have also applied a sort on the Results field, thereby ordering the records from highest result to lowest. If you wish, you can download the complete solution by clicking the link below:

Student Exam Results

Our example database contains a table, tblExamResults, and report, rptExamResults- the table being the Record Source for the report.  Our table contains a list of student names and their corresponding exam results:

Figure 1: tblExamResults - The Record Source for our Report.

The rptExamResults report is based on this table.  I created it by clicking the REPORT icon (located in the REPORTS group of the CREATE ribbon) whilst tblExamResults was highlighted in the NAVIGATION PANE.  I then just needed to tidy it up in DESIGN VIEW, and add the Conditional Formatting (on each text box on the report) in addition to a SORT on the Results field.  This is the finished result:

Applying Conditional Formatting.

To begin with, lets look at how we apply Conditional Formatting to the Result field of rptExamResults.

1. Open the report in DESIGN VIEW
2. Right click the Result Text Box.
3. Click CONDITIONAL FORMATTING from the drop down menu.
4. When the CONDITIONAL FORMATTING dialog form opens, set the conditions as described in the next stage of the process below.

Figure 2: The Conditional Formatting dialog form.  It is possible to have a maximum of
three different conditions plus the default formatting.  

Setting Conditions based on FIELD VALUE IS

The next stage is to set the actual conditions for the Result field of the report (see Figure 2 above). NB: when FIELD VALUE IS is selected, it refers to the value contained in the text box being formatted:

1. First set the DEFAULT FORMATTING section so it displays a BLUE FONT.  Do this by selecting BLUE from the FONT/FORE COLOR icon
2. Next set CONDITION 1 so that a BOLD GREEN FONT displays when the  FIELD VALUE IS GREATER THAN 74.  If necessary click the drop down list to select FIELD VALUE IS, and then do the same in the next combo box along to select GREATER THAN.  Then just enter the value 74 in the third box along, and select the BOLD and RED icons.  You should see a preview in the box below when done.
3. Click the ADD >> button to show CONDITION 2.
4. Repeat the process of stage 2 so that a BOLD RED FONT displays when FIELD VALUE IS LESS THAN 40.
5. Click OK to close.

Setting Conditions based on EXPRESSION IS

The next stage is to set the conditions for the StudentId field.  Since we are not basing the condition on the value contained in the StudentId field itself, we need to construct an expression which refers to the value contained in the Results field. As such, we shall be selecting EXPRESSION IS from the appropriate drop down lists, rather than FIELD VALUE IS.

1. Again, set the DEFAULT FORMATTING section so it displays a BLUE FONT.  Do this by selecting BLUE from the FONT/FORE COLOR icon
2. Next set CONDITION 1 so that a BOLD GREEN FONT displays when the EXPRESSION IS [Results]>=75.  Do this by clicking the drop down list to select EXPRESSION IS, and then enter the expression in the elongated box (see Figure 3 below).  Then select the BOLD and RED icons as before.  
3. Click the ADD >> button to show CONDITION 2.
4. Repeat the process of stage 2 so that a BOLD RED FONT displays when EXPRESSION IS [Results]<40
5. Click OK to close.

Figure 3: Condition based on EXPRESSION IS.  Note the elongated box where the expression is entered.  This appears when EXPRESSION IS is selected from the drop down list.

Finish the report by repeating this last stage for the FirstName and Surname fields using the EXPRESSION IS selection when creating the conditions.

Creating an Update Query to Update a Salaries and Wages Database

Imagine you have responsibility for the management of a Company's Salaries and Wages database.  The company employs 200 colleagues, and it is your job to process the annual pay increase. The problem you face is that different groups of employees receive a different percentage rate increase; and each employee within a group may have a different salary level to start with.  So what is an efficient way to update each employee's salary without having to having to go through each record manually?  A good solution would be to use an Update Query.

Update Queries are a type of Action Query, similar in nature to Delete Queries, which I blogged about last month.  However, whereas a Delete Query will find a group of records matching a given criteria and delete them from the table, the Update Query will find a group of records matching a given criteria and update one or more fields in each of those records.  So for example, we can use an Update Query to update the salaries of all staff with the job title, Telesales Administrator.

The great thing about using an update query is that we can update a field to an absolute value or a value based on a calculation.  So in addition to being able to update all Telesales Administrator salaries to a fixed value such as, say, £15000, we could also update all existing Telesales Administrator salaries by, say, 5%.  So in a scenario where each Telesales Administrator is on a different salary level to begin with, it makes sense to do the latter to update each unique salary figure.

Please feel free to download the completed solution to the above scenario by clicking this link: Example Update Query.  I recommend you begin by opening tblEmployees and take a look at the 12 sample records and then see how the records for Telesales employees change after the qryUpdateTelesalesSalaries Update Query is run.  You can also follow the instructions below to find out how to create this Update Query yourself.

Figure 1: tblEmployees.  As you can see, there are 6  Telesales Employees.
Notice how their salaries change after the Update Query is run.  

Creating an Update Query

So how do we create this Update Query?  It is easy.  We do it in two stages.  First of all we create a standard Select Query which uses a criteria to find the group of employee records who have Telesales as their job title.  Then we convert the query to an Update Query, and in the process, we enter the calculation which updates the existing salary level for each Telesales Administrator.   Lets do this step by step:

Stage One - Testing the Criteria with a Select Query

1. Click the QUERY DESIGN icon (located on the QUERIES group of the CREATE ribbon).
2. Select the tlbEmployees table from the SHOW TABLES dialog form.
3. Double click the JobTitle and Salary fields from the employees table, so they appear as columns on the Query Design Grid.
4. Enter ="Telesales" in the criteria row of the JobTitle column.
5. Click the RUN icon to run the query.

The results of the query are shown in the screenshot below:

Figure 2: Results of the Select Query
in stage one.

As you can see, the query has found all six records where the employee's job title is Telesales.  We could have included their names and employee id's in the results, but this is not necessary.  Once we convert it to an Update Query and click run, we won't actually see the query results until we open the underlying table and note the updated fields in each of the records matching the criteria.

Stage Two - Converting to an Update Query

1. Re-open the Select Query in Design View.  If the Select Query is already open in DATASHEET VIEW, simply click the DESIGN VIEW icon from the VIEWS group of the HOME ribbon.  Otherwise you can highlight the Query name in the NAVIGATION PANE, right click the mouse, and then click the DESIGN VIEW icon from the drop down menu.
2. Once the Query opens in DESIGN VIEW, click the UPDATE QUERY icon from the QUERY TYPE group of the DESIGN ribbon. The Icon is then highlighted in orange. 
   You will also notice that the SHOW and SORT rows of the QUERY DESIGN GRID have disappeared, being replaced by a new row called UPDATE.
3. All we need to do now is enter the calculation into the UPDATE row of the Salary column.  The calculation to enter is:  [salary]*1.05
   
   

   

   Figure 3: This is what the Query Design Grid should
   now look like for the Update Query.
4. Click the RUN icon from the RESULTS group of the DESIGN ribbon.  
5. Click YES when you  receive a warning message saying "you are about to update 6 row(s)".

Once you have run the query and clicked YES to the warning message, you won't see the changes until you re-open (or refresh) the underlying tblEmployees table.  When you do so, you will see that the Salaries for Telesales staff have been updated by 5% as expected (see figure 4 below).

Figure 4: The Updated tblEmployees Table.

Using The Tab Control for Tidy Form Presentation

The Tab Control is a type of form control used to aid presentation and enhance user-friendliness.  It works much the same way as the Access Ribbon - the user clicks a tab to reveal a new strip.  However, instead of Ribbon icons, these strips contain our text boxes or other form control that we choose.  As such, it allows us to separate the fields on our forms, displaying one group at a time. Here is a screen shot of one I prepared earlier:

Figure 1a: The Tab Control: Tab 1 "Personal".

Figure 1b: The Tab Control: Tab 2 "Address". 

Figure 1c: The Tab Control: Tab 3 "Education".

As you can see, the Employee Record Form in figure 1 above contains a Tab Control which is comprised of three separate Tabs.  The first tab shown in figure 1a has been given the caption, "Personal", and displays fields relating to the Employee's ID, Name and Date of Birth. The second tab in 1b relates to the employee's Address, and the third in 1c relates to Education.  As such, we see how the Tab Control has enabled us to organize the information into logical categories, in addition to presenting that information in a tidy and easy to access format.  In fact,we have condensed 11 fields to fit on a small Dialog form.

Lets take a look at how the form from figure 1 was created. 

Adding a Tab Control

1. To begin with I created a new form by clicking the FORM DESIGN icon.  This is located in the FORMS group of the CREATE ribbon.
2. I set the RECORD SOURCE property (via the DATA tab of the PROPERTY SHEET) to the appropriate table.
3. I clicked the TAB CONTROL icon  (located in the CONTROLS group of the DESIGN ribbon), and clicked a position for it to go on the form design grid. A blank Tab Control with two pages appears on the grid.

   

   Figure 2: A blank Tab Control added to the
    Form Design Grid.

Customizing the Tab Control

The  process of customizing the Tab Control simply involved adding an extra tab page and giving each tab its own individual name - ie Personal, Address and Education.  

1. I began by selecting the Tab Control by clicking its outer edge so that it was highlighted. 
2. Then I clicked the INSERT PAGE icon  (located in the CONTROLS group of the DESIGN ribbon).  This added a new blank page to the tab control, thereby giving us the three required tabs.
3. Next I clicked the part of the Tab Control which says PAGE1 to select this particular tab page. 
4. I then changed the CAPTION property for PAGE1 to "'Personal".  This property is located on the FORMAT TAB of the PROPERTY SHEET.
5. I repeated the process for the other two tab pages -  so I had a tabs for  "Personal", "Address" and "Education" respectively.

Adding Text boxes to the Tab Control

The final stage involved adding the text boxes to the tab control.  The key thing to remember here is that it is important to select the required tab page before adding the text box to the tab control.  It is also worth mentioning that if you are moving an existing form control to the tab control, you will need to cut and paste rather than simply drag and drop (otherwise it will sit on top of the tab control and not be a part of it).

1. I began by clicking the ADD EXISTING FIELDS icon (located in the TOOLS group of the DESIGN RIBBON).  This opened the FIELD LIST pane.
2. I then selected the Personal Tab of the tab control (like I did in stage three of the customization process above).  
3. I then dragged the first four fields - EmployeeId, Firstname, Surname and DOB - from the pane over to the tab control and positioned them in the required location. When you try this yourself, notice how the tab control goes black as you position the text box. This shows that it is being added to the Tab Control rather than the form Detail.
4. I repeated stages 2 and 3, adding the Address and Education related fields to the Address and Education tabs respectively.  
5. All I had to do then was align and resize the text boxes for a professional finish.  A good way of doing this is to click on the text box and manually enter the grid position on the FORMAT tab of the PROPERTY SHEET - the TOP property determines the vertical grid position, the LEFT property the horizontal, and the WIDTH is self explanatory. You can also position the text box label's separately using the same method. 

Figure 3: Adding fields to the Personal Tab Page.

Filtering Report Results Using the Filter Property

In this tip we are going to filter the results of a Report to display records with order amounts above a given value.  In so doing we are going to pay special attention to two Report properties: these are FILTER, and FILTER ON LOAD.

Our solution works when the user opens a Dialog Form containing a text box and command button.  The user enters an amount value in the text box, and clicks the command button.  The command button then runs a small amount of VBA code to open the report.  As the report loads, it applies a filter defined in the Report's Filter Property to produce the desired result.

Figure 1: Dialog Form to collect the Amount Parameter.

So how does this work?

When the Report opens, Access checks the report's FILTER ON LOAD property (which we have set to YES). When it detects the property is set to YES, this tells Access to apply the report's Filter (which we have entered into the report's FILTER property). Our filter then references the users parameter value from the Dialog Form text box, and filters all records with Order Amounts greater than or equal to that value.

You can download the completed solution from here: Filter Property Tip Database. You may also want to work your way through the instructions below.

The Table

The solution contains a Table (tblOrders), a Dialog Form (frmParameter) and a Report (rptOrders).  In the screenshot, below you will see the data we shall be working with in tblOrders:

Figure 2: tblOrders.

As you can see the table is made up of four fields: OrderId (AutoNumber), OrderDate (Date/Time), Customer (Text), and Total (Currency).  

The Dialog Form

We then create the dialog form shown in Figure 1 above (there is more information about Dialog Forms in my last post).  I have referred to the textbox as txtAmount, the control button as cmdOpenReport, and the form itself as frmParameter.

As I have already mentioned, the command button runs a small amount of VBA Code when clicked by the user.  To add this code:

1. Select the command button on the form design grid, and click the PROPERTY SHEET icon (located in the TOOLS group of the DESIGN ribbon).  
2. When the PROPERTY SHEET opens, click the EVENT TAB.  
3. Then select the ON CLICK event by clicking into that particular cell of the grid.  
4. A three dot  symbol appears at the right edge of the cell. Click it and select CODE BUILDER from the CHOOSE BUILDER dialog box which has opened.  
5. Then click the OK button and the VBA editor opens.

Figure 3: The On Click Event on
the PROPERTIES SHEET.

You can can copy the code below and paste it onto the editor.  The top and bottom lines that begin Private Sub... and End Sub respectively should have appeared already, so you just need to copy the middle section in between:

---

Private Sub cmdOpenReport_Click()

On Error GoTo MyError

    DoCmd.OpenReport "rptOrdersFiltered", acViewReport

    DoCmd.Close acForm, Me.Name

Leave:

    Exit Sub

    

MyError:

    MsgBox "Error " &  Err.Number &  ": " &  Error$

    Resume Leave

End Sub

---

The two important lines in this section of code begin with DoCmd (meaning do command).  The first of these is the command to open the form in Report View.  The second closes the Dialog form once the report has opened.  NB it is very important the report opens before  the Dialog Form closes, because it needs to reference the value contained in the Dialog Form's Textbox as the it loads.  

The Report

The quickest way to create the Report is to base it on our table.

1. Select tblOrders in the Navigation Pane.  It will highlight in orange.
2. Then select  the CREATE ribbon , and click the REPORT icon (located in the REPORTS GROUP). 

This creates a basic report which we can then then modify in DESIGN VIEW as required. 

Figure 4: Report Design.

Setting the Filter Properties

Next we turn our attention to setting the Report's Filter Properties accessed via its Property sheet.

1. Click the Square Box in the top right hand corner of the Report Design Grid.  This ensures we are going to be working the properties of the actual Form (rather than one of its controls).
2. Click on the PROPERTY SHEET icon (located in the TOOLS group of the DESIGN ribbon) whilst the Report is open in Design View.
3. Click the DATA TAB of the pane.
4. Make sure the SELECTION TYPE is set to REPORT.  This should be the case if you carried out step 1 successfully.  Otherwise just change the selection to Form in the drop down list.
5. Enter the following into the FILTER property cell: Total >= forms![frmParameter]![txtAmount] .  This has the effect of filtering out report records where the amount value in the Total field is greater or equal to that entered by the user in the Dialog Form's textbox.
6. Then set the FILTER ON LOAD PROPERTY to YES.  This ensures the filter is applied when the forms loads.
7. Save the Report as rptOrdersFiltered.
8. Close the Report

Figure 5: The Form's Filter Properties.  Note the reference to the textbox
on our Dialog Form in the FILTER property cell.

The solution should now be ready to run.  Just click the frmParameter form in the Navigation Pane to begin.  The screenshot below shows what the report results look like when £50,000 is entered by the user as the amount parameter: 

Figure 6: Report Results filtered for amounts greater than or equal to £50,000.

Creating a Dialog Form

Dialog Forms are an integral part of most customized Access Database Applications.  As developers, we set them up to enable our applications to communicate and interact with users.  The simplest sort of dialog box is one which displays a message.  These may be created using VBA's MsgBox Function or the MsgBox Macro Action.   Others involve a greater degree of customization, especially when the application requires the user to provide information such as parameters for a query.  One way to do is to create a standard Access form and modify it's properties to make it into a Dialog Form.

Figure 1: This Dialog Form obtains date parameters used to restrict the
records displayed in a report.  You may remember this was NorAzri's chosen
method for filtering report results mentioned in my last post.

To create a Dialog Form such as that displayed in figure one, we begin by creating an unbound form.  This is done by clicking the FORM DESIGN icon from the FORMS group of the CREATE ribbon.  The term unbound means that the form is not bound to a particular Table as with conventional forms.  The information it is designed to collect is temporary, and does not need to be stored.

When a new form opens in design view, the screen will show a blank grid in the main window, and a variety of  form controls in the CONTROLS group of the DESIGN ribbon.  The Dialog Form in figure 1 uses four Combo Box Controls and a Command Button.  The values displayed in the Combo Boxes are defined in the Row Source Property.  I recently blogged about Customizing an Access Combo Box.  If you are interested, I recommend you check it out by following the link above (just remember that the combo boxes in this example do not need a Control Source because we do not need to store it's data in a table).

However in this post we are mainly interested in the process of changing the appearance and action of an ordinary form to that of a Dialog Form.  So how is this done?  The answer to this lies in a number of key form properties.  The two most important properties to understand are the POP UP and MODAL properties.

The POP UP property ensures that when the dialog form is opened, it appears as a pop up box rather than a full size form. Otherwise it would just appear as an ordinary form rather than dialog form.  As such this property should always be set to YES.  Setting the MODAL property to YES ensures the user cannot ignore the dialog form by clicking on a ribbon icon or form control, for example.  The idea here is that your application is waiting for a user response. Access will not do anything until that response is given, and/or the dialog form is closed.  These properties are located on the OTHER TAB of the PROPERTY SHEET.

Figure 2: Set the POP UP and MODAL properties to YES.  This is done
 from the OTHER TAB of the PROPERTY SHEET.  The property
sheet is accessed by clicking the PROPERTY SHEET icon
on the TOOLS group of the DESIGN ribbon.

NB When you change these properties make sure the SELECTION TYPE of the PROPERTY SHEET is set to FORM.  If it is not, just click the drop down list at the top of the sheet and change it. Alternatively click the square box at the top left hand corner of the DESIGN GRID. 

If you look again at the dialog form in figure1 you will notice there are no navigation buttons or record selectors which forms normally have by default.  To remove these just go to the FORMAT TAB of the PROPERTY SHEET and change the NAVIGATION BUTTONS and RECORD SELECTORS properties to NO.

You may also notice that our dialog form has a border style specific to dialog forms.  One of its features is that the user cannot resize it, thereby diminishing its impact!  This is attained by changing the BORDER STYLE property to DIALOG.

One of the more subtle properties that you might want to change is that of the SCROLL BARS property.  I recommend you set this to NEITHER.  Apart from the fact you should not need to scroll a dialog form, Access also leaves a thick white line at the bottom of the form if this is not changed.  This looks particularly bad if you choose a different Back Color for your form.

One last property alteration that I recommend, is the addition of a form Caption.  This appears in the top border of the dialog form.  In figure 1 I have set this to "Please Enter a Date Range".  The caption does not have to be a question, generally it is just a form title.  Either way you will need to choose your own caption and enter it in the CAPTION property, the first property on the FORMAT TAB.

As far as the actual function of the dialog form is concerned, this particular example runs a VBA sub when the command button is clicked.  The code creates an SQL WHERE statement based on information obtained from data entered by the user on the dialog form.  This is part of the code that I used:


varYearFrom = Me!cboYearFrom
varYearTo = Me!cboYearTo
varMonthFrom = Me!cboMonthFrom
varMonthTo = Me!cboMonthTo

strSQL = "(Year between " & varYearFrom & " AND " & varYearTo & ") AND (Month between " & varMonthFrom & " AND " & varMonthTo & ")"


As you can see, each combo box value is stored in a variable in the first four lines of code.  These variables are then integrated into the string containing the SQL Statement. Once we have the SQL Statement stored in the string strSQL, this can be passed as the WHERE parameter of the DoCmd.OpenReport Method like this:


DoCmd.OpenReport varForm, acViewReport, , strSQL


Then, when our report opens, only records falling within the criteria set in the SQL WHERE statement are displayed. In this case these are all the records between January and December 2011 (see figure 1 above).

Manipulating Dates Using the DatePart Function

I was recently asked a question on my Access 2007 Tutorial Facebook Page which involved the manipulation of dates.  In my answer to this question, I suggested a solution that made use of the DatePart Function.  If you are interested, I was replying to NorAzri's comment on his post of the 5th Sep 2011.  However, manipulating dates in this way is quite an interesting area, so I thought I would use this blog to write a bit more about the function.

So what does DatePart actually do?  Put simply, the DatePart Function allows us to isolate part of a given date.  In so doing we may then go on to group or retrieve records according to the date interval set . We use the function by passing an interval parameter telling Access which part of the date we are interested in, and a second parameter which is the date itself.  The syntax for the function is constructed as follows:

DatePart("Interval", DateValue)

The interval parameter is comprised of the same values as that used in the related DateDiff Function - a function I blogged about in August.  These intervals may pertain to the Year ("yyyy"), the Quarter ("q"), or the month ("m") to name but three. There are ten possible interval types in total (going right down to hours, minutes, and seconds).

However, NorAzri was interested in filtering his records by month.  So in order to do this we passed "m" for the interval parameter, and fldDate (a field value from the table) for the date parameter.  The syntax for this was written as follows:

DatePart("m", [fldDate])

The result of the function gave us a numeric month value based on the full date.  For example, if the date in fldDate had been 16/09/2011, the function would have returned the value 9. This is useful when we have a list of dates, such as that in Figure 1 below, which we want to group by month value in a query or report.

Figure 1:  List of Orders with dates.

So lets take a look at how we would use the DatePart Function in a query to group these order records by month:

Figure 2

As you can see in Figure 2 above, we have created a calculated field called TheMonth using the DatePart Function.  The interval, "m", has been passed in the first parameter, and the second parameter references the OrderDate field from tblOrders.  We have also clicked the TOTALS icon from the SHOW/HIDE group of the DESIGN ribbon, setting TheMonth and Total fields to GROUP BY and SUM respectively.

When we run the Query, any month containing an order will be represented in the results as its own individual row.  The first column then displays the Month value derived from the DatePart function in TheMonth, and the second contains the sum of order totals for the particular month in question.  Figure 3 below shows the query result for our sample data:

Figure 3: Results of Query with Calculated
Field using the DatePart Function.  SumOfTotal
gives us the total for each group of order records
falling within each Month.

Another excellent use of DatePart would be to filter a group of records using a criteria based on this function.  For example, if we wanted to show each individual record for all orders falling within the month of May, we would construct our Query as follows:

Figure 4

As you can see, we have kept our calculated field called TheMonth.  However, instead of grouping all the records from tblOrders by month, we have added the month value =5 as a criteria for this column.  When we run the query, all Orders from May will be displayed.

Figure 5: The result of the second Query filters
out all orders from May.

If we wanted, we could convert this to a parameter query, and use it as the data source for a report.  This was something I recommended to NorAzri who wanted to select the Month from a Combo Box on an unbound form, and then produce a report which would filter out records falling within the month in question.  If you are interested in seeing the solution I suggested, just follow the link at the top of this post and look down the stream.

How to Display a Form Automatically when your Application Opens

This is a quick tip on how to automatically display a form when the user opens your Access Application.  In addition to improving User Friendliness, your database design will also appear much more professional.  The step by step instructions below will display the Switchboard form in figure 1 immediately upon the application opening.  This will give them a good start point from which to navigate your system.

Figure 1: Switchboard Form opens Automatically
when the Application Opens.

1. Open the Database and create a Switchboard Form similar to that in figure 1 above.  Call the form frmSwitchboard.
2. Click the MICROSOFT OFFICE button in the top right hand corner of the Access Screen.  
   
3. Click the ACCESS OPTIONS button at the bottom of the open pane.  This opens the ACCESS  OPTIONS dialogue form.
4. Select CURRENT DATABASE from the menu on the left side of the dialogue form.  This displays the Options for the Current Database (see figure 2 below).

   

   Figure 2: Options for the Current Database.

5. The fourth option down from the top is DISPLAY FORM.  Click the Combo Box arrow and select frmSwitchboard from its drop down list, 
6. Click OK to close the ACCESS OPTIONS form.
7. Close the database.

The next time you open your application, the Swichboard is displayed automatically without the user needing to select it from the Navigation Pane.

Removing Multiple Records with a Delete Query

Imagine you have a contact database containing a list of suppliers.  One of those suppliers proved themselves unreliable once to often, so your company decides not to use them any more.  It is your job to delete any names from this list that belong to this particular supplier. The only problem is, the contact database contains hundreds of names, a number of which are from the suppliers company. This is where an Access Delete Query is going to prove very useful.

Delete Queries are a type of Action Query ie a Query which performs some sort of action on our database.  In the case of a Delete Query, Access will find a group of records matching a given criteria and delete them from the database table.  As such, we can make good use of a Delete Query in the scenario outlined above.  By means of a Delete Query, we can find all the records of staff who work for the ex-supplier and delete them from the contact list.  Lets take a look at the contact list we are going to working with (shortened for the purpose of this exercise):

Figure 1: The Contact List upon which
we shall run our Delete Query.

Lets say the name of our ex-supplier is Company 5.

So how do we create a Delete Query?

It's quite simple really.  The first stage is to create an initial select query that would give us a list of all the names of staff working for Company 5.  This involves entering "Company 5" as the query criteria. Once we have run the query and checked its results, we just need to change it to a Delete Query and click RUN again.

How to Create a Delete Query

Stage 1 - Creating the initial Select Query

1. Select the CREATE TAB of the Access Ribbon.
2. Click the QUERY DESIGN icon.  It is located in the OTHER group of the CREATE ribbon.
3. Select tblContacts from the SHOW TABLE dialogue box.
4. Drag the asterix (*) from tblContacts down to the first column of the DESIGN GRID.  This is a way of getting the query results to display all fields from the table without having to select each one individually.
5. Then Drag the Company field from tblContacts down to the second column of the grid.  We have added this field separately because we are going to enter a criteria in this column.
6. Click on the CRITERIA row of the Company column, and add the criteria: "Company 5"

The Select Query has now been created.  It should look like this:

Figure 2: The Select Query created in the
first stage of the Delete Query.

It is advisable to run the query at this point and check the results are correct.  They should look like this:

Figure 2: The results from the Select Query.

As you can see, our select query has found four records from tblContacts matching the criteria of Company 5".  Since this is the correct result for the dataset we are working with,  we can move onto the second stage of the process: converting the Select Query to a Delete Query.

Stage 2 - Converting the Initial Select Query to a Delete Query

1. If you look at the QUERY TYPE group of the DESIGN ribbon, you will notice that the SELECT QUERY icon is highlighted orange.  We need to change this to DELETE QUERY.  To do this just click the DELETE QUERY icon further along the group.  

   

   Figure 3: The QUERY TYPE group of the DESIGN ribbon.
   The DELETE QUERY icon is highlighted orange.
2. After the clicking the DELETE icon, you will notice that the row of SHOW tick boxes disappears from the DESIGN GRID, along with the row for SORT. A new row entitled DELETE has taken their place.  Access has filled in the values of FROM and WHERE in the first and second columns respectively.  These are SQL Keywords: the FROM keyword indicates the first column contains fields from tblContacts, and WHERE indicates the Company column contains a criteria against the data stored in this field. 

   

   Figure 4: The QUERY DESIGN GRID for our
   DELETE Query.  Notice the new row for DELETE
   containing the SQL FROM and WHERE
   Keywords.

3. Click RUN from the QUERY RESULTS group.  
4. Click YES when prompted whether we want to delete the number of rows matching our query criteria.  This will be four rows for the dataset we have been working with.

We can now go back and open the tblContacts table.  As you can see from Figure 5 below, all Company 5 contacts have been removed by our DELETE QUERY.

Figure 5: The tblContacts table after Company 5
contacts have been removed.

Using a Crosstab Query to Present Summary Data

In this post we shall use the power of an Access Crosstab Query to summarize and restructure our data into a clear and concise format.  We shall illustrate how to do this with sales made by employees of a Real Estate Company (who are referred to as Estate Agents in the UK).

So in what way does a Crosstab Query improve the presentation of summary data?  Well lets imagine our Real Estate Company asks us to summarize a list of sales made by each of its employees, for each quarter, over the period of a year.  This list has fields for Employee, Quarter and SaleValue.

Figure 1: This is the raw data that will form
the basis of our Crosstab Query.

As you can see in Figure 1 above, this raw data provides us with all the necessary information, but is difficult to digest in its present form.  What we are interested in, is the total SalesValue, for each Employee, for each Quarter.  With the help of a Crosstab Query, we can restructure this information so that each Employee Name becomes a single Row Heading on the left side of the table, and each Quarter Value is grouped together to form Column Heading's across the top of the table.  Since there are three employees, and four quarters in our data, this will give us a table with three rows and four columns.  The Sum of the Sales value then appears where each Employee Row intersects with each Quarter Column.  This is how our Crosstab Query will be structured:

	|Quarter 1|	Quarter 2|	Quarter 3|	Quarter 4|
Employee 1	|Sum of SaleVale|	Sum of SaleVale|	Sum of SaleVale|	Sum of SaleVale|
Employee 2	|Sum of SaleVale|	Sum of SaleVale|	Sum of SaleVale|	Sum of SaleVale|
Employee 3	|Sum of SaleVale|	Sum of SaleVale|	Sum of SaleVale|	Sum of SaleVale|

Creating a Crosstab Query

So how do we create a Crosstab Query?

Well, there are two main ways: the first with the Crosstab Query Wizard, and the second using the Query Design Grid.  We are going to focus on the second method which involves creating the Crosstab Query from Scratch.  However if you do wish to use the Crosstab Query Wizard, you can select the CREATE TAB, click the QUERY WIZARD ICON from the OTHER group, highlight CROSSTAB QUERY from the list in the dialogue box, and then click OK.  When the Wizard starts, follow the instructions to select the Table/Query, the field to be used as Row Headings (SalesPerson), the field to be used as Column Headings (Quarter), and the aggregate function to be used to summarize the SaleValue (Sum).

However, here is the method to create a Crosstab Query from Scratch using the Query Design Grid:

1. Select the CREATE TAB of the Access Ribbon.
2. Click the QUERY DESIGN icon.  It is located in the OTHER group.
3. Select the table or query to be used from the SHOW TABLE dialogue box.  The one I have used is called qrySales.
4. Click the CROSSTAB icon.  This is located in the QUERY TYPE group of the DESIGN ribbon.  Notice how two new rows, Crosstab and Totals, appear in the query design grid.
5. Drag the three field names from qrySales down onto the grid.
6. Go to the Crosstab row of the  SalesPerson column on the QUERY DESIGN GRID. Then select Row Heading from the drop down box in that cell.
7. Next go to the Crosstab row of the Quarter column.  Then select Column Heading from the drop down list.
8. As you may recall, the sum of SaleValue is going to provide the summary data in our table.  To do this, go to the Totals row of the SaleValue column.  Then select Sum from the drop down list.  Then move down to the Crosstab row of the SaleValue column, and then select Value from the drop down list. 

The QUERY DESIGN GRID should  now look like this:

Figure 2: The QUERY DESIGN GRID for our Crosstab Query.

 When you run the Crosstab Query our results should appear like this:

Figure 3: Results of our Crosstab Sales Query.

Its worth pointing out that we can add another row heading containing a Total SalesValue for each SalesPerson across the four quarters.  To do this just go back to the grid and add an additional column for SalesValue (you might want to give the column the alias of Total).   Then select Row Heading from the Crosstab Row on the grid. When run, it should look like this:

Figure 4: Crosstab Query with additional Row Heading comprised of the row Total.

Handling the Conditional: Using the IIf Function

Let's imagine we have an Access table containing a list of academic exam results.  There is a field for Subject and a field for the percentage Result , but we do not have one telling us whether each percentage result is deemed a Pass or Fail.  So what is the best way to 'store' this missing information?

Figure 1: An Access Table containing
Subject and Result fields.

We might be tempted create such a Pass/Fail field, but this is generally considered bad database design practice: since it is possible to calculate whether the student passed or failed on the basis of the percentage result, we would be storing redundant data.  Creating a Query would be a much better option.  However, we need to display whether the result is a Pass or a Fail, so simply entering a criteria to filter out all results above or below a certain percentage is not going to do the job: this would only provide us with a list of Passes or a separate list of Fails.  We just want one column stating Pass or Fail.

A great way of doing this task would be to use the IIf function as a new calculated column within the Query.  This will enable us to display a value indicating whether the exam has been passed or failed.  The IIf function allows us to specify a conditional expression (in a similar way to a query criteria), but then to go on and specify a value to be displayed based on whether the result of the expression happens to be true or false.  In our case we want the expression to determine whether the value contained in a percentage result field is, say,  greater than 50%, and if it is, display "PASS", or else display "FAIL".

The IIf function is constructed as follows:

IIf(Conditional Expression,  True_Condition, False_Condition)

The first parameter we pass for this function is the conditional expression.  In our case this would be Result > 50.  The Second parameter we pass is the string value which appears if the condition is true (for us this would be "PASS"); and the third parameter is the string value which appears if the condition is false (for us this would be "FAIL").  As such we would construct our own particular IIf function like this:

IIf([Result] > 50,  "Pass", "Fail")

We shall enter the this expression into our query as follows:

Figure 2: The IIf Function has been entered into the third column using the alias Pass.

As you can see we have entered our IIf function in the last column on the right.  We have used the alias "Pass" to describe the data to be displayed in this column.  (For more information about alias's and using functions in Access Queries, please see this explanation in relation to the DateDiff Function).  Just remember to separate the alias name from the function with a colon as shown in Figure 2 above.

Now lets run the query and see what we get:

Figure 3: the results of our query to calculate whether
a student has passed or failed an exam.

As you can see, the IIf function has correctly assigned a Pass or Fail based on the percentage Result attained.

Calculating Date Difference: Using the DateDiff Function

Suppose you want to calculate the difference between two dates.  An example of this might be a Library Management System: somebody returns an overdue library book, and receives a fine based on the number of days it happens to be late.  Our two dates in this instance would be the DueDate and the actual DateReturned.  To calculate the difference between these two dates we may use the DateDiff Function.

We could use the DateDiff Function as a Calculated Control (just put an = sign in front of it and enter the function as the textbox's CONTROL SOURCE), or within a VBA Code Module.  In this example, however, we shall use it within an Access Query.

Let's take a look at how the DateDiff Function is constructed:

DateDiff("interval", FirstDate, SecondDate)

As you can see, the function passes three parameters: these are "Interval", FirstDate, and SecondDate.  The interval parameter allows us to specify whether we want the function to return the difference in, for example, days, weeks, or years.  In our case we are interested in how many days a book is overdue, so we enter "d" as a string value. (Had we wanted the interval in weeks, we would have entered the parameter as "ww"; or years as "yyyy".  There are also a number of other options available such as quarter: "q"; hours: "h"; and minutes: "n").  The FirstDate and SecondDate parameter's refer to the two dates between which the difference is to be calculated.  In our case, these dates are contained in the DueDate and ReturnDate fields of a table called jnkLoan. As such, we would construct the DateDiff Function as follows:

DateDiff("d", DueDate, ReturnDate)

Incidentally if the book was returned early, ie the DueDate is later than the ReturnDate, the function would return the Date Difference as a negative value.  As such, the order in which the two date parameters are entered will make a difference to the return value of the function. It is the same principle as the subtraction of a smaller number from a larger number, and vice versa. With the DateDiff Function, the value of the first date is "subtracted" from that of the second, to return the date difference which we defined in the interval parameter.

So lets take a look at how we may use this function in the context of an Access Query:

Figure 1: The DateDiff Function is used in the last column on the right.

For the sake of simplicity I have used a table with just three fields - LoanID, DueDate, and ReturnDate.  However, as you can see from the screen shot above, our query has four columns.  The first three columns contain the fields of our jnkLoan table, but the last column on the right is not bound to any field; it is actually a calculation based on the DateDiff Function that we constructed above. To use the function within this query we have just had to choose an alias for the column - ie a name we make up to refer to the column.  In this example, we have used an alias called Difference.  A colon is then used to separate the alias from our DateDiff Function.

Another thing worth mentioning is how our DateDiff Function refers to the DueDate and ReturnDate fields of the jnkLoan table.  As such our Query bases the DateDiff calculation on the date values contained in the relevant record (ie that which matches the criteria set in the LoanID column) of this table.  Here is the result:

Figure 2: Results of the Query using the DateDiff Function.
The result of the DateDiff calculation is displayed in
the last column on the right.

Our query criteria selected a record containing the LoanID 427648205.  The DueDate for this record was the 08/08/2011 and the ReturnDate is 12/08/2011.  Our DateDiff Function, displayed in the last column on the right, has calculated the book is being returned four days overdue.