An example of calculation of sum in the table of database Microsoft Access using SQL-query. The component TDBText
Sometimes, in the table of database, you need to calculate the sum of numbers of the some column. For this, there are different ways of calculation. One of them is based on the using of SQL-query.
In the task, for an example of table Microsoft Access, the sum is calculated using a SQL-query. The result of sum calculation is saved in the DBText component.
Using this example, you can perform any numerical calculations on the set of records of specified column.
The task
Is given the table, which was formed in Microsoft Access. The table contains data with salary of employees.
Table has the following structure.
Application must calculate the sum of salary of all workers. The result of sum must be displayed in the component of TDBText immediately after loading the application.
Progress
Create the application as “Windows Forms Application”.
- Building the form.
From the Tool Palette you need to place on the form following components (Figure 1):
– from tab “dbGo” the component TADOConnection. The object with name “ADOConnection1” is created. This component needed to link other components with the database file;
– from tab “Data Access” the component TDataSource. The object with name “DataSource1” is created. This component links the database table ADOTable1 with component of data visualization DBGrid1;
– from tab “Data Access” the component TDataSource. The object with name “DataSource2” is created. This component links the component ADOQuery1 with component DBText1;
– from tab “dbGo” the component of type TADOTable. The object with name ADOTable1 is created. This component corresponds to the table “Workers” of database;
– from tab “dbGo” the component of type TADOQuery. This component needed for forming the SQL-query and visualization of result of this query in the component TDBText;
– from tab “Data Controls” the component of type TDataGrid. The object with name DBGrid1 is created. This component displays the table Workers of database;
– from tab “Data Controls” the component of type TDBText. The object with name DBText1 is created. This component displays the result of SQL-query, which is formed in the component ADOQuery1.
The resulting sum will be displayed in DBText1 component.
The interconnection scheme between components is displayed in Figure 2.
Figure 1. The components of main form
Figure 2. The interconnection scheme between components of program
- Connecting of database. The property “ConnectionString” of component ADOConnection1.
The process of connecting of application to database Microsoft Access is described here in details.
The application is connected to database by using the property “ConnectionString” of component ADOConnection1 (Figure 3). In the property “ConnectionString” is pointed the type of data provider and full path to database.
Figure 3. The string ConnectionString of component ADOConnection1
- The setting of components of the form.
The next step you need to realize the setting of components to each other. To do this you need to set:
– in the component ADOConnection1 property LoginPrompt = “false” (disabling the password check for access to database);
– in the component ADOTable1 property Connection = “ADOConnection1”;
– in the component ADOQuery1 property Connection = “ADOConnection1”;
– in the component DataSource1 property DataSet = “ADOTable1”;
– in the component DataSource2 property DataSet = “ADOQuery1”;
– in the component DBGrid1 property DataSource = “DataSource1”;
– in the component DBText1 property DataSource = “DataSource2”;
– in the component ADOTable1 property TableName = “Worker”;
– in the component DBGrid1 from property Options set the option dgEditing = false (Disabling of entering data into the cells of the table directly from the grid DBGrid1).
- Property Active of component ADOTable1.
To display data into DBGrid1 you need to do the following actions over the component ADOTable1 (Figure 4):
– select the component ADOTable1;
– set the property Active to “true”.
After that, the data of table will be displayed in DBGrid1.
Figure 4. The “Active” property of component ADOTable1
- Setting of size of components.
At this stage is realized the correction of the size and position of the component on a form conforming to the model, is shown in Figure 5.
Figure 5. The main form of application
- Hide the field ID_Worker in DBGrid1.
Field ”IDWorker» expedient hide, because it is a key field. For this, from the context menu you need to call the editor “Fields Editor” of component ADOTable1. How to call the editor is shown in Figure 6.
Figure 6. Call of editor “Fields Editor“
Window Form1.ADOTable1 will be opened. With the help of mouse, in this window, you need to call the context menu. In the context menu you need to select the command “Add all fields”.
As a result, the editor window will have the following view (Figure 7).
Figure 7. Fields editor “Form1.ADOTable1”
In the fields editor are displayed all fields of table “Worker”. To delete the field ID_Worker, you need to click the right button of mouse on the string “ID_Worker” and in the context menu select the command “Delete”. After that you can close the editor Form1.ADOTable1.
As a result, the two fields will be displayed in the table DBGrid1 (Figure 8).
Figure 8. Showing of “Worker” table with two fields.
- Setting a filter in the field Salary to display two characters after the decimal point.
In order to, in the field “Salary”, correctly display the value of the sum (2 decimal places), do the following.
Select the component ADOTable1. Call the fields editor “Fields Editor…” as described in paragraph 6. This will open a list consisting of two fields “Name” and “Salary”. Select the string named “Salary”. In the Object Inspector the object named “ADOTable1Salary” will be activated.
Next step, in Object Inspector in the field “Display Format” you need to set the value “0.00” (Figure 9).
Figure 9. Set of output format in the “Salary” field of “ADOTable1” object
After performing, the value in the Salary table will be displayed with a precision 2 decimal places.
- Setting Label1 component.
In the Label1 component in property Caption need to type string “Sum = “.
- Property SQL of component ADOQuery1.
DBGrid1 component displays the sum based on the SQL-query. The SQL-query is formed in the component ADOQuery1 in the property “SQL…” (Figure 10).
Figure 10. Property SQL of component ADOQuery1
After selecting this property, the editor “String List Editor” will be called. In this editor you need to type the text of SQL-query (Figure 11).
For calculating the sum you need to type the following text of query:
SELECT SUM ([Salary]) AS [Sum] FROM Worker
After that, text of SQL-query you need to confirm by selecting of command (of button) “OK”.
In the SQL-query the aggregate function SUM will be called. This function is included into syntax of SQL-language. Function “SUM” calculates the sum of records of given column (column “Salary”). Also, in the SQL-query is formed a new field with name “Sum”, which displays the result of sum.
Aggregate functions provides some generalized information.
The SQL-language includes different aggregate functions, which you can use in the programs:
– COUNT – counts the records in the table or count of nonzero values in a table column;
– MIN – returns a minimal value in the column;
– MAX – returns a maximum value in the column;
– AVG – calculates an average for values of column.
- Property Active of component ADOQuery1.
As mentioned earlier, the result of SQL-query is displayed in DBText1 component. SQL-query is formed in the component ADOQuery1. To display the result of SQL-query in ADOQuery1, you need the “Active” property set to value “true” (Figure 12).
Figure 12. Property Active of component ADOQuery1
- Property DataField of component DBText1.
DBText1 component is designed to display only one value. This value can be a result of aggregate function or value of current record of some field. Therefore, this component contains the property DataField. This property specifies a field that should be displayed in DBText1 (Figure 13).
In our case, the field is named «Sum”. This name was entered in the text of SQL-query.
Figure 13. Property DataField of component DBText1
After setting of field DataField the result of sum will be showed in component DBText1 (Figure 14).
Figure 14. Displaying the sum in the component DBText1
- The setting of result output with the precision 2 of decimal places in the component DBText1.
Since DBText1 component receives data from ADOQuery1 component, you need to configure ADOQuery1 component.
The sequence of steps is the following.
Call the fields editor “Form1.ADOQuery” of component ADOQuery1 from the context menu (see paragraph 7).
In the fields editor you need to call the context menu and to select the command “Add all fields” (Figure 15).
Figure 15. Command “Add all fields” from the fields editor Form1.ADOQuery1
As a result, the one field “Sum” will be added, which is formed in the text of SQL-query. Automatically, the object named “ADOQuery1Sum” will be added.
After selection of field “Sum”, in the Object Inspector you need to set the property DisplayFormat to the value “0.00” (Figure 16).
Figure 16. Setting the output result with 2 decimal places in DBText1
- Run the application.
Now you can run the application.
Related article:
An example of sum calculation in the table of Microsoft Access database without SQL-query.