What Type of Query Will Allow You to See Statistics by Category

To brand summary data in Access easier to read and sympathise, consider using a crosstab query. A crosstab query calculates a sum, average, or other amass function, and then groups the results past two sets of values— one prepare on the side of the datasheet and the other set across the elevation. On the ribbon, click Create, and and then in the Queries group, click Query Wizard. In the New Query dialog box, double-click Crosstab Query Magician.

If one set up of headings has date values, the wizard volition help yous group the records past standard intervals – such as months or quarters.

In this article

• Overview

• Create a crosstab query by using the Crosstab Query Wizard

• Create a crosstab query in Design view

• Create a crosstab query in SQL view

• Utilise ranges or intervals for headings

• Prompt for a parameter to limit row headings

• Replace cipher values with zeroes

• Crosstab query tips

Note:Crosstab queries aren't available in an Access spider web apps, and are only partially supported in a web database: they can't be used by any web objects.

Overview

A crosstab query is a type of select query. When you run a crosstab query, the results brandish in a datasheet that has a dissimilar construction from other types of datasheets.

The structure of a crosstab query can make information technology 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 information both horizontally and vertically so that the datasheet tin can exist more compact and easier to read.

When yous create a crosstab query, yous specify which fields incorporate row headings, which field contains cavalcade headings, and which field contains values to summarize. Yous can apply just i field each when you lot specify column headings and values to summarize. You tin use as many as three fields when yous specify row headings.

Y'all can also use an expression to produce row headings, cavalcade headings, or values to summarize. See SQL Aggregate Functions for more than information.

ane. I, two, or 3 columns on this side contain row headings. The names of the fields that you employ every bit row headings announced in the top row of these columns.

2. The row headings appear here. The number of rows in the crosstab datasheet can grow chop-chop when y'all employ 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 proper noun 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 unremarkably the fastest and easiest way to create a crosstab query. It does about of the work for you lot, only at that place are a few options that the wizard does non offering.

The wizard has these benefits:

• It is easy to apply. To use it, you start the wizard, and 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 cavalcade headings, the wizard as well helps you group the dates into intervals, such as months or quarters.

  Tip:If yous want to use values from a Date/Fourth dimension field for column headings, but want to grouping the dates into intervals that the magician does not provide, such as fiscal yr 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 exist used equally a starting indicate. Y'all tin can apply the wizard to create the basic crosstab query that yous want, and so fine-tune the query'due south design past using Design view.

Withal, by using the wizard, you lot cannot:

• Apply more than ane table or query every bit a record source.

• Use an expression to create fields.

• Add together a parameter prompt.

• Specify a list of fixed values to use as cavalcade headings.

  At the terminal footstep of the wizard, you tin 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.

Piece of work in Design view     Design view allows you more command over your query design. Information technology supports the features that are non available in the wizard.

Consider using Blueprint view to create your crosstab query if y'all desire to:

• Have more control over the process. The wizard makes some decisions for you.

• Use more than ane table or query as a tape source.

• Add a parameter prompt to your query.

• Employ expressions as fields in your query.

• Specify a list of fixed values to employ 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 lot prefer. Even so, yous cannot specify parameter data types past using SQL view. If you lot desire to utilise a parameter in your crosstab query, you must specify the parameter data type by modifying your query in Pattern view.

Tip:Remember — you are not express to using simply one method for creating a crosstab query. You tin can use the magician to create the query, and and then utilize Design view to modify the query design.

Top of Page

Create a crosstab query past using the Crosstab Query Wizard

Using the Crosstab Query Magician requires that you employ a unmarried table or query equally the record source for your crosstab query. If a single table does non take all the data that you want to include in your crosstab query, commencement past creating a select query that returns the data that you lot desire. For more than data about creating a select query, refer to the Come across Also section.

For this instance, we'll create a crosstab query using the Products table from the Northwind sample database. We desire to display a count of the number of products in each category from all the suppliers.

1. On the Create tab, in the Queries group, click Query Wizard.

   

2. In the New Query dialog box, click Crosstab Query Magician, and and so click OK.

   The Crosstab Query Wizard starts.

3. On the kickoff page of the sorcerer, choose the table or query that you lot desire to use to create a crosstab query. For this instance, select the Products tabular array and then click Adjacent.

   

