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. 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.
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. 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. Type a comma after the range name outside the quotation marks , and then do one of the following:. 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.
For more information, see the online Access help for crosstabs. Crosstab is one of the Microsoft Access Query Types:. Crosstab is a Microsoft Access Query Type.
Unlike other query types, the Query Designer for Crosstabs has an extra Crosstab row to specify each of the columns. Columns can be a field or expression. This example shows a crosstab grouped by Country with columns for each month defined as the year-month format of the [OrderDate] , and displaying total sales:.
When you run the query, the summarized sales displayed for each month as a separate column some columns not displayed here :. 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. This prompts the user for the date of the first month e. 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.
The result is a month number from 1 to 12 defining the months we want. The result is this:. 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. Here's the query:. 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. With the crosstab defining each month as a set number, we simply reference those columns in the report:. Report design of rptSalesByMonth. To label each column, we translate the month column based on the [StartDate] parameter with the Format property set to yyyy-mm.
For instance:. The VBA DateAdd function when passed the "m" parameter adds the number of months to the [StartDate] to create the new date that the Format property is applied for display. The result is a nicely formatted monthly summary report that adjusts to any starting month a user enters.
Preview the report and enter the StartDate:. This is because of the IN clause in the crosstab:. Example of missing data as blank columns. If you want to show the column headings with Year-Month e. The need for monthly summary reports is quite common. Hopefully, the techniques shown here will help you create richer Access reports and use crosstabs more efficiently, while giving you more ideas on creating new reports.
Visit the FMS web site for additional Access resources, utilities, technical papers, demos, and product information. The most popular Microsoft Access Number Cruncher. Celebrating our 35th Year of Software Excellence. If you type something that cannot be found in the Query result e. Dec amount , the value will display blank for that column, therefore you can type the months of the whole year. Adding grand total for each grouping is through adding the 2nd Group By but apply aggregate function on that grouping.
As explained, the Grand Total column is a Group By , therefore it must be placed between Row Heading column and the first Value column. It cannot be moved the the right hand, the end of the month in Query Design, but you can move it to the back in Query View. Because normal Query can fully achieve the grouping you need using Group By , I highly recommend you not to use Matrix Report if your requirement is complicated. Read my another post to see how to use Expression to build a Matrix Report.
Your email address will not be published. Save my name, email, and website in this browser for the next time I comment.
0コメント