Summarization in reports. Summarizing in reports Supported data types

In any Access report that contains numbers, you can use totals, averages, percentages, or cumulative sums to make the data more understandable. This article explains how to add these elements to your report.

Types of aggregates that can be added to a report

The following table shows the types aggregate functions in Access that you can add to your report.

Calculation	Description	Function
	Sums the elements in a column.
	Determines the average of all elements in a column.
	Counts the number of elements in a column.
Maximum value	Returns the element that has the largest (numeric or alphabetical) value in the column.
Minimum value	Returns the element that has the smallest (numeric or alphabetical) value in the column.
Standard deviation	Shows how much the values in a column deviate from the average.
Dispersion	Calculates the variance for all values in a column.

Add a sum or other aggregate in layout view

Layout mode provides the fastest way to add sums, averages, and other aggregates to a report.

Data(Control Source) expression that performs the desired calculation. If your report has grouping levels, Access will also add a text box that performs the same calculations in each group note section.

For more information about creating grouping levels in reports, see Create a grouping or summary report.

Adding a sum or other aggregate in design mode

The constructor allows you to more accurately customize the placement and appearance total values. In grouped reports, you can put totals and other aggregates in the header or footnote of each group. Report-level aggregates can be placed in the header or footer of a report.

Access adds a text box to the report header and sets its property value Data(Control Source) expression that performs the desired calculation. If your report has grouping levels, Access will also add a text box that performs the same calculations in each group note section. If for property Cumulative amount(Running Sum) value set For everything, then the overall total can be repeated in the report footer. Create a field in it and set its property value as Data(Control Source) name of the field in which the cumulative sum is calculated, for example =[Order Amount].

The Total row in Access allows you to quickly view a summary of the data in the columns of a table. For example, if you add a Total row to a table that contains details of purchases, you can display the amount of sales, the total number of units of an item, or the number of items purchased.

Note: To display the sum of a column's values, you must set the column's data type to Numeric, Fractional, or Currency. For non-numeric columns, you can only select the Count of Values summary type.

Adding a "Total" line

Selecting a total type

After adding the line results you choose the type of total displayed for each column. For example, aggregates such as sums can be displayed when the data type is number, decimal, or currency. Counting values is possible if the data type is a text value.

Let's have the table in this example display the sum of the values in the columns Purchase price And Purchased items and the total for the column Position, as shown in the image below.

How Sum and other aggregate functions work

Aggregate functions perform calculations on columns of data and return a single result. They can be useful if you need to calculate a single value, such as a sum or average. Keep in mind that aggregate functions apply to columns of data. This may seem obvious, but when designing and using databases, you typically focus on rows of data and individual records so that users can enter data into a field, move the cursor right or left to fill in the next field, etc. Conversely, you use aggregates functions, focusing on groups of records in columns.

Let's say you use Access to store and track sales data. Using aggregate functions, you can calculate the number of items sold in one column, the total sales in a second, and the average sales for each item in a third.

The table below describes the Access aggregate functions that are available in the Total row. Remember that there are other aggregate functions in Access, but they are used in queries.

Function	Description	Supported data types
	Calculates the average value for a column. The column must contain numeric, monetary, or date or time values. The function ignores empty values.
Number of values	Counts the number of elements in a column.	All data types except complex repeating scalar data such as a multivalued list column. For more information about multi-valued lists, see Best practices for creating and deleting multi-valued fields in the Multi-Valued Fields tutorials.
Maximum value	Returns the element with the highest value. For text data, the largest value is the last value in the alphabet, and Access is not case sensitive. The function ignores empty values.	, "Date and time"
Minimum value	Returns the element with the smallest value. For text data, the smallest value is the first alphabetical value, and Access is not case sensitive. The function ignores empty values.	"Numeric", "Real", "Currency", "Date and Time"
Standard deviation		"Numeric", "Real", "Currency"
	Sums the elements in a column. Suitable for numeric and monetary data only.	"Number", "Real", "Currency"
Dispersion	Calculates the statistical variance for all values in a column. Suitable for numeric and monetary data only. If the table contains fewer than two rows, Access returns a blank value. For more information about the Variance function, see the next section.	"Numeric", "Real", "Currency"

