Skip to Main Content
University of York Library
Library Subject Guides

Essential Access: a Practical Guide

Further queries

We previously looked at creating queries to sort and filter data, creating subsets of data and working with data across multiple tables. However, you can also use queries to do data manipulation.

In this section, we'll explore using calculated fields in queries, working with grouping and totals, creating parameter queries and action queries, and what an alternative join is.

Calculated fields in queries

Queries can do calculated on data retrieved from tables and display it in a temporary field, with the data in the field calculated each time the query is run.

In Access, this is done by entering the details in an empty column in the QBE grid, with the name of the temporary field first followed by a colon. Then an expression is written to do a calculation, with the names of any existing fields from the tables included in the query written inside sqaure brackets.

The query can have fields added as normal and also calculated fields, which are temporary. The name and calculation is written in the Field row of the grid.

Zoom feature

To help with editing expressions in the Field: row in the QBE grid, you can Zoom the contents, opening a dialogue box with an adjustable font.

  1. To do this, right click on the existing contents in the Field: row and choose Zoom.... Or, whilst editing the contents of the Field: row press SHIFT + F2.
  2. The contents are shown in a dialogue box and can be edited here. Use the Font... option to change the display of the zoom control (it won't change the query view, only this box).
The Zoom feature makes it easier to edit longer expressions

Numerical data in calculated fields

When doing calculations with numerical data in calculated data:

  • The common arithmetic operators can be included, like + - * / ^
  • Brackets can be used to control the order of precedence or avoid ambiguity.
  • Numbers can be used but must not be enclosed in quotes.
  • Mathematical functions such as Sqr() or Round() can be used.

Some examples using sample field names:

  • [Cost] * [Quantity] * 10%
  • [Price] - [Discount]
  • Round([Mark]/5,2)
  • Date() + 14

Number format

You can control the number format (the same concept as the Format from Table Design view) and details like decimal places from in the query design view, though if you're using the values in a form or report you can also adjust these details when making that form or report.

To access the query properties and set the number format:

  1. With the query in design view, select Query Tools > Design > Show/Hide > Property Sheet to view the Property Sheet.
  2. The property sheet will always show properties for the current object - click anywhere in the field you want to be formatted to show the property values for this field.
  3. On the General tab, choose the appropriate format from the Format drop-down list. If you choose Fixed you can then set a number of Decimal Places to display. Access will round values correctly when using this option.

The format of individual fields can be configured using the property sheet

Text data in calculated fields

There are also built-in features that can be used with text data in a field, even if you wouldn't usually think of working with text as "calculating".

To adjust the view of text, use functions like:

  • UCase() converts the text to uppercase
  • LCase() converts the text to lowercase
  • Left() returns a specific number of characters starting from the left - commonly used to produce an initial from a name, e.g. Left([FirstName],1)


Any number of fields can be joined together (concatenated) by entering their field names separated by an ampersand &. New text enclosed in double quotes can also be joined. Bear in mind that spaces are not added when joining together text fields or additional text, so you may need to add the space character within quote marks " " into the formula to have the text display as expected.


  • "Telephone number: " & [TelNumber]
  • [FirstName] & " " & [LastName]
  • Left([FirstName],1) & " " & [LastName]

Grouping and totals

Queries can be used to calculate with and count a group of similar records.For example, you might count the number of students taking each module or work out the average of marks for each module.

This is done by enabling the Totals row and then applying Group By to one or more fields then choosing a function for another field. The steps are as follows:

  1. Begin a new query in design view.
  2. Identify the field(s) which identify the groups of similar records and add to the QBE grid. You want to choose as few as possible to ensure your query works.
  3. Identify the field containing values you need to count (total, average, etc) and add to the QBE grid.
  4. Enable the Totals row by selecting Design > Show/Hide > Totals.
  5. In this Total row that appears in the bottom grid, choose the function you want (Count, Average, etc) for the appropriate field and check all others are set to Group By.


Use as few fields as possible and only apply the function to one field, to ensure your query works. You might have to adjust your choices if you don't get what you expect when you run the query.

If you use Group By and set Count, it will group the records by certain fields and then count the number of records in each of those categories.

Total row

When in datasheet view, Access provides a quick way to display totals, averages, etc at the bottom of the fields (though if you need to do this frequently, you may want to create a report that does this instead).

  1. In datasheet view on the Home tab in the Records section go to Totals to enable the extra row.
  2. Select the appropriate function from the drop-down menu at the bottom of the relevant field. The options will depend on the data type.
  3. Once you enable the Totals row in datasheet view, there is a dropdown that allows you to select a function as see the result for that column.

Paramater queries

Filtering criteria are normally saved within a query, but sometimes it is useful to only specifiy the criteria when the query is run. For example, a query might only ask for a last name or department when the query is run, making it easier to run multiple searches without editing the underlying query each time.

This is done by entering text enclosed by square brackets into the criteria row of the query. When the query is run, this is the text that appears as the onscreen prompt.

In the Criteria row of the QBE grid enter text in square brackets.

This feature can be more useful when using forms, as the query the form is based on can take its input from a form field instead of the basic dialogue box.

Unexpected parameter requests

If a request for the entry of a parameter value appears when you don't expect it (i.e. you've not set up a parameter query), this generally means Access doesn't recognise one or more of the field names in the query. This can happen with calculated queries where field names are typed by hand. Check the text on the pop-up as this will indicate what the typing error is.

