PARAMETERS [Sales for which year?] Long; TRANSFORM Sum([Order Amount]) AS Sales SELECT [Product Name] FROM Orders, Products, [Order Details], Orders INNER JOIN [Order Details] ON Orders.[Order ID] = [Order Details].[Order ID], Products INNER JOIN [Order Details] ON Products.[Product ID] = [Order Details].[Product ID] WHERE DatePart("yyyy", [Order Date]) = [Sales for which year?] GROUP BY [Product Name] ORDER BY [Product Name] PIVOT DatePart("m", [Order Date])
| Creates a crosstab query that shows product sales by month for an application-specified year. The months appear from left to right (pivoted) as columns, and the product names appear from top to bottom as rows.
|
PARAMETERS [Sales for which year?] Long; TRANSFORM Sum([Order Amount]) AS Sales SELECT [Company Name] FROM Orders, Products, [Order Details], Suppliers, Orders INNER JOIN [Order Details] ON Orders.[Order ID] = [Order Details].[Order ID], Products INNER JOIN [Order Details] ON Products.[Product ID] = [Order Details].[Product ID], Suppliers INNER JOIN Products ON Suppliers.[Supplier ID] = Products.[Supplier ID] WHERE DatePart("yyyy", [Order Date]) = [Sales for which year?] GROUP BY [Company Name] ORDER BY [Company Name] PIVOT "Qtr " & DatePart("q", [Order Date]) In ('Qtr1', 'Qtr2', 'Qtr 3', 'Qtr 4')
| Creates a crosstab query that shows product sales by quarter for each supplier for an application-specified year. The quarters appear from left to right (pivoted) as columns, and the supplier names appear from top to bottom as rows.
|