How to Read a Summary of a Security

To make summary data in Access easier to read and understand, consider using a crosstab query. A crosstab query calculates a sum, boilerplate, or other aggregate role, then groups the results past two sets of values— one set on the side of the datasheet and the other set across the height. On the ribbon, click Create, 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 volition help you group the records by 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 Blueprint view

• Create a crosstab query in SQL view

• Use ranges or intervals for headings

• Prompt for a parameter to limit row headings

• Replace null values with zeroes

• Crosstab query tips

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

Overview

A crosstab query is a type of select query. When you lot run a crosstab query, the results display in a datasheet that has a unlike structure from other types of datasheets.

The construction of a crosstab query tin make it easier to read than a elementary select query that displays the same data, as shown in the following illustration.

1. This select query groups summary information vertically by employee and category.

2. A crosstab query can display the same data, but groups the data both horizontally and vertically and so that the datasheet tin can be more than compact and easier to read.

When you lot create a crosstab query, yous specify which fields contain row headings, which field contains column headings, and which field contains values to summarize. You can use only i field each when you lot specify column headings and values to summarize. You can utilize every bit many equally three fields when yous specify row headings.

You can also use an expression to produce row headings, column headings, or values to summarize. Meet SQL Aggregate Functions for more information.

1. One, two, or 3 columns on this side contain row headings. The names of the fields that you use as row headings announced in the top row of these columns.

ii. The row headings appear hither. The number of rows in the crosstab datasheet tin can grow quickly when you utilize more than one row heading field, because each combination of row headings is displayed.

3. The columns on this side incorporate column headings and summary values. Notation that the name of the column heading field does non appear on the datasheet.

iv. Summary values appear here.

Methods for creating your crosstab query

Use the Crosstab Query Sorcerer    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 offering.

The wizard has these benefits:

• Information technology is easy to use. To utilise it, you start the wizard, then answer a series of guided questions.

• It can automatically group dates into intervals. If you lot use a field that contains date/fourth dimension information for column headings, the wizard too helps yous group the dates into intervals, such every bit months or quarters.

  Tip:If you lot desire 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 financial year or biennium, do non apply the wizard to create your query. Instead, create the crosstab query in Pattern view, and apply an expression to create the intervals.

• It can be used as a starting point. You can apply the wizard to create the basic crosstab query that you desire, and then fine-melody the query's blueprint past using Design view.

Notwithstanding, by using the sorcerer, yous cannot:

• Employ more than one tabular array or query every bit a tape source.

• Apply an expression to create fields.

• Add together a parameter prompt.

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

  At the last step of the wizard, yous can choose to modify the query in Design view. This allows you lot to add query pattern elements that the wizard does not back up, such as additional record sources.

Work in Blueprint view     Pattern view allows you more command over your query pattern. It supports the features that are not available in the sorcerer.

Consider using Blueprint view to create your crosstab query if you want to:

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

• Use more than one tabular array or query as a record source.

• Add together a parameter prompt to your query.

• Use expressions equally fields in your query.

• Specify a list of fixed values to apply every bit cavalcade headings.

• Practice using the blueprint grid.

Write a query in SQL view    Yous can write a crosstab query in SQL view, if yous 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 blazon past modifying your query in Pattern view.

Tip:Remember — you are not limited to using simply one method for creating a crosstab query. Yous can utilise the magician to create the query, so utilise Blueprint view to modify the query pattern.

Peak of Page

Create a crosstab query by using the Crosstab Query Wizard

Using the Crosstab Query Sorcerer requires that y'all utilize a single table or query every bit 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 past creating a select query that returns the data that you want. For more data about creating a select query, refer to the See Also section.

For this example, nosotros'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 grouping, click Query Magician.

   

2. In the New Query dialog box, click Crosstab Query Wizard, and then click OK.

   The Crosstab Query Wizard starts.

3. On the get-go folio of the wizard, choose the tabular array or query that yous want to use to create a crosstab query. For this example, select the Products table and so click Next.

   

4. On the next folio, choose the field that contains the values that you desire to utilise as row headings. Yous tin can select up to iii fields to utilize as row headings sources, simply the fewer row headings y'all use, the easier your crosstab datasheet will be to read. If y'all choose more than one field to supply row headings, the order in which you choose the fields determines the default club in which your results are sorted.

   For this instance, select Supplier IDs.Value and so click the button labeled with a > symbol. Notice that Access displays the field proper name along the left side of the sample query preview at the bottom of the dialog box. Click Next to proceed.

   

5. On the next folio, choose the field that contains the values that you lot want to use equally cavalcade headings. In general, you should choose a field that contains few values, to help go along your results easy to read. For instance, using a field that has only a few possible values (such every bit gender) might exist preferable to using a field that tin can comprise 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 lesser of the dialog box. Click Next to go along.

   

6. If you choose a Date/Time field for cavalcade headings, the next page of the wizard asks you to specify the interval to use to group the dates. You lot tin can specify Year, Quarter, Month, Date, or Date/Time. If yous do not choose a Date/Fourth dimension field for column headings, the wizard skips this page.