Learn more about the Standard Deviation and Variance functions

Functions Standard deviation And Dispersion statistical values are calculated. In particular, they are used for values that lie in the vicinity of their average value and obey the law of normal distribution (are on a Gaussian curve).

Suppose you randomly selected 10 tools made on the same machine and measured their fracture strength for machine inspection and quality control. If you calculate the average fracture strength value, you will see that for most tools the fracture strength is close to the average value, but there are also tools with higher and lower values. However, if you calculate only the average fracture strength value, this figure will not give you any information about the effectiveness of quality control, since a few unusually strong or brittle tools can increase or decrease the average value.

Functions variability And standard deviations indicate this problem by indicating how close the values are to the mean. For critical strength, smaller numbers returned by one of the functions indicate that your production processes are operating normally, as some of them are of limited extent, being above or below average.

Detailed description variance and standard deviation are beyond the scope of this article. More information about both features can be found on statistics websites. When using functions Dispersion And Standard deviation Remember the following rules.

An example of calculating the total amount in a Microsoft Access database table without using an SQL query

There are times when in a database table you need to calculate the sum of the numeric values of a certain column. There are different calculation methods for this, one of which is based on using the appropriate SQL query.

In this task, using a Microsoft Access table as an example, the total amount is calculated without using an SQL query. Additionally, the arithmetic mean of the values of the cells of the specified column is calculated.

Using this example, you can create any numeric calculation over multiple records of a given column.

The task

As a result, the ConnectionString is formed in the ADOConnection1 component (Fig. 2). This line specifies the data provider type and the full path to the database file.

Rice. 2. ConnectionString of the ADOConnection1 component

Component setup ADOConnection1, ADOTable1, DataSource1, DBGrid1.

To display a database table, you need to configure the following component properties:

– in the ADOConnection1 component, the LoginPrompt property = “false” (Fig. 3) (cancelling the request for username and password to access the database);

– in the ADOTable1 component, the Connection property = “ADOConnection1” (Fig. 4);

– in the DataSource1 component, the DataSet property = “ADOTable1” (Fig. 5);

– in the DBGrid1 component, the DataSource property = “DataSource1” (Fig. 6);

– in the ADOTable1 component, the property TableName = “Worker” (Fig. 7);

– in the DBGrid1 component, from the Options property, set the option dgEditing = false (prohibiting entering data into table cells directly from the DBGrid1 grid) (Fig. 8).

Rice. 3. LoginPrompt property of the ADOConnection1 component

Rice. 4. Connection property of the ADOTable1 component

Rice. 5. DataSet property of the DataSource1 component

Rice. 6. DataSource property of the DBGrid1 component

Rice. 7. TableName property of the ADOTable1 component

Rice. 8. The dgEditing option of the Options property of the DBGrid1 component

Activating the table.

To display data in the table, you need to perform the following steps (Fig. 9):

– select table ADOTable1;

– property Active = true.

After this, the table data will be displayed in DBGrid1.

Rice. 9. Active property of the ADOTable1 component

Setting component sizes and shapes.

The next step is to adjust the sizes and positions of the components on the form, as shown in Figure 10.

Rice. 10. Main application form

Hiding the ID_Worker field in DBGrid1.

In order for the displayed table to have the correct appearance, you need to hide the ID_Worker field, which is a key field. In a table, this field is a counter. When adding new entry the value in this field is generated automatically (increased by 1).

First you need to call the field editor (“Fields Editor…”) of the ADOTable1 component from the context menu (Fig. 11).

Rice. 11. Calling the field editor of the ADOTable1 component

The Form1.ADOTable1 window will open. In this window, using the mouse, you need to call up the context menu. IN context menu select the command “Add All fields”.

As a result, the editor window will look as shown in Figure 12.

Rice. 12. Field editor

The Form1.ADOTable1 field editor displays all fields of the Worker table. To delete the ID_Worker field, you need to click on the ID_Worker line right click"mouse" and select the Delete command in the context menu. After this you can close the editor.

