Group operations in queries. Counting data using the Access grouping query in queries

Assigning group operations

Group operations allow you to select groups of records with the same values in the specified fields and use one of the aggregate functions for these groups.

Access provides nine statistical functions:

sum - the sum of the values of a certain field for the group;
Avg - average of all field values in the group;
max, Min - maximum, minimum value of the field in the group;
count - the number of field values in the group, excluding empty values;
StDev - standard deviation from the mean value of the field in the group;
var - variance of field values in the group;
First and Last - the value of the field from the first or last record in the group.

The result of a query using group operations contains one record for each group.

How to create a query using group operations

To create a query using group operations, a select query is generated. The request form includes fields for which you need to | perform grouping, and the fields on which to perform statistical calculations.

The command is executed View | Group operations(View | Totals) or click on the button on the Query Designer toolbar Group operations(Totals). You can also right-click and select from the context menu Group operation(Total) (the mouse cursor must be set in the request form). The line appears in the request form Group Operation (Total), in which the grouping is recorded for all fields (Group By).

In order to perform calculations in a group on a certain field, you need to replace the word grouping (croup By) in it with a statistical function. You can select the required function through the drop-down list in the field.

Let's consider the construction of a query with a group operation using the DELIVERY_PLAN table as an example.

Sum query

Determine what the total amount of each of the goods should be furnished to buyers under contracts. All data on the quantity of goods planned for Delivery is indicated in the DELIVERY_PLAN table.

Let's create a select query for the DELIVERY_PLAN table. From the list of the table, drag the item_code field - product code into the request form. We will create this field for further grouping by it. Drag and drop the qty_post field into the request form, according to which the sum function will be calculated to calculate the total quantity of a particular product ordered in all contracts.

Press the button Group operations(Totals). Replace the word grouping

(croup By) in column count_post per function Sum. To do this, let's call the list and select this function. The request form will take the form shown in Fig. 4.13.

The item_code field displays not the product code stored in the DELIVERY_PLAN table, but its name. This is determined by the fact that a list is built in the table for the field_codes based on the product table. If your database does not convert the tocode_field to combo box as shown in chapter 3, the product code will be displayed in the query table.

Field signature Sum- COL_POST can be replaced with Goods ordered... To enter this signature, we will switch to design mode, in the request form, place the mouse cursor on the count_post field and press the right button. In the context menu, select Properties(Properties). In the window Field properties(Field Properties) type in the line Signature(Caption) - goods ordered.

The result table after refining the query is shown in Fig. 4A5.

Rice. 4.15. Result table with changed field signature

Let's save the selection query under the name "Goods ordered".

Request with function Count

Let's determine how many times the goods were shipped under each of the contracts. The fact of shipment is recorded with the "Invoice" document.

Let's create a select query based on the invoice table. From the list of the invoice table, drag the field no_dog - the contract number into the request form. This field should be used for grouping. In fact, the meaning of the problem comes down to counting the number of rows in the table with the same contract number, so it doesn't matter which field the count function will be calculated on. Let's drag any field into the request form, for example, again no_dog.

Press the button Group operations(Totals). Let's replace the word grouping (Group By) in one of the columns named num_dog with the function count. The request form will take the form shown in Fig. 4.20.

Let's save the query under the name "Number of shipments under contracts". The query execution result is shown in Fig. 4.21.

The query allows for data belonging to the same groups to summarize: to calculate their number, sum, average, maximum and minimum value.

Exercise: create a query that will calculate the total number of items and the total volume of goods received for each invoice.

Consider the table data " Receipt of goods"(fig. 13). The table has the same column values Invoice number can be repeated many times (depending on how many goods were received under the invoice). It is necessary to create a request that will sum up the quantity of goods for invoices with the same number.

For a better understanding of what the result should be in the query in Fig. 13, on the right, in curly brackets, records are marked for which the invoice numbers are the same, and the total number of items and the total volume are calculated.

Rice. 13. Goods receipt table

Operating procedure:

1.In Navigation window selected Object category - Object type, and in the section Filter by groups switch is installed Inquiries... On the ribbon in the section Creation in a group Inquiries the button is pressed Query Constructor.

2. In the dialog box Adding tables, the tables are selected, the data from which you want to display in the query (" Receipt of goods") and the button is pressed Add.

3. The screen will display the query design window, which consists of two parts: the upper part displays the table layouts, and the lower part displays the section for defining the query parameters.

The fields, the values of which should be displayed as a result of the query execution, are added to the query parameters section (Fig. 14). (You can add a field to a query by double-clicking on its name in the corresponding table located at the top of the query window).

5. To implement a query in the Constructor mode, add an additional line Group operations (Fig. 14) by clicking on the button on the toolbar.