7. On the next folio, cull 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 articulate the Yes, include row sums cheque box to include or exclude row sums.

   If y'all 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 cavalcade that summarizes the remaining columns. For example, if your crosstab query calculates boilerplate age past location and gender (with gender cavalcade headings), the additional column calculates the average age past location, across all genders.

   For this example, select ID in the Fields box and Count in the Functions box in lodge to accept Admission count the number of products in each intersection of supplier and category. Leave the Yeah, include row sums check box selected. Admission will create a column that totals the number of products from each supplier. Click Next to go along.

   

8. On the concluding page of the wizard, type a name for your query and then specify whether y'all desire to view the results or modify the query blueprint.

   You tin change the part 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 tabular array from the Northwind database, the crosstab query displays the list of supplier names as rows, the product category names every bit columns, and a count of the number of products in each intersection.

   

Meridian of Page

By using Pattern view to create your crosstab query, y'all can use as many record sources (tables and queries) as you want. However, you can keep the blueprint simple by starting time creating a select query that returns all of the data that you lot want and so using that query as the only record source for your crosstab query. For more than information well-nigh creating a select query, refer to the Run across Also section.

When you lot build a crosstab query in Design view, y'all employ the Total and Crosstab rows in the pattern grid to specify which field's values will go column headings, which fields' values will become row headings, and which field's values to sum, average, count, or otherwise summate.

ane. 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

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

   

2. In the Show Table dialog box, double-click each table or query that you want to apply equally a record source.

   If you lot use more than than i 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 Likewise section.

3. Close the Show Table dialog box.

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

5. In the query design window, double-click each field that you desire to use as a source of row headings. You can select as many as three fields for row headings.

6. 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. Y'all can also use 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 use as the source of cavalcade headings. You can select only 1 field for column headings.

8. In the query blueprint grid, in the Crosstab row for the column heading field, select Column Heading.

   You tin enter an expression in the Criteria row to limit the results for the column heading field. Even so, using a criteria expression with the column heading field does non limit the number of columns returned by the crosstab query. Instead, it limits which columns contain data. For example, suppose yous have a column heading field that has iii possible values: red, greenish, and blue. If you apply the criterion ='blueish' to the column heading field, the crosstab however displays a cavalcade for ruddy and a column for green, but only the column for blueish contains data.

   If you desire to limit the values that brandish as column headings, you can specify a list of fixed values by using the query's Column Headings property. For more information, see the side by side section.

9. In the query design window, double-click the field that y'all want to use to calculate summary values. You can select only one field to use for summary values.

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

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

    Yous 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 desire to specify fixed values to use for column headings, yous tin set the query's Column Headings property.

1. Open the crosstab query in Blueprint view.

2. If the property canvas is non visible, printing F4 to display it.

3. In the property sheet, just above the Full general tab, make sure that the Selection blazon is Query Properties. If it is not, click an empty spot in the infinite higher up the query design filigree.

4. In the property sheet, on the Full general tab, in the Column Headings property, enter a comma-separated list of values that you want to use every bit column headings.

   Some characters (such as nigh punctuation marks) are not allowed in column headings. If y'all use these characters in your list of values, Access substitutes an underscore (_) for each such graphic symbol.

SQL syntax for a crosstab query

A crosstab query is expressed in SQL every bit a TRANSFORM statement. A TRANSFORM argument has the post-obit syntax:

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

The TRANSFORM statement has these parts:

Function	Description
aggfunction	An SQL aggregate role that operates on the selected information.
selectstatement	A SELECT statement.
pivotfield	The field or expression you want to use to create column headings in the query's result set.
value1, value2	Fixed values used to create column headings.

SQL view does non limit the number of tables or queries that you tin use every bit record sources for a crosstab query. Still, yous tin help keep the design simple past creating a select query that returns all of the data that you lot want to use in your crosstab query, and then using that select query as the tape source. For more information almost creating a select query, refer to the See Also section.

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

2. Close the Evidence Table dialog box.

3. On the Design tab , in the Views grouping, click View, and and then click SQL View.

4. In the SQL object tab, type or paste the post-obit SQL:

   TRANSFORM  SELECT  FROM  GROUP Past  Pin  ;                  

5. On the start line, after TRANSFORM, type an expression to apply to calculate summary values; for instance, Sum([Corporeality]).

   If you lot are using more than one table or query every bit a tape source, include the table or query name as part of each field name; for example, Sum([Expense].[Amount]).

6. On the second line, after SELECT, type a list of fields or field expressions that you desire to apply for row headings. Carve up the list items by using commas; for case, [Budget].[ Dept_ID ], [Expense].[Type].

7. 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.

8. On the fourth line, after GROUP BY, type the same list of fields that you lot used in the SELECT clause, in pace half-dozen.

9. On the fifth line, after PIVOT, type a field proper noun or expression that y'all desire to use for cavalcade headings; for instance, Pin [Upkeep].[Yea r].

