Beyond standard Select Queries, Microsoft Access provides powerful action queries to batch-update records, generate summary tables, transform pivot layouts, and calculate transactional figures on-the-fly.
Special Types of Queries
In addition to the standard Select Query, Access supports specialized Action Queries designed to modify, append, or restructure data directly in your tables.
Click to view query type selector ribbon
1. Select Query
The default query created by Query Design and the Query Wizard. It extracts records from one or more tables/queries without altering the underlying data.
2. Update Query
Modifies existing data in bulk based on specified criteria (e.g., updating State to "NY" where City is "New York"). Start with a Select Query to verify target records, click Update on the ribbon, and specify target values in the Update To: row.
3. Delete Query
Removes records matching specific criteria. Best practice: Test your criteria first in a Select Query to ensure only intended rows appear, then toggle the query type to Delete and execute.
4. Append Query
Inserts records from one data source into an existing destination table (e.g., migrating rows from an Excel staging table into production tables). Use the Append To: row to map disparate column names between source and destination tables.
5. Make Table Query
Creates a new table populated by query results. Useful for exporting standalone snapshots, archiving static historical records, or isolating data for end-user reporting without exposing operational tables.
6. Crosstab Query
Pivots columnar rows into a multi-dimensional matrix, similar to an Excel PivotTable. Use the Crosstab Query Wizard to structure row headers, column headers, and calculated values.
7. Union Queries
Combines rows from two compatible SELECT statements into a single unified result set. Because Union queries have no graphical design grid in Access, they must be written in SQL view.
Calculating Data in an Access Query
A common database design mistake is storing calculated totals directly in table columns. The best practice is calculating values dynamically at runtime within queries. If an underlying field (e.g., Quantity) changes, the query recalculates automatically without data drift.
Click to view calculated field expression syntax
Syntax & Expression Rules:
- Column Aliases: Specify the column name followed by a colon (e.g., Line Total:). Access uses the colon to separate the alias from the formula.
- Field Brackets: Enclose field names in square brackets (e.g., [Quantity] * [Unit Price] * (1 - [Discount])). This prevents parsing errors when field names contain spaces.
- Formatting: Right-click the calculated column, select Properties, and set the Format property to Currency, Percent, or Standard Number.
Click to view calculated rows
Handling Nulls and Division by Zero
Prevent #Error results on empty or zero denominators with the IIF() conditional expression:
IIF([TotalRevenue] > 0, [TodaysRevenue] / [TotalRevenue], 0)
Grouping Data with Access Aggregate Queries
Aggregate queries let you summarize large datasets with math functions like Sum, Avg, Count, Min, Max, and StDev.
To convert a standard query into an Aggregate Query, click the Totals (Σ) button on the ribbon. A new Total: row appears in the query design grid.
Click to view Totals row configuration
Controlling Granularity:
The number of columns set to Group By determines summary granularity. Grouping by Company and OrderID calculates line sums per distinct purchase order:
Removing the OrderID column aggregates all orders across time, producing a single lifetime revenue total per customer:
