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.