This example uses the SQL TRANSFORM clause to create a crosstab query showing the number of orders taken by each employee for each calendar quarter of 1994.
PARAMETERS prmYear SHORT;
TRANSFORM Count(OrderID)
SELECT FirstName & " " & LastName AS FullName
FROM Employees INNER JOIN Orders
ON Employees.EmployeeID = Orders.EmployeeID
WHERE (""yyyy"", OrderDate) = [prmYear]
GROUP BY FirstName & " " & LastName
ORDER BY FirstName & " " & LastName
PIVOT DatePart("q", OrderDate);
This example uses the SQL TRANSFORM clause to create a slightly more complex crosstab query showing the total dollar amount of orders taken by each employee for each calendar quarter of 1994.
PARAMETERS prmYear SHORT;
TRANSFORM Sum(Subtotal)
SELECT FirstName & " " & LastName AS FullName
FROM Employees
INNER JOIN (Orders INNER JOIN [Order Subtotals]
ON Orders.OrderID = [Order Subtotals].OrderID)
ON Employees.EmployeeID = Orders.EmployeeID
WHERE DatePart ("yyyy", OrderDate) = [prmYear]
GROUP BY FirstName & " " & LastName
ORDER BY FirstName & " " & LastName
PIVOT DatePart(""q"",OrderDate);