TRANSFORM Statement Example

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);