4. On the next page, choose the field that contains the values that you want to use equally row headings. You can select upwardly to iii fields to utilize as row headings sources, just the fewer row headings you use, the easier your crosstab datasheet volition be to read. If you lot choose more than 1 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 so click the push labeled with a > symbol. Observe that Access displays the field name forth the left side of the sample query preview at the bottom of the dialog box. Click Next to proceed.

   

5. On the next page, choose the field that contains the values that you want to utilise equally cavalcade headings. In general, you should cull a field that contains few values, to help keep your results easy to read. For example, using a field that has merely 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 cavalcade headings has the Appointment/Time data type, the wizard adds a step that lets you specify how to grouping the dates into intervals, such as months or quarters.

   For this example, select Category and notice that Access displays category sample names along the height of the sample query preview at the bottom of the dialog box. Click Next to go along.

   

6. If you choose a Date/Time field for column headings, the next folio of the magician asks you to specify the interval to utilize to grouping the dates. Y'all can specify Yr, Quarter, Month, Date, or Date/Time. If yous do not choose a Engagement/Time field for column headings, the wizard skips this page.

7. On the side by side page, choose a field and a function to utilize to calculate summary values. The information type of the field that you select determines which functions are available.

   On the same folio, select or articulate the Yes, include row sums bank check box to include or exclude row sums.

   If you include row sums, the crosstab query has an additional row heading that uses the aforementioned field and role as the field value. Including a row sum inserts an boosted column that summarizes the remaining columns. For instance, if your crosstab query calculates average age by location and gender (with gender cavalcade headings), the additional cavalcade calculates the average age by location, beyond 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 Yeah, include row sums cheque box selected. Access will create a column that totals the number of products from each supplier. Click Next to go along.

   

8. On the last page of the wizard, blazon a proper noun for your query and then specify whether y'all want to view the results or alter the query design.

   Y'all tin alter the role that is used to produce row sums by editing the crosstab query in Design view.

9. If you've walked through this example using the Products table from the Northwind database, the crosstab query displays the listing of supplier names as rows, the product category names as columns, and a count of the number of products in each intersection.

   

Meridian of Page

Past using Design view to create your crosstab query, y'all can use equally many record sources (tables and queries) as you want. Notwithstanding, y'all can go along the design simple by outset creating a select query that returns all of the data that you want and then using that query equally 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, yous use the Total and Crosstab rows in the design grid to specify which field'southward values will become cavalcade headings, which fields' values will get row headings, and which field'south values to sum, average, count, or otherwise calculate.

one. The settings in these rows determine whether the field is a row heading, column heading, or summary value.

2. This setting displays the field'due south values as row headings.

3. This setting displays the field'southward values as column headings.

4. These settings produce the summary values.

Create the query

1. On the Create tab, in the Queries group, click Query Blueprint.

   

2. In the Evidence Tabular array dialog box, double-click each table or query that you want to use equally a record source.

   If you use more than i record source, make certain 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 Run into Likewise department.

3. Close the Show Table dialog box.

4. On the Pattern tab, in the Query Type group, click Crosstab.

5. In the query blueprint window, double-click each field that y'all want to use as a source of row headings. You tin can select as many as 3 fields for row headings.

6. In the query design grid, in the Crosstab row for each row heading field, select Row Heading.

   Y'all can enter an expression in the Criteria row to limit the results for that field. You can besides utilise the Sort row to specify a sort order for a field.

7. In the query design window, double-click the field that you want to utilize as the source of column headings. You tin select only one field for cavalcade headings.

8. In the query design filigree, in the Crosstab row for the column heading field, select Cavalcade 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 comprise information. For case, suppose you lot accept a cavalcade heading field that has three possible values: ruby, green, and blue. If you apply the criterion ='blueish' to the column heading field, the crosstab nevertheless displays a column for red and a column for green, but only the column for bluish contains information.

   If you lot 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 adjacent section.

9. In the query pattern window, double-click the field that you desire to utilize to calculate summary values. You can select merely ane field to use for summary values.

10. In the query design grid, in the Total row for the summary values field, select an aggregate function to use to calculate the values.

11. In the Crosstab row for the summary values field, select Value.

    Y'all cannot specify criteria for or sort on a summary values field.

12. On the Design tab, in the Results group, click Run.

Specify fixed values for column headings

