Question

Provide a specific example when you would use a cross-tab query. Also, why would a cross-tab query be beneficial within the example that you provide?

Answer 1

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:

EE tblProjectTime: Table Period End | ID | Project ID | Hours- 14/01/1999 EN1-05 31-450-TO 12 14/01/1999 EN1-05 40-550-TC 14/01/1999 EN1-10 36-124-TC 14/01/1999 ENI-24 38-250-TC 14/01/1999 EN1-34 31-450-TC 14/01/1999 ENI-76 38-250-TC 14/01/1999 EN1-76 40-550-TC 14/01/1999 EN1-77 36-124-TC Record: 1 65 64 70 35 20 | 40의 of 72

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.

ojectTime:Crosstab Query Project ID Total Hours January February March 31-450-TC 102 91 288 36-124-TC 610 200 207 203 8978 92 38-100-TC 259 38-250-TC 714243 241 230 3 325 108 40-550-TC 112 105 42-390-TC 602 163 220 219

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.