Under the field Waybill number in line Group operations team is selected Grouping(invoices with the same number are grouped), under the field Product code in line Group operations team is selected Count(quantity), and under the field Quantity - command Sum ( the amount is added up). The operations used in the grouping query are described in Table 2.

The query execution result is shown in Fig. 15. (compare the result with the data in Fig. 13).

Table 2. Group operations

Operation name	Meaning
	Summation
	Mean
	Minimum value
	Maximum value
	Number of items in a column
	The last element
	First element
Condition	Indicates a boolean expression
Expression	Indicates that the field is calculated

Data is generally easier to analyze when it is divided into groups. For example, a report that grouped sales by region can help identify trends that might otherwise go unnoticed. Plus, placing totals (such as sums or averages) at the end of each group in your report replaces many of the calculator's operations.

Access makes it easy to work on grouped reports. You can create a simple grouped report using the report wizard, add grouping or sorting to an already created report, and change the already defined grouping or sorting parameters.

Note: This article does not apply to Access web apps, a new type of database that is created in Access and published on the web.

Create a summary report with grouping or sorting

Even if this is your first time creating grouped reports, you can quickly create a simple report by following these steps:

Creating a Grouped Report Using the Report Wizard

The Report Wizard asks you questions and creates a report based on the answers. One of them mentions the fields by which you want to group in the report. After creating the report, you can use it ready-made or modify it according to your needs. Before starting the report wizard, decide on the choice of the data source.

Launching the Report Wizard

Grouping Records Using the Report Wizard

Grouping allows you to organize your records by groups (for example, by region or by salesperson). Nested groups make it easy to define relationships between groups and quickly find the data you need. You can also get summary data using grouping, such as totals or percentages.

When you include multiple tables in a report, the wizard examines the relationship between them and determines a possible way to view the data.

Sorting and summarizing records

You can sort records in ascending or descending order by 1-4 fields.

Using the navigation buttons at the bottom of the viewing area, you can sequentially view the pages of the report or go to any of its pages. Press one of the navigation buttons, or type the page number you want in the page number input box, and then press Enter.

In preview mode, you can zoom in to see details, or zoom out to see where the data is on the page. Click once with the mouse cursor pointing at the report. Click again to cancel the magnification effect. You can also use the zoom control on the status bar.

Add or change grouping and sorting in an existing report

If you already have a report and need to add sorting or grouping to it, or you need to change the sorting or grouping conditions in it, this section will help you with that.

Adding grouping, sorting and totals

You can perform sorting, grouping, and summing operations by right-clicking fields in layout mode and then selecting the desired operation from the context menu. To enter Layout mode, right-click the report in the Navigation Pane and select Layout mode.

Note: Although the instructions in this section do not explicitly talk about a realm, we recommend that you open it and track changes to it as you work. You will gain a better understanding of Access actions by becoming familiar with the scope Grouping, sorting and totals, you can use it to further correct the report. To display the panel Grouping, sorting and totals:

in the tab Constructor in a group Grouping and totals click Grouping and sorting.

Sort by one field

Right-click any value in the field you want to sort by.

In the context menu, select the desired sorting option. For example, to sort the text box in ascending order, click Sorting from A to Z... To sort a numeric field in descending order, click Sort descending.

Access sorts the report as you specify. If the area Grouping, sorting and totals is already open, you may notice that a new line has been added to the field Sorting.

Sort by multiple fields

Note: When you apply a sort by clicking a field in layout mode, you can sort only one field at a time. Applying sort to another field removes sorting by the first field. This is in contrast to the way sorts are done on forms, where you can set different sort orders by clicking each field in turn and choosing the sort order you want. To create sort levels for multiple fields, see the section.

Grouping by field

Right-click any value in the field that you want to group by.

In the context menu, select Grouping.

Access adds a grouping level and creates a group header. If the area Grouping, sorting and totals is already open, it will be seen that a new line has been added to the field Grouping.

Adding a total to a field

This option allows you to calculate sum, average, quantity, or other statistical fields. The grand total is added to the end of the report, and the group totals are added to the report groups.

Right-click any value in the field for which you want to calculate the total.

Click Outcome.

Select the operation to be performed: Sum, The average, Number of records(to count all entries), Number of values(to count only records with the value of this field), Maximum, Minimum, Standard deviation or Dispersion.

Access adds a control to the report header, such as calculated text, that summarizes the total. If the report contains grouping levels, Access adds group headers and footers (if not present) and places the total in each header and footer.

Note: You can also add totals by clicking the field you want to calculate them by and on the tab Constructor in a group Grouping and totals clicking Outcomes.

Add Grouping, Sorting, and Total Using the Grouping, Sorting and Totals Pane

Working with an area Grouping, sorting and totals provides maximum flexibility when you need to add or change groups, sort orders, or totals in a report. The layout view is the most convenient to use, as it makes it much easier to see how the changes you make affect the display of the data.