To add a sort order to a crosstab query in SQL view, use an Lodge BY clause.

1. Insert a line betwixt the Grouping By clause and the Pivot clause.

2. On the new line, type Gild Past followed by a infinite.

3. Type the field proper name or expression that you want to sort on; for example, Gild 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.

4. If y'all desire to sort on an additional field or expression, type a comma so type the additional field name or expression. Sorting occurs in the club that the fields or expressions appear in the ORDER Past clause.

• At the end of the PIVOT clause in SQL view of your crosstab query, type IN, followed by a comma-separated listing of values (enclosed in parentheses) to use every bit 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.

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

2. Type WHERE followed by a field criterion.

   If you want to use additional criteria, you lot can apply the AND and OR operators to extend your WHERE clause. You tin also use parentheses to grouping criteria into logical sets.

Sometimes, rather than using every value of a field for row or column headings, you want to grouping the values of a field into ranges and then employ those ranges for row or cavalcade headings. For example, suppose you use an "Age" field for column headings. Rather than using a column for each age, y'all may prefer to use columns that represent age ranges.

You tin use the IIf Office in an expression to create ranges to use for row or column headings.

Tip:If you want to create intervals with a Date/Fourth dimension 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 desire, you should create your crosstab query in Design view, and then use the technique described in this section to create the intervals that you desire.

1. Open up the crosstab query in Blueprint view.

2. In the query blueprint filigree, in the Field row, correct-click an empty column, so click Zoom on the shortcut card.

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

4. Type IIf().

5. Inside the parentheses that follow IIf, blazon a comparing expression that defines the first range of field values.

   For case, suppose you are creating ranges for an Historic period field, and you want each range to exist twenty years. The comparing expression for the first range is [Age]<21.

6. Type a comma afterward the comparing expression, so blazon a proper 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 and so blazon "0-20 years".

7. Blazon a comma after the range proper noun (outside the quotation marks), so practise one of the post-obit:

   • To create another range, type IIf() and then repeat steps five, half-dozen 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 similar this (line breaks were added for readability):

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

     Annotation:When Access evaluates the expression, it stops evaluating as soon as i of the IIf statements evaluates to true. You exercise 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 take evaluated as true.

8. In the query pattern filigree, in the Total row, select Group By.

9. In the Crosstab row, specify whether to use the ranges as row headings or column headings. Remember that you tin specify from ane to three row headings, and one column heading.

Y'all 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 Land/Region. Rather than ever displaying data for every land or region, you may want your query to prompt for a proper noun then display data co-ordinate to the user'due south input.

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

Notation:You can besides add a parameter prompt to a column heading field, just this will not limit the columns that appear.

1. Open up the crosstab query in Blueprint view.

2. In the Criteria row for the row heading field for which yous want to prompt the user for input, blazon question text enclosed in foursquare brackets. The question text will appear as a prompt when the query is run.

   For instance, if you blazon [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 desire your parameter to be flexible, concatenate your expression with wildcards, past using the Like operator. For example, instead of using [Which country or region?] as your criterion, yous can use Like [Which country or region?]&"*" to make the parameter match a greater range of input. Using Like does not change the advent of the parameter prompt.

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

4. In the Query Parameters dialog box, in the Parameters column, enter the same parameter prompt that yous used in the Criteria row. Include the square brackets, but do not include any concatenated wildcards or the Like operator.

5. In the Information Type cavalcade, select the data blazon for the parameter. The information blazon should match the information type of the row heading field.

If the field that y'all employ to summate summary values in your crosstab query contains null values, those values are ignored by any amass function that you use. For some aggregate functions, your results can be afflicted. For case, to calculate an average, you add all of the values and divide the result by the number of values. Simply 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 supplant whatsoever null values with zeroes so that the values will be counted during aggregate calculations. You can use the Nz role to supercede null values with zeroes.

Nz syntax

Nz ( variant [, valueifnull ] )

The Nz function syntax has these arguments:

Argument	Description
variant	Required. A variable of information type Variant.
valueifnull	Optional (unless used in a query). A Variant that supplies a value to be returned if the variant argument is Null. This statement enables you to return a value other than nada or a nil-length string. Annotation: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.

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

2. On the shortcut carte du jour, 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 only inside the right parenthesis.

   For case, if you used Nz with a field chosen "Hours Lost" to plow zip values into zeroes, your finished expression would look like this:

Nz([Hours Lost], 0)

• Keep it simple    Crosstab queries can become hard 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 but tables. You tin ofttimes start past building a totals query and then using that query as the record source for a crosstab query.

• Choose your column heading field advisedly    Crosstab datasheets tend to be easier to read when the number of cavalcade 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 cavalcade headings. For instance, if your query calculates a value past age and gender, consider using gender for column headings instead of historic period considering gender typically has fewer possible values than historic period.

• Use a subquery in a WHERE clause    You tin can utilize a subquery as part of a WHERE clause in a crosstab query.

Top of Page

See Also

Create a uncomplicated select query

Join tables and queries

turnerexion1958.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

Share this post