If y'all want to specify stock-still values to utilize for column headings, you can set the query's Cavalcade Headings holding.

1. Open the crosstab query in Design view.

2. If the property sheet is not visible, printing F4 to display information technology.

3. In the property sheet, just to a higher place the General tab, make certain that the Pick type is Query Properties. If it is not, click an empty spot in the space above the query design grid.

4. In the holding sheet, on the Full general tab, in the Column Headings property, enter a comma-separated listing of values that you want to employ as column headings.

   Some characters (such as most punctuation marks) are not allowed in cavalcade headings. If you lot apply 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 every bit a TRANSFORM statement. A TRANSFORM statement has the following syntax:

TRANSFORM aggfunction
selectstatement
Pin pivotfield [IN (value1[, value2[, ...]])]

The TRANSFORM statement has these parts:

Role	Clarification
aggfunction	An SQL aggregate function that operates on the selected information.
selectstatement	A SELECT statement.
pivotfield	The field or expression you want to utilise to create cavalcade headings in the query's effect set.
value1, value2	Fixed values used to create column headings.

SQL view does not limit the number of tables or queries that y'all can utilise as record sources for a crosstab query. However, you can help keep the design elementary by creating a select query that returns all of the information that yous want to utilize 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 Besides section.

1. On the Create tab, in the Other group, click Query Blueprint.

2. Shut the Show Tabular array dialog box.

3. On the Design tab , in the Views group, click View, so click SQL View.

4. In the SQL object tab, blazon or paste the following SQL:

   TRANSFORM  SELECT  FROM  Grouping Past  PIVOT  ;                  

5. On the showtime line, after TRANSFORM, type an expression to utilize to calculate summary values; for example, Sum([Amount]).

   If y'all are using more than 1 table or query as a record source, include the tabular array or query name equally function of each field name; for example, Sum([Expense].[Amount]).

6. On the second line, later on SELECT, blazon a listing of fields or field expressions that yous want to use for row headings. Separate the listing items past using commas; for example, [Upkeep].[ Dept_ID ], [Expense].[Type].

7. On the third line, afterward FROM, type a listing of the tables or queries that you are using as record sources; for instance, Budget, Expense.

8. On the fourth line, later GROUP By, blazon the same list of fields that you used in the SELECT clause, in step half-dozen.

9. On the fifth line, after PIVOT, blazon a field name or expression that you want to employ for column headings; for example, PIVOT [Upkeep].[Yea r].

To add together a sort guild to a crosstab query in SQL view, employ an Guild BY clause.

1. Insert a line between the GROUP BY clause and the Pin clause.

2. On the new line, type ORDER By followed by a space.

3. Type the field name or expression that you desire to sort on; for case, Social club BY [Expense].[ Expense_Class ]

   Past default, an ORDER By clause sorts values in ascending order. If you want to sort in descending order, type DESC afterward the field name or expression.

4. If you want to sort on an additional field or expression, blazon 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, blazon IN, followed by a comma-separated list of values (enclosed in parentheses) to use every bit cavalcade headings. For case, IN (2007, 2008, 2009, 2010) produces four column headings: 2007, 2008, 2009, 2010.

If you lot specify a fixed value that does non correspond to a field value from the pivot field, that fixed value becomes a cavalcade heading for an empty cavalcade.

1. Insert a new line subsequently the FROM clause in SQL view of your crosstab query.

2. Type WHERE followed by a field criterion.

   If you want to utilize additional criteria, you can use the AND and OR operators to extend your WHERE clause. You lot 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 cavalcade headings. For case, suppose y'all use an "Age" field for column headings. Rather than using a column for each historic period, you may prefer to utilise 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 Sorcerer. The wizard allows you to group dates into Year, Quarter, Month, Date, or Date/Time intervals. If none of these intervals is what you desire, y'all should create your crosstab query in Design view, and then use the technique described in this department to create the intervals that y'all desire.

1. Open the crosstab query in Design view.

2. In the query design filigree, in the Field row, right-click an empty column, so click Zoom on the shortcut carte du jour.

3. In the Zoom box, type a field alias, followed by a colon (:).

4. Blazon IIf().

5. Inside the parentheses that follow IIf, type a comparison expression that defines the get-go range of field values.

   For case, suppose yous are creating ranges for an Age field, and you desire each range to be twenty years. The comparison expression for the first range is [Age]<21.

