To create this query, open the Northwind sample database, create a new query, switch to SQL View View menu , and paste:. 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 and later, you can also show the total at the bottom of each column, by depressing the Totals button on the ribbon.
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. 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: [Forms]. There's a Crosstab Query Wizard, but it is somewhat limited with what you can do. In many cases, you are probably better off creating your crosstab query from Design View or starting with the wizard, then modifying it in Design View. This switches the query to a crosstab query. You can see the options in the bottom query pane change when it's in crosstab mode i.
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. Note that the name of the column heading field does not appear on the datasheet. It does most of the work for you, but there are a few options that the wizard does not offer. 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. 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.
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. 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. 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. 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. 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.
0コメント