How to make an amount in access. Summarizing in reports

People! Help to make a simple request. There is a table: Table1 format: Order name | Amount | The total amount of all orders is the first 10 rubles. the second is 20 rubles. the third is 30 rubles. Tell me how to add the rows in the Amount column? To display the result "The total amount of all orders" = 60 rubles. And the table will be added over time ... that is. the 100th order will appear ... respectively, the 100th order has its own amount. It is necessary that the "Total amount of all orders" is recalculated automatically. Thanks in advance for your help.

8 answers

Based on the given data, this is impossible. What determines the sorting of rows in a table?

It's easy to do this in Excel. For example, command = SUM (A1: A100) - i.e. we get the sums for 100 cells in the table. Why not do this in the database? Each order name (text) corresponds to a corresponding number (number). Actually, these numbers (order numbers) are in order. Those. example: 1 | bag | 100 rub. | 2 | tire | 50 rub. | ... 100 | book | 60 rub. | ... How can I calculate the total amount of all orders (1 ... 100 ...)?

mivang In Excel, there is a physical order for the records in a table. Which determines what is above and what is below. In relational databases, this order DOES NOT EXIST. The order is determined by the sorting specified in the request, and in its absence it can be ANY. If you want the question to make sense - clearly indicate the criterion on the basis of which you can establish the order of the entries. In the above example, this is, for example, the line number. However, there is no such field in the structure of the table shown.

If you want the question to make sense - clearly indicate the criterion on the basis of which you can establish the order of the entries. In the above example, this is, for example, the line number. However, there is no such field in the structure of the table shown.

The table contains a field Order No.. Those. the general view is something like this: Order no. | Order name | Order amount | 1 | bag | 10rur | 2 | krushka | 20rur | 3 | banka | 30rur | ... | ... | *** | Is there a binding now? Or, again, I didn't understand something ...: (How to find the total amount of all orders?

Is there a binding now?

Yes. SELECT Table1. [Order No.], Table1. [Order Name], Table1. [Order Amount], Sum (q1. [Order Amount]) AS [Total Sum of All Orders] FROM (SELECT t1. [Order No.], t1. [Order name], t1. [Order amount], t2. [Order no.] FROM Table1 AS t1, Table1 AS t2 WHERE (((t1. [Order no.])> =. [Order no.]))) AS q1 INNER JOIN Table1 ON q1.t1. ​​[Order No.] = Table1. [Order No.] GROUP BY Table1. [Order No.], Table1. [Order name], Table1. [Order amount];

SELECT Table1. [Order No.], Table1. [Order name], Table1. [Order amount], Sum (q1. [Order amount]) AS [Total sum of all orders] FROM [SELECT t1. [Order No.], t1. [Order name], t1. [Order amount], t2. [Order no.] FROM Table1 AS t1, Table1 AS t2 WHERE (((t1. [Order no.])> =. [Order no.]))]. AS q1 INNER JOIN Table1 ON q1.t1. ​​[Order No.] = Table1. [Order No.] GROUP BY Table1. [Order No.], Table1. [Order name], Table1. [Order amount];

