Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 36 ยท Queries

Parameter, Crosstab and Action Queries in Access

Ask for input with parameter queries, pivot data with crosstab TRANSFORM, and change data safely with update, append, delete and make-table queries and union queries.

Select queries only look. Several other kinds of queries do more: a parameter query asks the user for a value each time it runs, a crosstab query rotates data into a compact grid, and action queries change data in bulk. This lesson covers all of them, plus union queries that stack results, and the safeguards that keep action queries from doing damage.

Action queries are powerful. A badly written delete query can wipe a table in a second, and in Access there is no Undo for it. Read the safety section before running one.

Parameter queries

A parameter is a prompt in square brackets used as a criterion. When the query runs, Access shows a dialog and uses the typed value.

In the Criteria row of Category, type:

Text
[Enter a category]

Run the query and Access asks "Enter a category" in a small dialog. Whatever the user types becomes the criterion. You can combine prompts with wildcards:

Text
Like "*" & [Search for:] & "*"

This gives a "contains" search. A range works with two prompts:

Text
Between [Start date] And [End date]

To declare the type of a parameter (useful for dates and numbers), choose Query Design > Parameters and list each name with its data type. Without it, Access guesses.

SQL
PARAMETERS [Enter a category] Text ( 255 );
SELECT ProductName, UnitPrice
FROM Products
WHERE Category = [Enter a category];

The prompt text must match exactly wherever it is used in the query. A misspelled prompt shows as a second dialog. A field name that is spelled wrong also becomes a parameter prompt, because Access treats any unknown name in brackets as a parameter. If you are suddenly asked for "UnitPrise", you made a typo.

Forms can supply parameter values too, such as [Forms]![frmSearch]![txtCategory], which removes the pop-up.

SQLite cannot show prompts, but it has named parameters. This runnable stand-in sets the value in a one-row table instead:

SQL A parameter simulated with a settings row

Press Run (or Ctrl + Enter) to see the result here.

Crosstab queries

A crosstab query summarises data in a grid: row headings down the side, column headings across the top and a calculated value in each cell. Think of a pivot table: customers down the side, months across and total sales in the cells.

Build one with Create > Query Wizard > Crosstab Query Wizard, or in Design view with Query Design > Crosstab. A Crosstab row replaces Sort/Show, and each field gets one of:

  • Row Heading (one or more fields),
  • Column Heading (exactly one field),
  • Value (exactly one field, with a Total of Sum, Count and so on),
  • or no crosstab role (Where, to filter).

The SQL uses the Access-only keywords TRANSFORM and PIVOT:

SQL
TRANSFORM Sum(Amount) AS Total
SELECT Category
FROM Sales
GROUP BY Category
PIVOT Format(SaleDate, "yyyy-mm");

TRANSFORM names the value, SELECT ... GROUP BY the rows and PIVOT the column headings. SQLite has no TRANSFORM. The standard approach is conditional aggregation, which works in every database:

SQL A crosstab by conditional aggregation

Press Run (or Ctrl + Enter) to see the result here.

A few Access crosstab facts: it can show a Row Total column, the column headings are sorted alphabetically unless you fix them with the Column Headings property (such as "Jan","Feb","Mar"), and a crosstab result cannot be edited. Forms and reports built on a crosstab need fixed headings, or they break when headings change.

Union queries

A union query stacks the results of two or more selects into one list. The selects must have the same number of columns, in compatible types. UNION removes duplicates, UNION ALL keeps them. Union queries have no grid; you must write them in SQL view.

SQL
SELECT CustomerName AS Name, City FROM Customers
UNION
SELECT SupplierName, City FROM Suppliers
ORDER BY Name;
SQL UNION and UNION ALL

Press Run (or Ctrl + Enter) to see the result here.

Action queries

Action queries change data. Their icons on the Query Design tab have a red exclamation mark. There are four kinds.

Update query

Changes values in existing rows. Choose Query Design > Update; a new Update To row appears. Put the new value or expression there, and a criterion to limit which rows change. To raise Furniture prices by 5%:

SQL
UPDATE Products SET UnitPrice = UnitPrice * 1.05 WHERE Category = "Furniture";

Append query

Adds rows from one table or query into another table: Query Design > Append, then pick the destination. Use it to archive old orders or to load a staging table. In SQL: INSERT INTO Archive SELECT ... FROM Orders WHERE ....

Delete query

Removes rows: Query Design > Delete with a Where criterion. Always double-check the criteria. Without them, every row goes.

Make-Table query

Runs a select and stores the result as a new table, replacing a table of the same name. It is used to take a snapshot or to speed up a slow report. In SQL: SELECT ... INTO NewTable FROM ....

SQL Update, append and delete together

Press Run (or Ctrl + Enter) to see the result here.

Running action queries safely

  1. Preview first. Build the query as a select with the same criteria, run it and check the rows. Then switch the query type to Update or Delete (Query Design > Update/Delete).
  2. Back up the database before bulk changes, or at least copy the table.
  3. Use transactions in VBA (BeginTrans, CommitTrans, Rollback) when several actions must succeed together.
  4. Expect warnings. Access asks "You are about to update N row(s)". Read the number. Turning off confirmations (File > Options > Client Settings > Confirm > Action queries) removes your last safety net.
  5. Know the limits: cascading deletes, referential integrity and validation rules still apply, and an update can fail for some rows ("Access was unable to update N records due to key violations or lock violations") while still changing the others unless you run it in a transaction.

An action query cannot be undone. A delete query with an empty Criteria row deletes every record. Always preview with a select query and keep a backup.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What makes Access ask the user for a value when a query runs?

    Any unknown name in brackets is treated as a parameter, which is why a typo in a field name triggers an unexpected prompt.
  2. 2. Which keywords build a crosstab query in Access SQL?

    TRANSFORM names the aggregate and PIVOT supplies the column headings.
  3. 3. What is the safest way to build a delete query?

    Previewing shows exactly which rows will be affected before you convert it to a delete.
  4. 4. What is the difference between UNION and UNION ALL?

    UNION de-duplicates the combined result; UNION ALL simply stacks the rows.

Summary

  • A parameter query uses a bracketed prompt as criteria; declare types with the Parameters dialog.
  • Crosstab queries use TRANSFORM and PIVOT in Access; conditional aggregation does the same elsewhere.
  • Union queries stack selects and must be written in SQL view.
  • Action queries (update, append, delete, make-table) change data with no Undo, so preview with a select and back up first.
  • Misspelled field names can show up as surprise parameter prompts.

Next: Creating Forms in Access