Display the Sort, Group, and Totals area

In the tab Constructor in a group Grouping and totals click Grouping and sorting.

Access will display the area Grouping, sorting and totals.

To add a grouping or sorting level, click Add grouping or Add sort.

To the area Grouping, sorting and totals a new line is added and a list of available fields is displayed.

You can click one of these field names, or expression below the list of fields to enter an expression. As soon as you click a field or type an expression, Access adds a grouping level to the report. In layout mode, the sort or group order is immediately displayed.

For more information on creating and using expressions, see the article Create expressions.

Notes:

After specifying multiple levels of sorting or grouping, you may need to navigate down the area Sorting, grouping and totals to see the buttons Add grouping and Add sort.

You can assign up to ten levels of sorting and grouping in a report.

Change grouping options

Each level of grouping or sorting contains a set of options that you can use to get the results you want.

Sorting order. You can change the sort order by clicking the appropriate drop-down list and choosing the option you want.

Grouping interval. This setting determines how records are grouped. For example, text fields can be grouped together by their first character (if they start with "A", "B", etc.). Date fields can be grouped by day, week, month, quarter, or you can enter your own interval.

Results. To add totals, click this option. You can add totals to multiple fields, and you can calculate multiple types of totals for a single field.

Total by field and select the field for which you want to calculate totals.

Click the dropdown arrow Type of and choose a calculation method.

Please select Show grand total to add the grand total to the end of the report (its header and footer).

Please select Show group totals and grand total to add a control to the group footer that calculates the percentage of the grand total for each group.

Please select Show in group header or Show in group header to display the grand total where you want.

After selecting all the options for the field, you can repeat the process by selecting another field from the dropdown list Total by field, or click outside the popup Outcomes to close it.

Name. Allows you to change the title of the field by which the total is calculated. Used for a column heading and for summary fields in headers and footers.

To add or change a title:

click the blue text after the caption with title;

a dialog box will appear Scale;

enter a new title in the dialog and then click OK.

With / without heading section. With this setting, you will be able to add or remove the header section that is in front of each group. When you add a heading section, Access places the grouping field in the heading. Access will ask you for confirmation before you delete the header section that contains controls other than the grouping field.

With / without a note section. Use this option to add or remove a header and footer section after each group. Access will ask you for confirmation before deleting the header and footer section that contains the controls.

The unbreakable representation of the group. This parameter determines how the groups are arranged on the page when the report is printed. You may want to arrange the groups as close to each other as possible for easy viewing on the page. But this will usually increase the paper consumption when printing the report, because most pages will have blank space at the bottom.

Don't keep the group on one page. Use this option if you don’t care about the position of the groups for page breaks. For example, 10 items in a 30-item group might be at the bottom of one page and the remaining 20 at the top of the next page.

Keep the group on one page. This option helps to minimize the number of page breaks in a group. If the group does not fit in the remaining space of one page, Access leaves that space blank and places the group on the next page. Large groups can still span multiple pages, but this option minimizes these occurrences.

Keep the title and the first post on the same page. Make sure that the group heading is not printed separately from the group itself at the bottom of the page. If Access determines that there is not enough space to print at least one line after the heading, the group prints on the next page.

Changing the priority of grouping and sorting levels

To change the priority, click a line in the area Grouping, sorting and totals and then an up or down arrow to the right of the row.

Removing Grouping and Sorting Levels

To remove a level, in the area Grouping, sorting and totals select the line you want to delete and then press the DELETE key or the Delete to the right of the line. When you delete a grouping level, if the group header or footer contained a grouping field, Access moves it to the detail section of the report. All other controls are removed.

Create a summary report (no record details)

If you only want to show totals (data in header and footer rows), on the tab Constructor in a group Grouping and totals click Hide details... This will hide the records of the next lowest grouping level, and the totals will be displayed more compactly. Although the entries are hidden, the controls in the hidden section are not removed. Click Hide details again to return the detail lines to the report.

6.1. Calculated fields.

6.2. Create expressions using the Expression Builder.

6.3. An overview of the built-in functions of the MS Access DBMS.

6.4. Summary queries.

6.5. Cross queries.

Very often, when forming a set of records, it is required to perform calculations on the data (determine the age of the employee, the cost of the order, the percentage of sales, highlight a part of the item code, etc., that is, display information that is not stored in the database) or perform certain operations for direct processing of the selected data.

In MS Access QBE, such capabilities are provided through calculated fields and group operations.

6.1. Calculated fields

A calculated field is an expression made up of operators (arithmetic, comparison, boolean, concatenation) and operands. Constants, built-in or user-defined functions and identifiers can be used as operands, for example

Cost: Goods! Price * Quantity * (1-Discount)

Number of Men: Sum (IIf (Gender = "m"; 1; 0))

Full name: Last name & "" & Left (First name; 1) & "." & Left (Middle name; 1) & "."

