What if you want to show multiple sets of values at each matrix point? Say the crosstab shows products at the left, and months across the top, and you want to show both the dollar value and number of products sold at each intersection? Duane Hookom has an example of dynamic monthly crosstab reports. But if you specify the Column Headings, it can read the field names without running the query.Īn alternative approach is to alias the fields so the names don't change. If you do not specify the column headings, Access is unable to determine the fields that will be available to the report without running the entire query. Where a report has a complex crosstab query as its Record Source, specifying the column headings can speed up the design of the report enormously.
Delimit text values with quotes, or date values with #.įor the query above, set the Column Headings property like this (on one line): "Buchanan, Steven", "Callahan, Laura", "Davolio, Nancy", "Dodsworth, Anne", "Fuller, Andrew", "King, Robert", "Leverling, Janet", "Peacock, Margaret", "Suyama, Michael"
The button is on the Records group of the Home tab, and the icon is an upper case sigma (Σ). In Access 2007 and later, you can also show the total at the bottom of each column, by depressing the Totals button on the ribbon. (The total displays to the left of the employee names.)
So, we added the Sum of Quantity again as a Row Heading - the right-most column in the screenshot. In the example above, we used the Sum of the Quantity as the value. To show the total of all the columns in the row, just add the value field again as a Row Heading. GROUP BY Products.ProductID, Products.ProductName ON Employees.EmployeeID = Orders.EmployeeID SELECT Products.ProductID, Products.ProductName, Sum(.Quantity) AS TotalįROM Employees INNER JOIN (Products INNER JOIN (Orders INNER JOIN To create this query, open the Northwind sample database, create a new query, switch to SQL View (View menu), and paste: TRANSFORM Sum(.Quantity) AS SumOfQuantity In the example below, the product names appear down the left, the employee names become fields, and the intersection shows how many of this product has been sold by this employee: An exampleĪ crosstab query is a matrix, where the column headings come from the values in a field.
#CROSSES TABS SERIES#
This article explains a series of tips for crosstab queries. Multiple sets of values Crosstab query techniques