A parameter request has appears because Mark has been misspelled as Mirk

Action queries

Queries that retrieve, filter, and sort data from underlying tables, but do not themselves change the data, are called select queries. Action queries are able to modify data and delete records in bulk.


Changes made by an Action query cannot be undone so you should take great care when using them.

There are four sorts of action query available:

Make TableCreates a new table containing the data selected by the query.
AppendAdds data selected by the query to an existing table - the field properties and field order must match exactly.
UpdateChanges the value in one or more fields for selected records.
DeleteRemoves entire selected records (not just selected data) from the table. Deleted records cannot be recovered - there is no undo.

Constructing an action query

As action queries can result in significant changes to data, it is recommended to follow this approach where you create a select query first:

  1. First construct a Select query (as already covered) that selects the records that need to be affected by the action query. Check this is correct before proceeding.
  2. Choose the required action query from the Design tab Query Type section.
  3. Supply any additional information as necessary - this will depend on the type of action query chosen:

Make TableA dialogue requests a name for the new table.
AppendA dialogue requests the name of the existing table to which the data is to be appended.
UpdateEnter the value to update to in the new Update To: row in the QBE grid.
DeleteNo further editing needed - selected records will be deleted.

Using action queries

If an action query is to be used on more than one occasion, it is worth saving the design. When saved, an alternative icon is used to indicate an action query.

Action queries appear differently in the All Access Objects list

Opening to edit the design

To open an action query to modify the design, locate the query in the navigation pane, right-click on the query and select design view.

Never double-click on an action query to open for editing, as this will run the query instead.

If the action query is open in Design view, switching to Datasheet view will usually be possible, and will not result in data changes.

Running an action query

To run an action query, either when you're in Design view, choose Design > Results > Run, or from the navigation pane, right-click on the query and select Open.

Unless the database designer has disabled messages, you will be warned of any changes to data about to take place and have the option of cancelling the action.

Action queries usually require confirmation before changing data - unless messages have been disabled by the designer.

Append and delete

One use for action queries is archiving old data, which is often preferable to deleting.

There is no single query for archiving. Instead, you must use an Append query to identify and add records to a separate archive table (which you can initially create using a make table query) and follow this with a Delete query (with the same criteria) to remove them from the original table.

Alternative joins

When a query contains two tables with related records, the default relationship (an inner join) will only display corresponding recoeds from both tables. Records that exist in one table but have no corresponding records in the second table will not be displayed.

For example, this query shows a list of modules and the tutors. When configured as an inner join, only modules for which tutors have been allocated are shown; when an outer join is used, all modules are listed, including those with no tutor.

An inner join shows only modules with tutors, whereas an outer join shows modules that do not have a tutor listed.

Configuring an outer join in a query

An outer join may be configured when the join is first created or by later editing. In most cases an outer join will only be required in specific queries, in which it is best configured in the query design.

if an inner join already exists, the reconfigured outer join will apply only to that query and will not affect an underlying inner join.

  1. In the query design view, right click on the relationship to be changed and choose Edit Relationship... or double click on the relationship.
  2. Modify the relationship settings as required in the Join Properties dialogue.
On the Join Properties dialogue choose the option that displays the appropriate combination of records, then select OK.

Make sure you've downloaded the Essential Access exercise files before attempting these exercises.