Cost, Number of Men and Full Name are the names of calculated fields and are displayed in table mode in the column header, the colon character acts as a separator between the name of the calculated field and the expression.

If the name of the table or field contains spaces, then its identifier must be enclosed in square brackets in the expression, for example

Cost: Price * [Item Quantity]

A calculated field is created directly in the QBE design by entering an expression in a cell Field any free column. Calculation results displayed in the field are not stored in the base table. Calculations are performed again each time the query is executed, so the results always represent the current contents of the database.

You cannot manually update the calculated results.

To build complex expressions, MS Access includes a utility called Expression builder.

6.2. Creating expressions with the Expression Builder

The Expression Builder can be launched by clicking on the key Build on the toolbar Query constructor or by selecting the menu command Build ....

Figure 6.2. Expression Builder Dialog Box
with a formed expression

6.3. Overview of built-in DBMS functionsMS Access

The MS Access DBMS contains more than 100 built-in functions (Figure 6.3), which can be used when forming a calculated field or when setting a selection condition.

	Returns the value of the day of the month from 1 to 31
	Returns a month value from 1 to 12
MonthName (month [; flag])	Returns the name of the month corresponding to the month number: 1 - January, 2 - February, etc. If the value of the argument flag is True, the function returns the abbreviation of the month: 1 - Jan, 2 - Feb, etc.
	Returns a year value from 100 to 9999
Weekday (date [; date])	If number is not specified, returns a day of the week from 1 (Sunday) to 7 (Saturday). If number is 0, then returns a day of the week from 1 (Monday) to 7 (Sunday)
	Returns an integer from 0 to 23 representing the hour
DatePart (interval; date)	Returns a numeric value based on the value of the interval argument: "q" - quarter (from 1 to 4); "m" - month (from 1 to 12); "yyyy" - year (from 100 to 9999); "ww" - week (from 1 to 53); etc. (see the help for the function).
	Returns the current system date

Continuation of table 6.1

Description

Left (text; n)	Returns the left n characters of the argument text
Right (text; n)	Returns the right n characters of the argument text
Mid (text; start_position [; n])	Returns n characters starting at the start_position position of the text argument. If n is not specified, then returns all characters up to the end of the string starting at the start_position position of the text argument.
	Returns the number of characters (string length) in the text argument
LTrim (text)	Returns the string value of the text argument without leading spaces
RTrim (text)	Returns the string value of the text argument without trailing spaces
Trim (text)	Returns the string value of the text argument without leading and trailing spaces
	Returns the string value of the argument number
Format (variable; format)	Returns the value of the argument variable in the format given by the argument format

6.3. Summary queries

When analyzing data, it is often not individual records that are of interest, but the totals for groups of data, for example:

The number of transactions with Partners for a certain period of time;

Average sales for each month for the previous year.

Answers to such questions are given by the final query.

To calculate the total values, click the button Group operations on the toolbar Query constructor so that the line appears on the QBE Group operation(after the table name).

By default, for each field entered in the request form, the value is set Grouping(no results are summed up).

To summarize, it is necessary to replace the installation Grouping for a specific final function. The MS Access DBMS provides 9 functions (Table 6.2), providing the execution of group operations.

Table 6.2

	Appointment
	Returns the sum of a set of values
	Returns the arithmetic mean of a set of values
	Returns the smallest value from a set of values
	Returns the largest value from a set of values
	Returns the number of records in a set of non-Null values
	Returns the first value of a field in a group
	Returns the last value of a field in a group
	Returns the standard deviation of a set of values
	Returns the variance of a set of values

Group operation there is an installation Expression... This setting is applied when the expression (line Field) uses several summary functions.

Dropdown strings Group operation there is an installation Condition... This setting is applied when the selection condition is written in the Criteria row, but the data of the column (fields) should not participate in the group operation.

To solve more complex statistical problems in the MS Access DBMS, a special type of query is provided - cross queries.

6.4. Cross queries

Cross query Is a special type of group query that reflects the results of statistical calculations based on the value of one field in the table.

Three basic source fields are sufficient to build a cross-reference query. By repeating values of one field, the names of the headings of the rows of the final (pivot) table are formed (Figure 6.4). By repeating values of another field, the names of the column headings of the final (pivot) table are formed. The aggregation results for the third field are displayed in the cells of the PivotTable (value area). An example of a cross query in design mode is shown in Figure 6.5, and the query results are shown in Figure 6.5. 6.6.

Multiple fields can be placed in the row header area, while only one field can be placed in other areas.

In a cross-reference query, it is allowed to specify the selection conditions. Sorting can only be carried out by fields located in the row header area.

Figure 6.4. Crosstab layout

Figure 6.5. Formed cross-reference query in QBE

Figure 6.6. The result of executing a query in the Tables mode