An error is displayed: Invalid use of brackets named "SELECT t1. [Order no.":((What is the problem? Added after 3 minutes and 33 seconds People! Ready to send the database. If you do. From me payment in WM. VERY URGENT (within 1 hour)

Suppose we have the following table: table: tblOrdersfield type idOrders Counter fldName Text fldSumma Monetary Then the query will look something like this: SELECT Sum (tblOrders.fldSumma) AS FROM tblOrders; Sum-fldSumma this is an expression alias Sum (tblOrders.fldSumma)... As a result, we get a table with one cell. For clarification, I wrote a sample code in VS 2005, without exception handling, to make it clearer. First, you need to create a project and add a button there. GetSumButton and a text box SumTextBox, that is, change their names. Replace the form name with MainForm and replace it with a downstream code. Option Strict OnOption Explicit OnImports System.Data.OleDbPublic Class MainForm "" "

Get the amount

Private Function GetSumma () As Decimal Using cn As New OleDbConnection (My.Settings.dbConnectionString) cn.Open () Dim cmd As OleDbCommand = cn.CreateCommand () cmd.CommandText = "SELECT Sum (tblOrders.fldSumOr) AS FROM; " Return CDec (cmd.ExecuteScalar ()) End Using End Function "" "

Handling the Click event of the GetSumButton

Private Sub GetSumButton_Click (ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GetSumButton.Click "Displaying the amount in the current currency Me.SumTextBox.Text = FormatCurrency (GetSumma ()) End SubEnd Class For the example to work, you need to in My Project >> Settings add a line with the name dbConnectionString following content Provider = Microsoft.Jet.OLEDB.4.0; Data Source = | DataDirectory | \ orders.mdb... Attach an MS Access database to the project or simply copy it into the directory with the executable file, inside which the required table should be created (preferably with data).

In this article, we'll talk about calculated fields in Access queries... A query, like a table, can perform calculations for each record with numeric, string, or date values ​​using data from one or more fields. The result of the calculation forms a new calculated field in the query table. Unlike calculated table fields, calculated fields do not create new fields in the original database tables. Each time the query is executed, calculations are performed based on the current field values.

Calculated field expressions can use constants and functions in addition to field names. As a result of processing an expression, only one value can be obtained.

Objective 1. The PRODUCT table contains the PRICE and VAT_RATE fields, calculate the price including VAT and compare it with that obtained in the calculated field of the Price with VAT table.

1. In design mode, create a select query for the ITEM table. Drag the fields NAIM_TOV, PRICE, RATE_VAT and Price with VAT into the request form (Fig. 4.6).
2. To calculate the price including VAT, create a calculated field by writing [PRICE] + [PRICE] * [VAT_VAT] in an empty cell in the Field row.
3. To filter records with a value greater than 5000 in a calculated field, in the Criteria row, enter> 5000
4. After entering the expression, the system, by default, generates the name of the calculated field Expression 1, which becomes the column heading in the table with the results of the query execution. This name will be inserted before the expression [PRICE] + [PRICE] * [VAT_VAT]. For each new calculated field in the query, the expression number is incremented by one. The calculated field name is separated from the expression by a colon. To change the name, place the mouse cursor in the calculated field of the request form and press the right mouse button. From the context-sensitive menu, select Properties(Properties) of the field and in the Caption line enter the new name of the field - Price with VAT1... Now, in the table with the results of the query execution, this name will be displayed in the header of the calculated column. The field name can also be corrected directly in the request form.
5. To display the result of the query execution, click on the button Execute(Run) in a group results(Results). The calculated field of the table and the query have the same values.
6. Change the price of the item in one of the request records. The values ​​in both calculated fields will be recalculated instantly.
7. To form a complex expression in a calculated field or selection condition, it is advisable to use the Expression Builder. The builder allows you to select the required field names in the expression from tables, queries, operation signs, functions. Remove the expression in the calculated field and use the builder to shape it.
8. Call the Expression Builder by clicking the button Builder(Builder) in the group Setting up a request(Query Setup) of the Design ribbon, or by selecting Build(Build) on the context sensitive menu. The mouse cursor must be previously set in the expression input cell.
9. On the left side of the window Expression Builder(Expression Builder) (Fig. 4.7) select the PRODUCT table on which the query is built. A list of its fields will be displayed on the right. Sequentially select the required fields and operators by double-clicking to insert into the expression. The expression will form at the top of the window. Note that the builder indicated the name of the table to which it belongs before the field name, and separated it from the field name with an exclamation mark.
10. Complete the process of building the expression in the calculated field by clicking OK.
11. Save the request under the name - Price with VAT and close it.
12. Execute the saved one by highlighting it in the Navigation Pane and selecting in context menu Open command.

Objective 2. You can use built-in functions in calculated fields and filter conditions. There are over 150 functions defined in Access.
Suppose it is necessary to select all the invoices for which the shipment was made in a given month. IN BACKGROUND, the shipment date is stored in the DATE_DATE field with the data type Date / Time.

1. In design mode, create a select query for the OVERLAY table. Drag and drop the NOM_NUMBER and CODE_SK fields into the form (Fig. 4.8).
2. Create a calculated field in an empty cell in a row Field(Field), by writing one of the expressions there: Format ([OVERLAY]! [DATE_PUT]; "mmmm") - this function will return the full name of the month
   or Format ([OVERLAY]! [POST_DATE]; "mm") - this function will return the number of the month.
3. To select invoices issued in a given month, in the calculated field in the Criteria line, enter the name of the month, for example, March (Fig. 4.8), or the number of the month, for example, 3 in accordance with the parameter in the Format function.
4. Execute your request by clicking the button Execute(Run) in a group results(Results) on the ribbon tab Working with Queries | Constructor(Query Tools | Design).
5. In the calculated field, write the Month function (OVERLAY! DATE_REF), and make sure that this function returns the month number extracted from the date.
6. To select all rows from the second quarter, in the Criteria row, enter the operator Between 4 And 6 to determine whether the expression value falls within the specified range.
7. In the calculated field, write MonthName (Month (OVERLAY! DATE)) and verify that the MonthName function converts the month number to its full name.

To consolidate, watch the video tutorial.

The Total row in Access lets you quickly view a summary of the data in the columns of a table. For example, if you add a Total row to a table of purchases, you can display the amount of sales, the total number of units of a product, or the number of products purchased.

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

Adding the "Total" line

Selecting the total type

After adding the line outcomes you choose the type of total displayed for each column. For example, aggregates such as sums can be displayed if the data type is Number, Decimal, or Currency. Counting values ​​is possible if the data type is a text value.

Let's make it so that in this example the table displays 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 when you need to calculate a single value, such as sum or average. Remember that aggregate functions are applied to columns of data. It might seem obvious, but when designing and using databases, you usually pay attention to rows of data and individual records so that users can enter data in a field, move the cursor to the right or left to fill in the next field, and so on. Conversely, you use aggregates. functions, focusing on groups of column entries.

Let's say you're using Access to store and track sales data. Using aggregate functions, you can count the number of products sold in one column, the total sales in the second, and the average sales for each product in the third.

The following table 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	Available on the "Total" row?
	Calculates the average for a column. The column must contain numeric or monetary values, or date or time values. The function ignores null values.
Number of values	Counts the number of elements in a column.	All data types except complex repeating scalar data such as a column of multivalued lists. For more information about multivalued lists, see the article Guidelines for creating and deleting multivalued fields in the multivalued fields tutorials.
Maximum value	Returns the item with the highest value. For text data, the highest value is the last alphabetically, and Access is not case-sensitive. The function ignores null values.	, "Date and time"
Minimum value	Returns the element with the lowest value. For text data, the lowest value is the first alphabetically, and Access is not case-sensitive. The function ignores null values.	"Numeric", "Valid", "Currency", "Date and Time"
Standard deviation		"Numeric", "Real", "Monetary"
	Adds the items in a column. Suitable for numeric and monetary data only.	"Number", "Real", "Monetary"
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 details on the Variance function, see the next section.	"Numeric", "Real", "Monetary"

Learn more about Standard Deviation and Variance

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

Suppose you randomly select 10 tools made on the same machine and measure their fracture toughness for machine inspection and quality control. If you calculate the average fracture strength, you will see that for most of the tools, the fracture strength is close to the average, but there are also tools with higher and lower values. However, if you only calculate the average fracture strength, this measure will not give you any information about the effectiveness of quality control, as several unusually strong or fragile instruments can increase or decrease the average.

Functions variability and standard deviations denote this problem by indicating how close the values ​​are to the mean. For critical force, lower numbers returned by one of the functions indicate that your manufacturing processes are running normally, as some are of a limited degree as they are above or below average.

Detailed description variance and standard deviation are outside the scope of this article. More information about both features can be found on the 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 you need to calculate the sum of the numeric values ​​of a certain column in a database table. For this, there are different calculation methods, one of which is based on using the corresponding 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 cells in the specified column is calculated.

Using given example, you can create any numeric calculation on a set of records in a given column.

The task

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

Rice. 2. String ConnectionString of the ADOConnection1 component

1. Component customization ADOConnection1, ADOTable1, DataSource1, DBGrid1.

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

- in the component ADOConnection1 property LoginPrompt = “false” (Fig. 3) (cancellation of the request for a username and password to access the database);

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

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

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

- in component ADOTable1 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. Property LoginPrompt of ADOConnection1 component

Rice. 4. Connection property of ADOTable1 component

Rice. 5. Property DataSet of component DataSource1

Rice. 6. Property DataSource of the DBGrid1 component

Rice. 7. Property TableName of ADOTable1 component

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

1. Activating the table.

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

- select the table ADOTable1;

- property Active = true.

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

Rice. 9. Active property of the ADOTable1 component

1. Adjustment of 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

1. Hiding the ID_Worker field in DBGrid1.

To make the displayed table look correct, you need to hide the ID_Worker field, which is a key field. In the table, this field is a counter. When adding a new record, the value in this field is generated automatically (increases by 1).

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

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

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

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

Rice. 12. Field editor

All fields of the Worker table are displayed in the Form1.ADOTable1 field editor. To delete the ID_Worker field, you need to right-click on the ID_Worker line and select the Delete command from the context menu. After that, you can close the editor.

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

Rice. 13. Displaying the Worker table with two fields

1. Sets the output filter with two decimal places in the Salary field.

In order for the Salary field to correctly display the value of the sum (2 decimal places), you need to perform the following actions.

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

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

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

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

1. Input of internal variables.

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

Variables are entered in the private section. The code snippet of the TForm1 class of the form 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; ...

1. Form activation event programming.

As soon as the user loads 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 at the moment the form is activated after the program has been 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 variable of the "Field" type begin // 1. Check if there are records in the table if ADOTable1.RecordCount = 0 then exit; // 2. Disable rendering in DBGrid1 ADOTable1.DisableControls; // 3. Loop through all records of the Worker table // 3.1. Move to the first record ADOTable1.First; // 3.2. Zero the amount sum: = 0; // 3.3. Take the salary value from the first record f: = ADOTable1.FieldByName ("Salary"); // 3.4. Loop through records in a table // - check for reaching the end of the table while ADOTable1.Eof<>true do begin // 3.4.1. Increase the amount sum: = sum + f.Value; // 3.4.2. Move to next record ADOTable1.Next; end; // 4. Calculate the arithmetic mean avg: = sum / ADOTable1.RecordCount; // 5. Fill in lines of type TStaticText StaticText1.Caption: = "Amount:" + FloatToStr (sum, ffFixed, 8, 2); StaticText2.Caption: = "Average salary:" + FloatToStr (avg, ffFixed, 8, 2); // 6. Turn on visualization in DBGrid ADOTable1.EnableControls; end;

Let's explain some code snippets.

The DisableControls and EnableControls methods are responsible for disabling and enabling visual controls connected to the dataset (database). Calling the DisableControls method can significantly speed up the iteration of records because it will prevent the application from spending time redrawing the contents of the DBGrid1 control each time the record is changed.

Property

ADOTable1.RecordCount

indicates the number of records in the table.

ADOTable1.First

makes the first record of the table 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 records of the Salary field, an additional variable f of the TField type 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 has been reached.

ADOTable1.Next

implements the transition to the next record of the table.

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.

1. Launching the application for execution.

Now you can start the application for execution.

In any Access report that contains numbers, you can use totals, averages, percentages, or stacked amounts to make the data easier to understand. This article explains how to add these items to your report.

In this article

Types of aggregates that can be added to the report

The following table lists the types of aggregate functions in Access that you can add to a report.

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

Add a sum or other aggregate in layout mode

Layout mode provides the most quick way adding sums, averages, and other aggregates to the report.

Data(Control Source) expression that performs the desired computation. If the report has grouping levels, Access also adds a text box that performs the same calculations on each section of the group note.

For more information on how to create grouping levels in reports, see the article Create a grouped report or pivot report.

Add 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 note 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 computation. If the report has grouping levels, Access also adds a text box that performs the same calculations on each section of the group note. If for the property Accumulated amount(Running Sum) set to For everything, then the grand total can be repeated in the report header. Create a field in it and set its property as the value Data(Control Source) the name of the field in which the accumulated amount is calculated, for example = [Order Amount].