Builder for Microsoft Access
Need to see key data from your Access database in a single dashboard? Tired of creating query after query to really understand your Access data? Dashboard Builder for Microsoft Access is an easy to use tool to create powerful dashboards in minutes…without being a SQL wizard. Free trial available
A Microsoft Access crosstab query presents summary information in a compact format that is similar to a spreadsheet. These types of queries can present a large amount of summary data in a format that is usually simpler to analyse than viewing the information in a database form. Each attribute (field) in a table typically contains a category of data. A crosstab query summarizes the data from one or more of these fields that are separated into groups based on one or more fields.
For example, if you wanted to analyse the number of hours per project per month, it might be difficult to use a select query because you would have to scroll through the records of the many employees who had worked on various projects, and then try to make a comparison between the projects. A crosstab query would reduce the number of records presented by adding up the total hours per individual project.
In the crosstab query, which is a special type of Totals query, the Total row that appears in the query design grid will always be active. You can not toggle the Total row off when using a crosstab query.
If we look at the following examples, that show both a table (containing information on Project Times) and the crosstab query view, you will see how the crosstab query presents the summary information based upon that table:
The Project Time table, that will be used as the source for the crosstab query.
The crosstab query, presenting the summary information that is based upon the previous table.
Note how the crosstab query has summarized the data from 72 records contained in the table into 6 records in the query. This now presents the Total Hours worked on each Project and gives a further breakdown of how many Hours have been worked each Project per Month. This provides a much easier way to view the information.
If we view the design of the previous crosstab query we will see that it uses information from the ProjectTime table and it summarizes the Hours worked per Project per Month. It displays the information as one record per Project.
The Crosstab row, visible in the example below, is inserted between the queries Total row and Sort row in the design grid. This designates the Row and Column Headings and Values that will be summarized in the crosstab query.
The Group By clause is specified in both the Total cells of the Row and Column Headings and an aggregate Total cell (Sum, in this case) is set for the Value field.
The design of the crosstab query, showing the Crosstab row and the Group By and Aggregate settings.
A crosstab query can be produced by you, however, it is usually quicker to make use of the Crosstab Query Wizard available in Microsoft Access. The Crosstab Query Wizard has some limitations, but you can work around most of them by having the Wizard create the query and then modifying it to suit your needs.
Which ever way you decide to go, you must specify the following three items:
In a crosstab query, you can specify only one Value field and one Column Heading field, but you can have multiple Row Heading fields. For example, you might want to know who at what company ordered how many of what product. In that case, you could designate Company and Contact as Row Headings, Product as Column Heading, and Quantity as Value.
To run the Crosstab Query Wizard, click on the New button in the Query Objects section of the database window and select the Crosstab Query Wizard from the New Query dialog box - you can then follow the wizard prompts:
After selecting these options, Microsoft Access will create the Crosstab Query and run it for you.