Lesson · 24 min

Crosstab, Action, and Append Queries

Move beyond SELECT: summarize data in a grid, and use queries that change data rather than just returning it.

By the end of this lesson you can

Build a crosstab query to summarize data across two dimensions
Distinguish the four action query types and what each does
Use an append query to add records from one table to another
Apply the safety practices that action queries require

📘 Reading Lesson

Lesson Notes

Read through the key concepts before you try the challenge.

Queries that summarize, and queries that change things

On the job

You produce management reports at Lakeside Medical Associates.

The practice manager wants supply spending shown as departments down the side and months across the top — the shape of a spreadsheet cross-tab. She also wants last year's records moved out of the active table into an archive, because the active table has grown slow.

Your task: Learn the query types that summarize across two dimensions, and the ones that modify data.

A crosstab query summarizes data in a grid: one field supplies the row headings, another supplies the column headings, and a third is aggregated at each intersection. It is Access's equivalent of a PivotTable, and it answers 'how much of X by Y' in one object.

Setting	Supplies	Example
Row Heading	The labels down the left	Department
Column Heading	The labels across the top	Month
Value	What is aggregated at each cell	Sum of TotalCost

Crosstab query components

Action queries are different in kind: they change data rather than returning it. There are four, and each is genuinely destructive in its own way, so they require a different level of care than a SELECT query.

Type	Does	Typical use
Make Table	Creates a new table from query results	Snapshotting data at a point in time
Append	Adds query results as records in an existing table	Moving last year's records into an archive table
Update	Changes values in existing records	Applying a price increase across a category
Delete	Removes records matching the criteria	Clearing archived records from the active table

The four action queries

Worked example

Archiving last year's records safely

Move records older than one year from the active Supplies table into SuppliesArchive, then remove them from the active table.

1
Back up the database file before doing anything else.
Action queries cannot be undone. There is no Ctrl+Z after an append or delete, and no confirmation beyond a single dialog. A copy of the .accdb file is the only real safety net, and it takes seconds.
2
Build it as a SELECT query first and examine the results.
This is the discipline that prevents disasters. Run it as a SELECT and you see exactly which records the criteria match. If the count or the contents look wrong, you have learned that harmlessly rather than after deleting them.
3
Convert it to an Append query and target SuppliesArchive.
Access converts the query type while keeping your verified criteria, so the records that are appended are precisely the ones you just inspected. Check that the source and destination field names line up — mismatches silently drop data into the wrong column.
4
Run the append, then verify the archive table before deleting anything.
Confirm the row count in the archive matches what the SELECT returned. Deleting from the active table before confirming the copy arrived is how a year of records is lost permanently.
5
Only then convert a copy of the same query to a Delete query and run it.
Using the same criteria guarantees you delete exactly what you archived. Writing fresh criteria for the delete step risks a subtle difference between the two sets — and the difference would be records deleted but never archived.

Result: Last year's records safely in the archive table and removed from the active one, with a backup if anything went wrong.

Back up, build as SELECT, verify, then convert. Every action query gets this treatment — there is no undo.

Action queries do not have an undo. A Delete query that runs with the wrong criteria removes records permanently, and Access asks only once before doing it. Treat every action query as irreversible: back up first, always build it as a SELECT to see what it will touch, and never run one you did not write yourself without reading its criteria.

Check your understanding

What is the most important step before running a Delete query?

Challenge

Apply what you've learned in this lesson.

Work on a copy of your database. Do not run action queries against anything you cannot afford to lose.

Copy your database file before starting. Confirm the copy opens.
Build a crosstab query with category as rows, order month as columns, and sum of total cost as the value. Describe what question it answers that a normal Totals query does not.
Create an archive table matching your supplies table's structure. Build a SELECT query for records older than a chosen date, verify the results, then convert it to an Append query and run it.
Write the five-step safety procedure for action queries in your own words, as you would give it to a new colleague.

Finished this lesson?

Progress is saved in this browser only. It is not a grade — official progress lives in Brightspace.

Designing Reports with Grouping and Sorting →