Creating Crosstab Queries in Microsoft Access:
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.
When to use a Microsoft Access Crosstab Query
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.
Examining the design of the Crosstab Query
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.
The Microsoft Access Crosstab Query Wizard
I generally prefer to create a crosstab query myself. That way I don't have to bother modifying a query the wizard makes. However, you can 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:
- Row Heading field: The name of the field selected as the row heading begins the first column in the query results datasheet. Each value in this field is displayed in the first column as a row title.
- Column Heading field: The values in the field selected as the column heading are displayed as headings for the remaining columns in the query results datasheet.
- Value field: The values in the selected field are summarized by the values in the rows and columns. You must designate the summary operation to be performed. For example, you might want to sum or average the values in a field.
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.
Crosstab Query Limitations
- To use multiple tables for the crosstab query, you will first need to create a separate query that has the tables required in it. The Crosstab Query Wizard will only allow you to select one table or one query for the row and column headings.
To work around this limitation: you will need to create a query that includes all of the fields from the multiple tables and save this as an intermediate query. You can then use this as the record source for the crosstab query wizard.
- You can not specify limiting criteria whilst using the crosstab query wizard.
To work around this limitation: Create the crosstab query using the wizard and then go into the query design to apply the criteria.
- Column Headings or Column Orders can not be specified whilst using the wizard
To work around this limitation: Again, create the crosstab query using the wizard and then go into the query design and modify it.
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:
- The source for the query (either a table or query)
- The field(s) to be used as the Row Heading(s)
- The field to be used for the Column Heading
- The field to be used as the Value field
- The Title for the query.
After selecting these options, Microsoft Access will create the Crosstab Query and run it for you.
Creating an Annual 12 Month Summary Report without VBA Code by Creatively Using a Microsoft Access Crosstab Query
Microsoft Access reports reference field names directly. If we want to use a crosstab query as the RecordSource of a report, its column names should not change (unless we want to write a lot of code to handle that).
Fortunately, there's an easy way to make sure our crosstab query always generates 12 columns of monthly data even if the table doesn't have data for all 12 months.
Take a look at the query qryCountryMonthlySalesDateXTAB with [StartDate] parameter in the criteria. This prompts the user for the date of the first month (e.g. 7/1/96) to define the 12 months we want to retrieve:
Query design of qryCountryMonthlySalesDateXTAB
Use Numbers Rather than Specific Month Names
In the original crosstab query qryCountryMonthlySalesXTAB, each month column is shown in yyyy-mm format. That makes it difficult for a report (or subsequent query) to use as the date range changes.
A better approach is to define each month as a number between 1 and 12, starting with the first month to display. This is defined in the ad hoc query qryCountryMonthlySalesDateXTAB.
The trick is to think of each month as a unique number starting from Year 0:Year([OrderDate])*12+Format([OrderDate],"mm") - (Year([StartDate])*12+Format([StartDate],"mm")) + 1
The equation defines each month as a number (Years * 12 + Month) and subtracts the StartDate month from the OrderDate month plus one. The result is a month number from 1 to 12 defining the months we want. The result is this:
Use the PIVOT IN Clause to Specify Required Column Names
Defining the month number is the first step and works great if you always have data for each month. However, if a month doesn't have any data, the crosstab won't create that field. We want to ensure every month from 1 to 12 exists.
This is accomplished by using the IN clause in the crosstab syntax. To do this, we need to edit the SQL string of the query and insert the IN clause. Here's the query:
PARAMETERS StartDate DateTime;
TRANSFORM Sum(([UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS Sales
SELECT Orders.ShipCountry FROM Orders
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID WHERE (((Orders.OrderDate)>=[StartDate]))
GROUP BY Orders.ShipCountry
The important part is the last line highlighted in red. By adding the IN clause and listing the column names required, the crosstab always creates those columns. The IN clause also limits columns (e.g. month 13 and higher are not included).
To make summary data in Access easier to read and understand, consider using a crosstab query. A crosstab query calculates a sum, average, or other aggregate function, and then groups the results by two sets of values— one set on the side of the datasheet and the other set across the top. On the ribbon, click Create, and then in the Queries group, click Query Wizard. In the New Query dialog box, double-click Crosstab Query Wizard.
If one set of headings has date values, the wizard will help you group the records by standard intervals – such as months or quarters.
In this article
Note: Crosstab queries aren’t available in an Access web apps, and are only partially supported in a web database: they can’t be used by any web objects.
A crosstab query is a type of select query. When you run a crosstab query, the results display in a datasheet that has a different structure from other types of datasheets.
The structure of a crosstab query can make it easier to read than a simple select query that displays the same data, as shown in the following illustration.
1. This select query groups summary data vertically by employee and category.
2. A crosstab query can display the same data, but groups the data both horizontally and vertically so that the datasheet can be more compact and easier to read.
When you create a crosstab query, you specify which fields contain row headings, which field contains column headings, and which field contains values to summarize. You can use only one field each when you specify column headings and values to summarize. You can use as many as three fields when you specify row headings.
You can also use an expression to produce row headings, column headings, or values to summarize. See SQL Aggregate Functions for more information.
1. One, two, or three columns on this side contain row headings. The names of the fields that you use as row headings appear in the top row of these columns.
2. The row headings appear here. The number of rows in the crosstab datasheet can grow quickly when you use more than one row heading field, because each combination of row headings is displayed.
3. The columns on this side contain column headings and summary values. Note that the name of the column heading field does not appear on the datasheet.
4. Summary values appear here.
Methods for creating your crosstab query
Use the Crosstab Query Wizard The Crosstab Query Wizard is usually the fastest and easiest way to create a crosstab query. It does most of the work for you, but there are a few options that the wizard does not offer.
The wizard has these benefits:
It is easy to use. To use it, you start the wizard, and then answer a series of guided questions.
It can automatically group dates into intervals. If you use a field that contains date/time data for column headings, the wizard also helps you group the dates into intervals, such as months or quarters.
Tip: If you want to use values from a Date/Time field for column headings, but want to group the dates into intervals that the wizard does not provide, such as fiscal year or biennium, do not use the wizard to create your query. Instead, create the crosstab query in Design view, and use an expression to create the intervals.
It can be used as a starting point. You can use the wizard to create the basic crosstab query that you want, and then fine-tune the query's design by using Design view.
However, by using the wizard, you cannot:
Use more than one table or query as a record source.
Use an expression to create fields.
Add a parameter prompt.
Specify a list of fixed values to use as column headings.
At the last step of the wizard, you can choose to modify the query in Design view. This allows you to add query design elements that the wizard does not support, such as additional record sources.
Work in Design view Design view allows you more control over your query design. It supports the features that are not available in the wizard.
Consider using Design view to create your crosstab query if you want to:
Have more control over the process. The wizard makes some decisions for you.
Use more than one table or query as a record source.
Add a parameter prompt to your query.
Use expressions as fields in your query.
Specify a list of fixed values to use as column headings.
Practice using the design grid.
Write a query in SQL view You can write a crosstab query in SQL view, if you prefer. However, you cannot specify parameter data types by using SQL view. If you want to use a parameter in your crosstab query, you must specify the parameter data type by modifying your query in Design view.
Tip: Remember — you are not limited to using only one method for creating a crosstab query. You can use the wizard to create the query, and then use Design view to modify the query design.
Top of Page
Create a crosstab query by using the Crosstab Query Wizard
Using the Crosstab Query Wizard requires that you use a single table or query as the record source for your crosstab query. If a single table does not have all the data that you want to include in your crosstab query, start by creating a select query that returns the data that you want. For more information about creating a select query, refer to the See Also section.
For this example, we'll create a crosstab query using the Products table from the Northwind sample database. We want to display a count of the number of products in each category from all the suppliers.
On the Create tab, in the Queries group, click Query Wizard.
In the New Query dialog box, click Crosstab Query Wizard, and then click OK.
The Crosstab Query Wizard starts.
On the first page of the wizard, choose the table or query that you want to use to create a crosstab query. For this example, select the Products table and then click Next.
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.Value and then click the button labeled with a > symbol. 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).
If the field that you choose to use for column headings has the Date/Time data type, the wizard adds a step that lets you specify how to group the dates into intervals, such as months or quarters.
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. Click Next to continue.
If you choose a Date/Time field for column headings, the next page of the wizard asks you to specify the interval to use to group the dates. You can specify Year, Quarter, Month, Date, or Date/Time. If you do not choose a Date/Time field for column headings, the wizard skips this page.
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. Click Next to continue.
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.
Top of Page
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. For more information about creating a select query, refer to the See Also section.
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.
1. The settings in these rows determine whether the field is a row heading, column heading, or summary value.
2. This setting displays the field's values as row headings.
3. This setting displays the field's values as column headings.
4. These settings produce the summary values.
Create the query
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.
Close the Show Table dialog box.
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 apply the criterion ='blue' to the column heading field, the crosstab still displays a column for red and a column for green, but only the column for blue contains data.
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.
You cannot specify criteria for or sort on a summary values field.
On the Design tab, in the Results group, click Run.
Specify fixed values for column headings
If you want to specify fixed values to use for column headings, you can set the query's Column Headings property.
Open the crosstab query in Design view.
If the property sheet is not visible, press F4 to display it.
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. If you use these characters in your list of values, Access substitutes an underscore (_) for each such character.
SQL syntax for a crosstab query
A crosstab query is expressed in SQL as a TRANSFORM statement. A TRANSFORM statement has the following syntax:
PIVOT pivotfield [IN (value1[, value2[, ...]])]
The TRANSFORM statement has these parts:
An SQL aggregate function that operates on the selected data.
A SELECT statement.
The field or expression you want to use to create column headings in the query's result set.
Fixed values used to create 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. For more information about creating a select query, refer to the See Also section.
On the Create tab, in the Other group, click Query Design.
Close the Show Table dialog box.
On the Design tab , in the Views group, click View, and then click SQL View.
In the SQL object tab, type or paste the following SQL:
On the first line, after TRANSFORM, type an expression to use to calculate summary values; for example, Sum([Amount]).
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].[Amount]).
On the second line, after SELECT, type a list of fields or field expressions that you want to use for row headings. Separate the list items by using commas; for example, [Budget].[Dept_ID], [Expense].[Type].
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.
On the fourth line, after GROUP BY, type the same list of fields that you used in the SELECT clause, in step 6.
On the fifth line, after PIVOT, type a field name or expression that you want to use for column headings; for example, PIVOT [Budget].[Year].
To add a sort order to a crosstab query in SQL view, use an ORDER BY clause.
Insert a line between the GROUP BY clause and the PIVOT clause.
On the new line, type ORDER BY followed by a space.
Type the field name or expression that you want to sort on; for example, ORDER BY [Expense].[Expense_Class]
By default, an ORDER BY clause sorts values in ascending order. 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. Sorting occurs in the order that the fields or expressions appear in the ORDER BY clause.
At the end of the PIVOT clause in SQL view of your crosstab query, type IN, followed by a comma-separated list of values (enclosed in parentheses) to use as column headings. For example, IN (2007, 2008, 2009, 2010) produces four column headings: 2007, 2008, 2009, 2010.
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.
Insert a new line after the FROM clause in SQL view of your crosstab query.
Type WHERE followed by a field criterion.
If you want to use additional criteria, you can use the AND and OR operators to extend your WHERE clause. 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.
Tip: If you want to create intervals with a Date/Time field, consider using the Crosstab Query Wizard. The wizard allows you to group dates into Year, Quarter, Month, Date, or Date/Time intervals. If none of these intervals is what you want, you should create your crosstab query in Design view, and then use the technique described in this section to create the intervals that you want.
Open the crosstab query in Design view.
In the query design grid, in the Field row, right-click an empty column, and then click Zoom on the shortcut menu.
In the Zoom box, type a field alias, followed by a colon (:).
Inside the parentheses that follow IIf, type a comparison expression that defines the first range of field values.
For example, suppose you are creating ranges for an Age field, and you want each range to be twenty years. The comparison expression for the first range is [Age]<21.
Type a comma after the comparison expression, and then type a name for the range, enclosed in quotation marks. The name you supply is the crosstab heading for values that fall within the range.
For example, after [Age]<21, type a comma and then type "0-20 years".
Type a comma after the range name (outside the quotation marks), and then do one of the following:
To create another range, type IIf() and then repeat steps 5, 6 and 7.
For the last range, type just a name for the range.
For example, a complete nested IIf expression that separates an Age field into twenty-year ranges might look like this (line breaks were added for readability):
Note: When Access evaluates the expression, it stops evaluating as soon as one of the IIf statements evaluates to true. You do not need to specify the lower end of each range, because any value that falls below the lower end of a given range would already have evaluated as true.
In the query design grid, in the Total row, select Group By.
In the Crosstab row, specify whether to use the ranges as row headings or column headings. Remember that you can specify from one to three row headings, and one column heading.
You may want your crosstab query to prompt for input when it is run. For example, suppose that you are using several row headings, one of which is Country/Region. Rather than always displaying data for every country or region, you may want your query to prompt for a name and then display data according to the user's input.
You can add a parameter prompt to any row heading field.
Note: You can also add a parameter prompt to a column heading field, but this will not limit the columns that appear.
Open the crosstab query in Design view.
In the Criteria row for the row heading field for which you want to prompt the user for input, type question text enclosed in square brackets. The question text will appear as a prompt when the query is run.
For example, if you type [Which country or region?] in the Criteria row, when the query runs, a dialog box appears that contains the question "Which country or region?," an input box, and an OK button.
Tip: If you want your parameter to be flexible, concatenate your expression with wildcards, by using the Like operator. For example, instead of using [Which country or region?] as your criterion, you can use Like [Which country or region?]&"*" to make the parameter match a greater range of input. Using Like does not change the appearance of the parameter prompt.
On the Design tab, in the Show/Hide group, click Parameters.
In the Query Parameters dialog box, in the Parameters column, enter the same parameter prompt that you used in the Criteria row. Include the square brackets, but do not include any concatenated wildcards or the Like operator.
In the Data Type column, select the data type for the parameter. The data type should match the data type of the row heading field.
If the field that you use to calculate summary values in your crosstab query contains null values, those values are ignored by any aggregate function that you use. For some aggregate functions, your results can be affected. For example, to calculate an average, you add all of the values and divide the result by the number of values. But if the field contains any null values, those null values are not counted as part of the number of values.
In some cases, you may prefer to replace any null values with zeroes so that the values will be counted during aggregate calculations. You can use the Nz function to replace null values with zeroes.
Nz(variant [, valueifnull ] )
The Nz function syntax has these arguments:
Required. A variable of data type Variant.
Optional (unless used in a query). A Variant that supplies a value to be returned if the variant argument is Null. This argument enables you to return a value other than zero or a zero-length string.
Note: If you use the Nz function in an expression in a query without using the valueifnull argument, the results will be a zero-length string in the fields that contain null values.
With the query open in Design view, in the query design grid, right-click the Value field.
On the shortcut menu, click Zoom.
In the Zoom box, surround the field name or expression with parentheses, and then type Nz in front of the parentheses.
Type , 0 just inside the right parenthesis.
For example, if you used Nz with a field called "Hours Lost" to turn null values into zeroes, your finished expression would look like this:
Nz([Hours Lost], 0)
Keep it simple Crosstab queries can become difficult to read as the number of row combinations grows. Don't use more row headings than you need.
Consider building your crosstab in steps Don't limit yourself to using only tables. You can often start by building a totals query and then using that query as the record source for a crosstab query.
Choose your column heading field carefully Crosstab datasheets tend to be easier to read when the number of column headings stays relatively small. Once you have identified the fields that you want to use as headings, consider using the field that has the fewest distinct values to produce column headings. For example, if your query calculates a value by age and gender, consider using gender for column headings instead of age because gender typically has fewer possible values than age.
Use a subquery in a WHERE clause You can use a subquery as part of a WHERE clause in a crosstab query.
Top of Page
Create a simple select query
Join tables and queries
How to use the Crosstab Query Wizard in Access
Create a crosstab query within minutes using the Query Wizard.
A crosstab query is a special type of query that calculates a sum, average, or other aggregate function, and then groups the results by two sets of values — one down the left side of the datasheet and the other across the top.
You specify which fields are to be row headings, which field contains column headings, and which field contains the values to summarize.
Below, I'll demonstrate how to use the Crosstab Query Wizard to create a crosstab query.
Crosstab ms access
How do I... create a crosstab query in Microsoft Access?
This article is also available as a PDF download, along with a sample database.
Crosstab queries can be useful when you need to cross-reference grouped data and summarize those groupings in a meaningful way. You may not need them on an everyday basis, but they provide a handy solution in certain situations. Let's look at what's involved in creating these unique queries. Then, we'll work though an example to see one in action.
What is a crosstab query?
Crosstab queries are data retrieval queries used primarily for reporting purposes. They differ from typical SELECT queries in Microsoft Access in that they can pivot aggregated row data into a columnar format. The one prerequisite to this query is that the data must be aggregated in some fashion. The results from these queries are somewhat similar to spreadsheets in their format. However, the meaningful data is represented in columns, rather than rows.
The crosstab rundown
The idea of pivoting your data can be somewhat confusing at first, but once you grasp a few simple points, you'll be well on your way to writing your own.
Crosstab queries require a little bit of planning. That's not to say that all queries don't require some type of planning, but crosstab query design is somewhat different, and you need to follow a couple of rules when you create them:
- Crosstab queries are always aggregated. The aggregations must be performed to provide summary data based upon required groupings for the crosstab format.
- When you perform a crosstab query, you must provide at least three forms of crosstab option information. You must specify one or more Row Headings, one Column Heading, and one Value option.
Row Heading: This crosstab option is represented vertically in your dataset. Good candidates for this grouping are product types or other categories of data you want to aggregate. You can have multiple row heading columns, so multiple column aggregations are allowed.
Column Heading: This crosstab option is represented horizontally in your dataset. Good candidates for this grouping are sales quarters or other categories of data for which you want only one grouping aggregation, because only one column heading is allowed in a crosstab query.
Value: This crosstab option is the data that's typically summarized in your crosstab query. It's the product of cross-referencing your Row Heading(s) and your Column Heading aggregation.
This should give you a reasonable idea of how crosstab queries work, but if you're still a little confused, don't fret. An example will clear things up.
A crosstab example
For this example, we're going to look at is a report regarding company sales transactions. First, we'll need to create a table to hold our sales. We'll call it SalesTransaction. It's a relatively simple table that contains the basic key elements most sales tables have: a type of product, the sale price of the product, and the date of the sales transaction. Figure A shows the design of the table in Microsoft Access. You can download a sample database containing this table if you want to follow along with this example.
I created dummy data for the SalesTransactions table so that we can produce some queries from it. To create the data, I used what's known as a Cartesian Product (Cross Join). The query named qryCreateSampleData in the sample database will introduce you to this useful tool for creating dummy data for testing purposes.
Now that we have a structure and table to query, we're ready to create our crosstab query. Open a new query window and add the SalesTransactions table to the query designer. Then, navigate to the Query Type drop-down list and select Crosstab Query, as shown in Figure B. You'll notice that when you select this query option, the aggregations are instantly flipped on.
Our goal is to present to management a report detailing product sales by quarter. Of course, we could present this information through a typical SELECT query; however, the data would be much more difficult to absorb and would probably require some massaging by management to present the numbers in a more meaningful way. This is what's great about crosstab queries: They enable you to present data in a way that's easier to read and understand.
Because we're going to look at product sales by quarter, we need to aggregate data by our ProductType and our TransactionDate columns. How you display the data depends on your preference, but when I'm running crosstab queries based upon quarters, months, or years, I typically like to present date data as column headers, which will leave me to display the product data as a row heading.
Once we've set our Row Heading and Column Heading options, we need to decide which Value crosstab option to use. We'll use the ProductPrice field here because we want to see sales information, which means we'll need to SUM that data.
Figure C shows our query. Notice that we are using two Row Heading crosstab options. Adding the ProductPrice again and summing it as a row heading will actually act as a summary field for the Sales Quarters figures.
Figure D shows the output from our crosstab query. As you can see, we've accomplished what we set out to do. The TotalSales column is the summation of all four quarters of product sales for the specific ProductType.
Although crosstab queries aren't appropriate for all situations, they're worth their weight in gold when applicable. They enable you to cross-reference grouped data and summarize those groupings in a meaningful way. Happy querying!
Crosstab query techniques
Microsoft Access Tips for Serious Users
Provided by Allen Browne Created: August 2006 Last updated: April 2010.
This article explains a series of tips for crosstab queries.
A crosstab query is a matrix, where the column headings come from the values in a field. In the example below, the product names appear down the left, the employee names become fields, and the intersection shows how many of this product has been sold by this employee:
To create this query, open the Northwind sample database, create a new query, switch to SQL View (View menu), and paste:
TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity SELECT Products.ProductID, Products.ProductName, Sum([Order Details].Quantity) AS Total FROM Employees INNER JOIN (Products INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID) ON Employees.EmployeeID = Orders.EmployeeID GROUP BY Products.ProductID, Products.ProductName PIVOT [Employees].[LastName] & ", " & [Employees].[FirstName];
Display row totals
To show the total of all the columns in the row, just add the value field again as a Row Heading.
In the example above, we used the Sum of the Quantity as the value. So, we added the Sum of Quantity again as a Row Heading - the right-most column in the screenshot. (The total displays to the left of the employee names.)
In Access 2007 and later, you can also show the total at the bottom of each column, by depressing the Totals button on the ribbon. The button is on the Records group of the Home tab, and the icon is an upper case sigma (Σ).
Display zeros (not blanks)
Where there are no values, the column is blank. Use Nz() if you want to show zeros instead. Since Access frequently misunderstands expressions, you should also typecast the result. Use CCur() for Currency, CLng() for a Long (whole number), or CDbl() for a Double (fractional number.)
Type the Nz() directly into the TRANSFORM clause. For the example above, use:
TRANSFORM CLng(Nz(Sum([Order Details].Quantity),0)) AS SumOfQuantity
A query can ask you to supply a value at runtime. It pops up a parameter dialog if you enter something like this:
[What order date]
Or, it can read a value from a control on a form:
But, parameters do not work with crosstab queries, unless you:
a) Declare the parameter, or
b) Specify the column headings.
To declare the parameter, choose Parameters on the Query menu. Access opens a dialog. Enter the name and specify the data type. For the examples above, use the Query Parameters dialog like this:
|[What order date]||Date/Time|
|[ OK ]||[ Cancel ]|
Declaring your parameters is always a good idea (except for an Access bug in handling parameters of type Text), but it is not essential if you specify your column headings.
Specify column headings
Since the column headings are derived from a field, you only get fields relevant to the data. So, if your criteria limits the query to a period when Nancy Davolio made no sales, her field will not be displayed. If your goal is to make a report from the crosstab, the report will give errors if the field named "Davolio, Nancy" just disappears.
To solve this, enter all the valid column headings into the Column Headings property of the crosstab query. Steps:
- In query design view, show the Properties box (View menu.)
- Locate the Column Headings property. (If you don't see it, you are looking at the properties of a field instead of the properties of the query.)
- Type in all the possible values, separated by commas. Delimit text values with quotes, or date values with #.
For the query above, set the Column Headings property like this (on one line):
"Buchanan, Steven", "Callahan, Laura", "Davolio, Nancy", "Dodsworth, Anne", "Fuller, Andrew", "King, Robert", "Leverling, Janet", "Peacock, Margaret", "Suyama, Michael"
Side effects of using column headings:
- Any values you do not list are excluded from the query.
- The fields will appear in the order you specify, e.g. "Jan", "Feb", "Mar", ...
Where a report has a complex crosstab query as its Record Source, specifying the column headings can speed up the design of the report enormously. If you do not specify the column headings, Access is unable to determine the fields that will be available to the report without running the entire query. But if you specify the Column Headings, it can read the field names without running the query.
An alternative approach is to alias the fields so the names don't change. Duane Hookom has an example of dynamic monthly crosstab reports.
Multiple sets of values
What if you want to show multiple sets of values at each matrix point? Say the crosstab shows products at the left, and months across the top, and you want to show both the dollar value and number of products sold at each intersection?
One solution is to add another unjoined table to get the additional set of columns in your crosstab (a Cartesian product.) Try this example with the old Northwind sample database:
- Create a table with one Text field called FieldName. Mark the field as primary key. Save the table with the name tblXtabColumns.
- Enter two records: the values "Amt" and "Qty" (without the quotes.)
- Create a new query, and paste in the SQL statement below:
TRANSFORM Sum(IIf([FieldName]="Qty",[Quantity],[Quantity]*[Order Details]![UnitPrice])) AS TheValue
FROM tblXtabColumns, Products INNER JOIN (Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID
WHERE (Orders.OrderDate Between #1/1/1998# And #3/31/1998#)
GROUP BY Products.ProductName
PIVOT [FieldName] & Month([OrderDate]);
The query will look like this:
It generates fields named Amt and the month number, and Qty and the month number:
You can then lay them out as you wish on a report.
For an alternative approach, see kb 304458.
- Sonic head pixel art
- Sprint cup winner
- Skin magical soap
- Heart of azeroth powers
- Civil beat
- Dr bahrami dentist
- Pokemon clockwork
- Half life train
- University of virginia decal