Different Types of Access Queries

Throughout this module you have been working with the Select query — the most common query type in Access, used to retrieve and display records from one or more tables in a datasheet, or to serve as the record source for a form or report. The Select query is the foundation, but it is one of six query types available in Access, each designed for a different category of task. Knowing which query type to reach for, and why, is what separates a functional Access database from a well-designed one.

Query types fall into two broad groups. Select and Crosstab queries retrieve and display data without modifying it. Action queries — Make-Table, Update, Append, and Delete — modify data in bulk. The distinction matters because Access treats action queries differently: it warns you before running them, and their effects cannot be undone with Ctrl+Z.

To change the query type while in Design View, go to the Query Design tab on the ribbon and click the appropriate button in the Query Type group. The query type can be changed at any point during the design process.

The Select query is the default and the most widely used query type. It retrieves records from one or more tables based on the criteria, sort order, and field selections you define in the design grid, and displays the results in a datasheet. Select query results are generally editable — you can update individual field values directly in the datasheet, and those changes write back to the underlying table.

Select queries are also the standard record source for forms and reports. When a form opens, it loads its data from whatever Select query — or table, which behaves like an implicit Select query — is set as its record source. Most of the queries you have built in this module so far are Select queries.

Select queries can include calculated fields, criteria, sorting, joins across multiple tables, and parameter prompts. They are the most flexible query type for read-oriented work.

A Crosstab query displays data in a compact, spreadsheet-style grid where row headings come from one field, column headings come from another field, and the cells contain an aggregate value — a sum, count, average, or other aggregate function — calculated from a third field. The result resembles Microsoft Excel's PivotTable feature and is useful for summarizing large datasets in a way that a standard datasheet cannot.

A practical example: you have an Hours table with DateWorked, ProjectDescription, and HoursWorked fields. A Crosstab query could display project names as row headings, months as column headings, and the sum of HoursWorked in each cell — giving you a month-by-month breakdown of hours per project in a single compact view.

Crosstab query results are read-only. Because the values in the cells are aggregated from multiple source records, there is no single record to write back to. You can view and export the results, but you cannot edit them directly in the datasheet.

Access provides a Crosstab Query Wizard (available from the New Query dialog) that guides you through selecting the row heading, column heading, and value fields step by step. For complex crosstabs you can also build them directly in Design View using the Totals row and the Crosstab row that appear when the Crosstab query type is selected.

A Make-Table query runs like a Select query — it retrieves records based on your criteria and field selections — but instead of displaying the results in a datasheet, it writes them into a brand-new table that it creates on the spot. If a table with the specified name already exists, Access asks whether you want to replace it before proceeding.

Make-Table queries are useful for creating snapshot tables — a frozen copy of a result set at a point in time — which can then be used as the basis for reports, exports, or further processing without affecting the live source tables. They are also used to archive records before a Delete query removes them, or to move data to a different Access database.

Because a Make-Table query creates or overwrites a table, Access displays a confirmation dialog before running it, warning you that the action cannot be undone. This is standard behavior for all action queries.

An Update query modifies field values in existing records across an entire result set in a single operation. Instead of opening each record individually and editing it by hand, you write the update expression once in the design grid and Access applies it to every record that matches your criteria.

Common uses include: applying a price increase to all products in a category ([UnitPrice] * 1.1), correcting a misspelled value across all records that contain it, updating a status field from "Pending" to "Active" for all records past a certain date, or recalculating a derived value after a formula change.

Before running an Update query, switch to Datasheet View first. In Datasheet View, an Update query shows you the records that would be affected — the rows that match your criteria — without actually making any changes. This lets you verify that the right records are selected before you commit. Switch back to Design View and click Run to execute the update. Access will warn you how many rows will be updated and ask for confirmation.

An Append query adds records from one table — or from a query result set — into an existing table. It is the bulk-insert equivalent of typing new records one by one in a datasheet. The source records are not removed; only copies of them are appended to the destination table.

Append queries are commonly used for data migration tasks — moving records from a staging table into a production table after validation — and for consolidating data from multiple similar tables into a single master table. They are also useful for archiving: select records older than a certain date, append them to an archive table, then run a Delete query to remove them from the live table.

When you switch to Append query type in Design View, Access adds an Append To row to the design grid, where you map each field in the source query to the corresponding field in the destination table. Access will flag any type mismatches or constraint violations before committing the rows.

A Delete query removes all records that match the specified criteria from a table in a single operation. It is the most destructive of the action queries — deleted records cannot be recovered with Ctrl+Z, and there is no recycle bin for database records. As with Update queries, the best practice is to switch to Datasheet View first to review exactly which records will be deleted, then switch back to Design View and run the query after confirming the selection is correct.

Delete queries respect referential integrity rules. If the table you are deleting from is the "one" side of a one-to-many relationship and cascade delete is not enabled, Access will refuse to delete records that have related records on the "many" side. This is a safety feature — it prevents orphaned records in the related table.

A common workflow pairs an Append query with a Delete query: first append the target records to an archive table, then delete them from the source table. This gives you a recoverable backup before the deletion.

In addition to the six query types above, Access supports Total queries — sometimes called Aggregate queries — which calculate summary values across groups of records. A Total query can sum all the hours worked per project, count the number of clients per state, or find the maximum invoice amount per customer. Total queries use the Totals row in the design grid, which becomes available when you click the Totals (Σ) button on the Query Design ribbon. Totals queries and the action queries above will be covered in the remaining lessons of this module.

Access also supports three SQL-specific query types that are more advanced and fall outside the scope of this course:

• Union query — combines the result sets of two or more Select queries into a single output, stacking the rows vertically. Useful for merging data from tables with identical structures.
• Pass-Through query — sends SQL commands directly to an external database server (such as SQL Server or Oracle) without Access interpreting them. Used when you need to execute server-specific syntax or stored procedures.
• Data Definition query — executes DDL (Data Definition Language) SQL statements such as CREATE TABLE, ALTER TABLE, or DROP TABLE directly within Access. Used for programmatic schema changes.

These three query types are available from the SQL Specific submenu on the Query menu in Query Design mode. They require familiarity with SQL syntax and are not covered in this course.

In the next lesson, you will learn how to create action queries to perform bulk operations.