I85PREP
⌘ K
MOS Access Expert study materials
MOS Access Expert study resources

Crosstab, unmatched and parameter queries for a quarterly review

MOS Access Expert · Put your knowledge to work in context.

All case studies

Crosstab, unmatched and parameter queries for a quarterly review

A regional distributor keeps tblCustomers, tblOrders (OrderID, CustomerID, OrderDate, EmployeeID), tblOrderDetails (OrderID, ProductID, Quantity, UnitPrice, Discount) and tblProducts (ProductID, CategoryID, CategoryName). Finance wants four outputs for the quarterly review. First, revenue by month and product category as a crosstab; the existing crosstab pivots Format(OrderDate,'mmm') and currently shows only January and February columns, because only two months of the quarter have posted so far. Second, a list of customers who placed no orders in the last twelve months; the analyst's version returns zero rows even though three customers are known to be inactive. Third, the top ten customers by revenue. Fourth, a prompt where the user types a year and sees only that year's orders; the existing parameter query asks the question but returns the same 41,000 rows whatever is typed.

1. Fix the crosstab so all twelve months appear in calendar order with zeros rather than blanks, and explain why the fix is needed.

Read the worked answer

A crosstab generates a column only for values that actually exist in the data, which is exactly why only January and February appear. Sorting is a second, separate problem: pivoting on the month name sorts the column headings alphabetically (Apr, Aug, Dec, Feb...), which is not calendar order.

Fixes:

  1. In the crosstab query's property sheet, set Column Headings to the full literal list: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec. Access now creates all twelve columns, and any missing combination shows Null.
  2. Wrap the value in Nz: TRANSFORM Nz(Sum([Quantity]*[UnitPrice]*(1-[Discount])),0). Null is not zero; a report that sums the row would otherwise be fine but a chart or a downstream calculation is not.
  3. Keep the pivot on the formatted name, since the Column Headings list depends on it, and sort the SELECT row headings explicitly with an ORDER BY on CategoryName rather than relying on the grouping order.

Declare any parameters in the Query Parameters dialog rather than letting Access guess. That fixes both repeated prompts and silent type coercion, and it lets Access reuse one parameter in several places.

2. The 'no orders in twelve months' query returns nothing. Diagnose the likely construction errors and give a correct approach.

Read the worked answer

Three constructions produce an empty result here, and they look identical to the user.

  1. An inner join. Joining tblCustomers to tblOrders on CustomerID and then filtering for no orders can never return a row: the join already discards customers without orders.
  2. A Left Join with the criterion on the wrong side. WHERE tblOrders.OrderDate < Date()-365 on a LEFT JOIN promotes it back to an inner join, because the Null rows fail the comparison. The test for 'never ordered' must be a Null test on a field from the many side: tblOrders.OrderID Is Null.
  3. A NOT IN subquery. If the subquery can return a Null CustomerID, NOT IN evaluates to unknown for every row and returns nothing. Use NOT EXISTS or add WHERE CustomerID IS NOT NULL inside the subquery.

The reliable pattern is the Find Unmatched Query Wizard, or hand-built: LEFT JOIN from tblCustomers to a subquery of orders in the last twelve months, criterion Is Null on the subquery's key. Define 'no orders in twelve months' explicitly - a customer with a single old order qualifies, so the subquery must be filtered by date, not the outer query.

3. The year parameter prompts but returns all 41,000 rows. Explain the cause and the correct construction.

Read the worked answer

The prompt returning everything means the criterion is not actually filtering. The usual cause is that [Enter year:] and the field live in different expressions that Access never equates. For example, the analyst grouped by Year([OrderDate]) and put [Enter year:] in the criteria row of OrderDate, or added the parameter to a calculated field with an Or row. The prompt appears, the value is collected, and no comparison uses it.

Correct construction:

  1. Add a calculated column OrderYear: Year([OrderDate]).
  2. Put [Enter year:] on the Criteria row of that column, not of OrderDate.
  3. Open Query Parameters and declare [Enter year:] as Integer. Without the declaration Access treats the prompt as text and compares text to a number; it may coerce, it may match nothing, and in a crosstab it prompts once per reference.
  4. If the value must reach a subquery or a second query, put the parameter in the top-level query only.

Sanity check: run the query with 2023 and confirm the record count differs from 2024, and that the count matches a Year([OrderDate])=2023 filter written literally. A declared parameter also makes the query plan stable, which matters at 41,000 rows.