6. Type a comma after the comparison expression, and so blazon a name for the range, enclosed in quotation marks. The proper name y'all supply is the crosstab heading for values that fall inside the range.

   For case, after [Age]<21, blazon a comma and then type "0-20 years".

7. Blazon a comma afterwards the range proper noun (exterior the quotation marks), so practice one of the following:

   • To create some other range, type IIf() and then echo steps v, six and 7.

   • For the concluding range, type just a name for the range.

     For instance, a consummate nested IIf expression that separates an Age field into twenty-year ranges might await like this (line breaks were added for readability):

     IIf([Historic period]<21,"0-twenty years",  IIf([Age]<41,"21-40 years",  IIf([Historic period]<61,"41-60 years",  IIf([Historic period]<81,"61-80 years", "lxxx+ years"))))                      

     Note:When Access evaluates the expression, it stops evaluating as soon as one of the IIf statements evaluates to true. Yous 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 accept evaluated equally true.

8. In the query design grid, in the Full row, select Group By.

9. In the Crosstab row, specify whether to employ the ranges as row headings or column headings. Call back that you tin specify from ane to three row headings, and 1 cavalcade 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, ane of which is Land/Region. Rather than ever displaying data for every country or region, you lot may desire your query to prompt for a name so display data co-ordinate to the user'south input.

You lot can add a parameter prompt to any row heading field.

Note:You tin also add a parameter prompt to a cavalcade heading field, but this volition non limit the columns that appear.

1. Open the crosstab query in Design view.

2. In the Criteria row for the row heading field for which you want to prompt the user for input, type question text enclosed in foursquare 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 lot want your parameter to be flexible, concatenate your expression with wildcards, by using the Like operator. For case, instead of using [Which country or region?] equally your criterion, y'all can use Similar [Which land or region?]&"*" to make the parameter match a greater range of input. Using Like does not modify the appearance of the parameter prompt.

3. On the Design tab, in the Show/Hide group, click Parameters.

4. In the Query Parameters dialog box, in the Parameters cavalcade, enter the same parameter prompt that you used in the Criteria row. Include the square brackets, but exercise not include whatever concatenated wildcards or the Similar operator.

5. In the Data Type cavalcade, select the data type for the parameter. The information type should friction 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 past whatever aggregate role that you lot use. For some aggregate functions, your results can be afflicted. For example, to calculate an average, you add all of the values and separate the event by the number of values. Simply if the field contains any null values, those null values are not counted equally role of the number of values.

In some cases, you may adopt to replace whatever null values with zeroes so that the values will be counted during aggregate calculations. You tin use the Nz function to supervene upon null values with zeroes.

Nz syntax

Nz ( variant [, valueifnull ] )

The Nz role syntax has these arguments:

Argument	Clarification
variant	Required. A variable of information type Variant.
valueifnull	Optional (unless used in a query). A Variant that supplies a value to exist returned if the variant argument is Null. This argument enables you to render a value other than zilch or a zero-length string. Note:If you lot use the Nz function in an expression in a query without using the valueifnull argument, the results will be a aught-length cord in the fields that contain null values.

1. With the query open in Design view, in the query design grid, right-click the Value field.

2. On the shortcut menu, click Zoom.

3. In the Zoom box, surround the field name or expression with parentheses, and then type Nz in front of the parentheses.

4. Type , 0 but inside the right parenthesis.

   For instance, 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 tin can become difficult to read equally the number of row combinations grows. Don't use more row headings than you demand.

• Consider building your crosstab in steps    Don't limit yourself to using only tables. You tin can often start past edifice a totals query and and then using that query every bit the record source for a crosstab query.

• Choose your column heading field advisedly    Crosstab datasheets tend to exist easier to read when the number of cavalcade headings stays relatively small. In one case you have identified the fields that you want to utilise every bit headings, consider using the field that has the fewest distinct values to produce cavalcade headings. For instance, 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    Yous tin can use a subquery equally office of a WHERE clause in a crosstab query.

Top of Folio

Encounter Also

Create a unproblematic select query

Join tables and queries

healyinedan.blogspot.com

Source: https://support.microsoft.com/en-us/office/make-summary-data-easier-to-read-by-using-a-crosstab-query-8465b89c-2ff2-4cc8-ba60-2cd8484667e8

Bagikan Artikel ini