|< Day Day Up >|
Creating a Crosstab Query with SQL TRANSFORM
A crosstab query is a great way to present a lot of data in a consistent and easy-to-read format. These queries summarize data by categories, generally using an aggregate function for the summary calculations. The general rule of thumb is that the query must have at least one column heading and one summary column.
Crosstab queries are produced by SQL's TRANSFORM statement, which takes the form
TRANSFORM aggregate SELECT statement PIVOT column
where aggregate is one of many aggregate functions used to evaluate a column, statement is any valid SQL statement, and column identifies the column heading(s). TRANSFORM doesn't support the SQL HAVING clause.
Crosstabs can be a bit intimidating, but there's an easy way to get the results you want:
There's no guarantee the results will be exactly what you want, but they will probably be very close. After you have the basic structure, you can more easily add column headings and column-evaluating aggregate functions.
The following statement presents a lot of information:
TRANSFORM Sum([HourlyRate]*[Hours]) AS Total SELECT Clients.Client, Projects.ProjectName FROM Clients INNER JOIN Projects ON Clients.ClientID = Projects.ClientID INNER JOIN Tasks ON Projects.ProjectID = Tasks.ProjectID INNER JOIN Timeslips ON Tasks.TaskID = Timeslips.TaskID GROUP BY Clients.Client, Projects.ProjectName PIVOT Projects.StartDate
The results show a list of clients and projects with a total of labor hours listed by columns according to start dates. In other words, you can quickly see how much money has been spent thus far on each project for each client, while quickly discerning when the project began. Because this query retrieves data from more than one table, the references include table and column names, which prevent ambiguous references, such as the ClientID column in the Clients and Projects tables.
|< Day Day Up >|