As a result, only two fields will be displayed in the DBGrid1 table (Fig. 13).

Rice. 13. Displaying a Worker table with two fields

Set the output filter to two decimal places in the Salary field.

In order for the amount value (2 decimal places) to be correctly displayed in the Salary field, you need to perform the following steps.

Select the ADOTable1 component. Call the field editor “Fields Editor...” as described in step 6. As a result, a list of two fields Name and Salary will open. Select the line called Salary. An object named ADOTable1Salary will be activated in the Object Inspector.

The next step is to set the “Display Format” field in the Object Inspector to “0.00” (Fig. 14).

Rice. 14. Setting the output format in the Salary field of the ADOTable1 object

After completing the steps, the Salary field in the table will be displayed with an accuracy of 2 decimal places.

Entering internal variables.

To save the sum and arithmetic mean data, you need to enter internal variables named sum and avg into the text of the TForm1 form class.

Variables are entered in the private section. The code fragment for the TForm1 form class looks like this:

... type TForm1 = class(TForm) ADOConnection1: TADOConnection; DataSource1: TDataSource; ADOTable1: TADOTable; DBGrid1: TDBGrid; StaticText1: TStaticText; StaticText2: TStaticText; StaticText3: TStaticText; ADOTable1Name: TWideStringField; ADOTable1Salary: TFloatField; procedure FormActivate(Sender: TObject); procedure FormClose(Sender: TObject; var Action: TCloseAction); private( Private declarations ) sum:real; // sum avg:real; // average public(Public declarations) end; ...

Programming the form activation event.

As soon as the user downloads the program, you need to immediately calculate the values of the sum and the arithmetic mean in the tables.

Therefore, you need to program the OnActivate event of Form1. The OnActivate event will be called when the form is activated after the program is launched for execution.

An example of event programming in Delphi is described in detail.

In our case, the event handler looks like this:

procedure TForm1.FormActivate(Sender: TObject); var f:TField; // additional type variable"Field" begin // 1. Checking if there are records in the table if ADOTable1.RecordCount = 0 then exit; // 2. Disable visualization in DBGrid1 ADOTable1.DisableControls; // 3. Iterate over all records of the Worker table // 3.1. Go to first entry ADOTable1.First; // 3.2. Reset amount to zero sum:= 0; // 3.3. Take the salary value from the first record f:= ADOTable1.FieldByName("Salary"); // 3.4. Looping through records in a table // - check to see if the end of the table has been reached while ADOTable1.Eof<>true do begin // 3.4.1. Increase amount sum:= sum + f.Value; // 3.4.2. Go to next entry ADOTable1.Next; end; // 4. Calculate the arithmetic mean avg:= sum / ADOTable1.RecordCount; // 5. Fill lines of type TStaticText StaticText1.Caption:= "Sum: " + FloatToStr(sum, ffFixed, 8, 2); StaticText2.Caption:= "Average salary: " + FloatToStr(avg, ffFixed, 8, 2); // 6. Enable visualization in DBGrid ADOTable1.EnableControls; end;

Let's explain some code fragments.

The DisableControls and EnableControls methods are responsible for disabling and enabling visual controls connected to a data set (database). Calling the DisableControls method can significantly speed up the process of iterating through records, since it will prevent the application from wasting time redrawing the contents of the DBGrid1 control with each record change.

Property

ADOTable1.RecordCount

indicates the number of records in the table.

ADOTable1.First

sets the first table entry active.

ADOTable1.FieldByName()

allows you to get an object of type TField for a given field. Then in this object you can get information from the current record. In the program, to access the values of the Salary field records, an additional variable f of type TField is used. To read the value in the current record of the Salary field, just call the operator

f.Value

Property

ADOTable1.Eof

becomes true if the end of the table is reached.

ADOTable1.Next

implements transition to the next table record.

The result of the sum is displayed in the Caption property of the StaticText1 component.

The arithmetic mean value is displayed in the Caption property of the StaticText2 component.

Launching the application for execution.

Now you can run the application.

