Library Subject Guides
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.
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.
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.
When doing calculations with numerical data in calculated data:
Some examples using sample field names:
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:
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:
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.
Examples:
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:
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.
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).
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.
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.
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.
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 Table | Creates a new table containing the data selected by the query. |
|---|---|
| Append | Adds data selected by the query to an existing table - the field properties and field order must match exactly. |
| Update | Changes the value in one or more fields for selected records. |
| Delete | Removes entire selected records (not just selected data) from the table. Deleted records cannot be recovered - there is no undo. |
As action queries can result in significant changes to data, it is recommended to follow this approach where you create a select query first:
| Make Table | A dialogue requests a name for the new table. |
|---|---|
| Append | A dialogue requests the name of the existing table to which the data is to be appended. |
| Update | Enter the value to update to in the new Update To: row in the QBE grid. |
| Delete | No further editing needed - selected records will be deleted. |
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.
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.
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.
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.
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 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.
Make sure you've downloaded the Essential Access exercise files before attempting these exercises.
