Create and run a crosstab query.

      TRANSFORM aggFunction SelectStatement PIVOT
         pivotField [IN (value1[, value2[, …]])]

   aggFunction     An SQL aggregate function that operates on the selected data.

   SelectStatement A SELECT statement.

   pivotField      The field or expression used to create column headings
                   in the query's result set.

   value1, value2  Fixed values used to create column headings.

If any rows or columns contain NULLs they will appear under a row/column as <>


'Return Purchase Dates as rows, ServicePack pivoted as columns and count the numbers of servers from the table T_Servers:

TRANSFORM Count(T_servers.svr_Name) As CountServers
SELECT T_Servers.svr_Purchase_Date
FROM T_Servers
GROUP BY T_Servers.svr_Purchase_Date
PIVOT T_Servers.svr_os_Service_Pack;

“Some painters transform the sun into a yellow spot; others transform a yellow spot into the sun” ~ Pablo Picasso


Delete - Delete records.
Execute (SQL/VBA) - Execute a procedure or run SQL.
Insert - Add records to a table (append query).
Nz - Detect a NULL value or Zero Length string.
Select - Retrieve data from one or more tables or queries.
Select Into - Make-table query.
Select-Subquery - SubQuery
Union - Combine the results of two SQL queries.
Update - Update existing field values in a table.
Q304458 - Create a crosstab query with multiple value fields

Copyright © 1999-2024
Some rights reserved