This article describes how to count the data returned by a query. For example, in a form or report, you can count the number of items in one or more table fields or controls. You can also calculate averages, find the highest and lowest values, and the oldest and most recent date. Access also provides a tool called a total row that allows you to tally the data in a table without changing the structure of your query.

Select the desired action

Data counting methods

You can count the number of elements in a field (value column) using the function Number. Function Number belongs to a number of functions called aggregate functions. Aggregate functions perform calculations on columns of data and return a single value. In addition to the function Number, Access has the following aggregate functions:

Sum to sum columns of numbers;

Average to calculate the average of a column of numbers;

Maximum to find the largest value in a field;

Minimum to find the smallest value in a field;

Standard deviation to assess the spread of values relative to the average value;

Dispersion to calculate the statistical variance of all values in a column.

Access provides two ways to add a function Count and other aggregate functions into the query. You can:

Open the query in Datasheet view and add a total row. A total row allows you to use aggregate functions on one or more columns in the results of a query without having to change its structure.

Create a final query. The summary query calculates subtotals for groups of records. For example, if you want to calculate a subtotal of all sales by city or by quarter, you would use a summary query to group records by category and then sum all sales. On the other hand, you can use a total row to calculate a grand total for one or more columns (fields) of data.

Note: The following sections of this article describe in detail the use of the function. Sum, but be aware that you can use other aggregate functions in total rows and queries. For more information about using other aggregate functions, see the section below.

For more information about ways to use other aggregate functions, see .

The following sections describe the steps you need to follow to add a total row and describe how to use a total query to count data. Please note that the function Number works with more data types than other aggregate functions. Function Number can be used for any type of field except those containing complex, repeating scalar data, such as a multi-valued list field.

On the other hand, many aggregate functions only work with data in fields that have a specific data type. For example, the function Sum Only works with Number, Real, and Currency data types. For more information about the data types required for each function, see the section below.

General information For information about data types, see Change the data type for a field.

Counting data using the total row

To add a total row to a query, open it in Datasheet view, add the row, and then select the function Number or another aggregate function, for example Sum, Minimum, Maximum or Average. This section explains how to create a simple select query and add a total row.

Creating a simple select query

Adding a Total Row

Hide the total row

On the tab home in Group Posts click the button Results.

For more information about using a total row, see Displaying totals by column in a table.

Counting data using a summary query

When you need to count some or all of the records returned by a query, you can use a total query instead of a total row. For example, you can count the total number of transactions or the number of transactions in a particular city.

Typically, a summary query is used instead of a total row when you want to use the result value in another part of the database, such as a report.

Counting all records in a query

Counting entries in a group or category

Aggregate Functions Reference

The following table lists and explains the Access aggregate functions that you can use in total rows and queries. Remember that Access has more aggregate functions for queries than for total rows. Additionally, when working with an Access project (an external Access database that connects to a Microsoft database SQL Server) you can use the extended set of aggregate functions provided by SQL Server. For more information about them, see Microsoft SQL Server Books Online.

Function	Description	Supported data types
Sum	Sums the elements in a column. Suitable for numeric and monetary data only.
Average	Calculates the average value for a column. The column must contain numeric, monetary, or date or time values. The function ignores empty values.
Number	Counts the number of elements in a column.	All data types except complex repeating scalar data such as a multivalued list column. For more information about multi-valued lists, see Best practices for creating and deleting multi-valued fields in the Multi-Valued Fields tutorials.
Maximum	Returns an element having highest value. For text data, the largest value is the last value in the alphabet, and Access is not case sensitive. The function ignores empty values.	"Number", "Real", "Currency", "Date/Time"
Minimum	Returns the element with the smallest value. For text data, the smallest value is the first alphabetical value, and Access is not case sensitive. The function ignores empty values.	"Number", "Real", "Currency", "Date/Time"
Standard deviation	Shows how much the values deviate from the average. For more information about this feature, see Show totals by column in a table.	"Number", "Real", "Currency"
Dispersion	Calculates the statistical variance for all values in a column. Suitable for numeric and monetary data only. If the table contains fewer than two rows, Access returns a blank value. For more information about functions for calculating variance, see Display column totals in a table.	"Number", "Real", "Currency"