Create and Modify Queries: Criteria, Calculations, Totals, and Action Queries
The Create and modify queries domain covers the query design grid: joins, criteria placement, calculated fields, aggregate and crosstab queries, and the four action queries.
Criteria and joins
Conditions typed in the same Criteria row are ANDed; conditions on separate rows are ORed. Pattern matching requires Like with wildcards: * (any characters), ? (one character), # (one digit), and character ranges such as [A-C]. Date literals use hash marks, for example #3/31/2025#. An inner join returns only matching rows; change the join in the Join Properties dialog to include all records from one side for an outer join.
Calculations
Build expressions in a blank Field cell with an alias before a colon: LineTotal: [Quantity]*[UnitPrice], FullName: [LastName] & ", " & [FirstName], Nz([Discount],0), and IIf([Qty]>10,"Bulk","Standard"). The Expression Builder inserts correct syntax for field names and functions.
Totals and crosstab queries
Clicking Totals adds a Total row offering Group By, Sum, Count, Avg, and Where. A Where entry filters without grouping on that field. A crosstab query assigns Row Heading, Column Heading, and Value fields plus an aggregate such as Sum.
Action queries
Make-table, Append, Update, and Delete queries run from the Run button and cannot be undone. Update queries show an Update To row. Always preview the affected records with an equivalent select query first.
Worked example
List every customer with no orders in 2024. Reasoning: add both tables, change the join to include all Customers records, then put Is Null under the Orders primary key with Year([OrderDate])=2024 as a Where condition - an inner join would silently drop the very rows you want.
Traps
- Using
*without the Like operator. - Putting two OR conditions in one row, which ANDs them.
- Grouping by a field that should only filter it.
- Omitting the Total row in a crosstab, which leaves the query invalid.