Microsoft access cross tab query

Total and Crosstab rows appear, and the Show row disappears. As with a normal select query, you can double-click a field in the top pane to make it appear in the bottom pane. However, in this example, we're going to use some of Access's built-in functions to aggregate some fields, and also to display another field in a more readable way. In this example, we use the Format function to display only the month portion of the Order Date. We specify the format that we want the month to appear in i.

We also use the Sum function to perform a calculation on the Unit Price , Quantity , and Discount fields. On the next page, choose the field that contains the values that you want to use as row headings. You can select up to three fields to use as row headings sources, but the fewer row headings you use, the easier your crosstab datasheet will be to read.

If you choose more than one field to supply row headings, the order in which you choose the fields determines the default order in which your results are sorted. For this example, select Supplier IDs. Notice that Access displays the field name along the left side of the sample query preview at the bottom of the dialog box. Click Next to continue. On the next page, choose the field that contains the values that you want to use as column headings.

In general, you should choose a field that contains few values, to help keep your results easy to read. For example, using a field that has only a few possible values such as gender might be preferable to using a field that can contain many different values such as age. For this example, select Category and notice that Access displays category sample names along the top of the sample query preview at the bottom of the dialog box. On the next page, choose a field and a function to use to calculate summary values.

The data type of the field that you select determines which functions are available. On the same page, select or clear the Yes, include row sums check box to include or exclude row sums. If you include row sums, the crosstab query has an additional row heading that uses the same field and function as the field value.

Including a row sum inserts an additional column that summarizes the remaining columns. For example, if your crosstab query calculates average age by location and gender with gender column headings , the additional column calculates the average age by location, across all genders.

For this example, select ID in the Fields box and Count in the Functions box in order to have Access count the number of products in each intersection of supplier and category. Leave the Yes, include row sums check box selected. Access will create a column that totals the number of products from each supplier.

On the last page of the wizard, type a name for your query and then specify whether you want to view the results or modify the query design. You can change the function that is used to produce row sums by editing the crosstab query in Design view.

If you've walked through this example using the Products table from the Northwind database, the crosstab query displays the list of supplier names as rows, the product category names as columns, and a count of the number of products in each intersection. By using Design view to create your crosstab query, you can use as many record sources tables and queries as you want. However, you can keep the design simple by first creating a select query that returns all of the data that you want and then using that query as the only record source for your crosstab query.

When you build a crosstab query in Design view, you use the Total and Crosstab rows in the design grid to specify which field's values will become column headings, which fields' values will become row headings, and which field's values to sum, average, count, or otherwise calculate. The settings in these rows determine whether the field is a row heading, column heading, or summary value. On the Create tab, in the Queries group, click Query Design. In the Show Table dialog box, double-click each table or query that you want to use as a record source.

If you use more than one record source, make sure that the tables or queries are joined on fields that they have in common. For more information about joining tables and queries, refer to the See Also section.

On the Design tab, in the Query Type group, click Crosstab. In the query design window, double-click each field that you want to use as a source of row headings. You can select as many as three fields for row headings. In the query design grid, in the Crosstab row for each row heading field, select Row Heading. You can enter an expression in the Criteria row to limit the results for that field.

You can also use the Sort row to specify a sort order for a field. In the query design window, double-click the field that you want to use as the source of column headings. You can select only one field for column headings. In the query design grid, in the Crosstab row for the column heading field, select Column Heading. You can enter an expression in the Criteria row to limit the results for the column heading field.

However, using a criteria expression with the column heading field does not limit the number of columns returned by the crosstab query. Instead, it limits which columns contain data. For example, suppose you have a column heading field that has three possible values: red, green, and blue. If you want to limit the values that display as column headings, you can specify a list of fixed values by using the query's Column Headings property. For more information, see the next section.

In the query design window, double-click the field that you want to use to calculate summary values. You can select only one field to use for summary values. In the query design grid, in the Total row for the summary values field, select an aggregate function to use to calculate the values.

In the Crosstab row for the summary values field, select Value. On the Design tab, in the Results group, click Run. If you want to specify fixed values to use for column headings, you can set the query's Column Headings property. In the property sheet, just above the General tab, make sure that the Selection type is Query Properties.

If it is not, click an empty spot in the space above the query design grid. In the property sheet, on the General tab, in the Column Headings property, enter a comma-separated list of values that you want to use as column headings. Some characters such as most punctuation marks are not allowed in column headings.

SQL view does not limit the number of tables or queries that you can use as record sources for a crosstab query. However, you can help keep the design simple by creating a select query that returns all of the data that you want to use in your crosstab query, and then using that select query as the record source. On the Create tab, in the Other group, click Query Design. If you are using more than one table or query as a record source, include the table or query name as part of each field name; for example, Sum [Expense].

Separate the list items by using commas; for example, [Budget]. On the third line, after FROM , type a list of the tables or queries that you are using as record sources; for example, Budget, Expense.

If you want to sort in descending order, type DESC after the field name or expression. If you want to sort on an additional field or expression, type a comma and then type the additional field name or expression. For example, IN , , , produces four column headings: , , , If you specify a fixed value that does not correspond to a field value from the pivot field, that fixed value becomes a column heading for an empty column.

You can also use parentheses to group criteria into logical sets. Sometimes, rather than using every value of a field for row or column headings, you want to group the values of a field into ranges and then use those ranges for row or column headings.

For example, suppose you use an "Age" field for column headings. Rather than using a column for each age, you may prefer to use columns that represent age ranges. You can use the IIf Function in an expression to create ranges to use for row or column headings. 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.