An example of calculation of sum in the table Microsoft Access without using the SQL-query

Sometimes in the table of database, you need to calculate the sum of number values of some column. To do this, there are different methods of calculation, one of them is based on using of corresponding SQL-query.

In the task, by using the example of MS Access table, is calculated the general sum without using the SQL-query. In addition, the average of cells of given column, is calculated.

By using the example, you can realize any calculations on the set of records of given column.

 

The task

Is given the table, which was formed by Microsoft Access. Table contains data of the salary of workers.

The table has the following structure.

After loading, the application must calculate the general sum of salary and arithmetic average. The result must be displayed in the components of TStaticText type.

 

Progress

1. Run Delphi.

Create an application as VCL Forms Application.

Save the project under any name.

 

2. Creating a form.

Place on the form the following components (Figure 1):

• from the tab “dbGo” component TADOConnection. The object, named as “ADOConnection1”, will be created;
• from tab “Data Access” the component TDataSource. The object with name “DataSource1” will be created;
• from tab “dbGo” the component of type TADOTable. The object with name “ADOTable1” will be created;
• from tab “Data Controls” the component of type TDataGrid. The object with name “DBGrid1” will be created;
• from tab “Additional” the two components of type TStaticText. Objects with names “StaticText1” and “StaticText” will be created.

Component “StaticText1” displays the general sum of salary of all workers.

Component “StaticText2” displays the average of salary of one worker.

Figure 1. The components of form

 

3. Database connecting. The property “ConnectionString” of component ADOConnection1.

The detailed process of connecting to the database Microsoft Access is described here.

As a result the string “ConnectionString” in component ADOConnection1 is formed (Figure 2). This string points the type of data provider and path to the database file.

Figure. 2. String “ConnectionString” of component ADOConnection1

 

4. Setting up of components ADOConnection1, ADOTable1, DataSource1, DBGrid1.

To show the database table, you need to set the following properties of components:

– in the component ADOConnection1 property LoginPrompt = “false” (Figure 3) (disable the password checking);

– in the component ADOTable1 property Connection = “ADOConnection1” (Figure 4);

– in the component DataSource1 property DataSet = “ADOTable1” (Figure 5);

– in the component DBGrid1 property DataSource = “DataSource1” (Figure 6);

– in the component ADOTable1 property TableName = “Worker” (Figure 7);

– in the component DBGrid1 from property Options set the option dgEditing = «false” (Figure 8).

Figure 3. Property LoginPrompt of component ADOConnection1

Figure. 4. Property “Connection” of component ADOTable1

Figure. 5. Property DataSet of component DataSource1

Figure 6. Property DataSource of component DBGrid1

Figure. 7. Property TableName of component ADOTable1

Figure. 8. The option dgEditing of property Options of component DBGrid1

5. Activation of table.

To display data in table you need to do following actions (Figure 9):

– select the table ADOTable1;

– property Active = “true”.

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

Figure. 9. Property Active of component ADOTable1

 

6. Setting up of size of components and form.

Next step, you need to set up the size and position of components on the form as shown in Figure 10.

Figure 10. The main form of application

 

7. Hiding of field ID_Worker in DBGrid1.

To view the table correctly, you need to hide the field ID_Worker, which is the primary key. In the table, this field is a counter. When you add a new record, the value in this field will be formed automatically (increased by 1).

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

Figure 11. The calling of fields editor of component ADOTable1

The window Form1.ADOTable1 will be opened. In this window, with the help of mouse, 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 12).

Figure. 12. Fields editor

In the fields editor Form1.ADOTable1 the all fields of table Worker are displayed. To delete the field ID_Worker, you need to click the right mouse button, and in the context menu select select command “Delete”. After that, you can close the editor.

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

Figure 13. The displaying of table Worker with two fields

 

8. Setting of filter of output with the two characters after the decimal point in the Salary field.

To correctly display the value of sum (2 decimal points) you need to do following actions.

Select the component ADOTable1. Call the “Fields Editor…” as described in p.6. This will open the list of two fields “Name” and “Salary”. Select the string named “Salary”. In the Object Inspector the object with name “ADOTable1.Salary” will be activated.

The next step, you need to set the value “0.00” in the field “Display Format” (Figure 14).

Figure 14. Setting the output format in the field “Salary” of object ADOTable1

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

 

9. Input of internal variables.

To save the sum and average you need to input the internal variables named “sum” and “avg” in the text of class of form “Form1”.

Variables inputted in the “private” section. The code snippet of class TForm1 of form has the following view:

...

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; // the sum
    avg:real; // average
  public
    { Public declarations }
  end;

...

 

10. Programming the event of form activation.

When user runs the program, it is needed to calculate the sum and average of field “Salary” of table.

Therefore, you need to program the event “OnActivate” of form Form1. The event OnActivate is called at the moment of form activation after running the program by user.

An example of event programming in Delphi is described here in details.

In our case, the event handler has the following view:

procedure TForm1.FormActivate(Sender: TObject);
var
  f:TField; // additional variable of type “TField”
begin
  // 1. Checking, whether are records in the table?
  if ADOTable1.RecordCount = 0 then exit;

  // 2. Disable the visualization in DBGrid1
  ADOTable1.DisableControls;

  // 3. Enumerating of all records of table "Worker ".
  // 3.1. Go to the first record
  ADOTable1.First;

  // 3.2. Set the sum to zero.
  sum := 0;

  // 3.3. Take the value of the salary of the first record.
  f := ADOTable1.FieldByName('Salary');

  // 3.4. Traversing the table.
  //      - checking, is achieved the end of the table?
  while ADOTable1.Eof<>true do
  begin
    // 3.4.1. Increase the sum
    sum := sum + f.Value;

    // 3.4.2. Go to the next record
    ADOTable1.Next;
  end;

  // 4. Calculate the arithmetic average
  avg := sum / ADOTable1.RecordCount;

  // 5. Fill the strings of type TStaticText
  StaticText1.Caption := 'Сумма: ' + FloatToStr(sum, ffFixed, 8, 2);
  StaticText2.Caption := 'Средняя зарплата: ' + FloatToStr(avg, ffFixed, 8, 2);

  // 6. Enable visualisation in DBGrid1
  ADOTable1.EnableControls;
end;

Let’s explain some code snippets.

Methods DisableControls() and EnableControls() realize the disabling and enabling the data which are connected to the dataset of visual controls (DBGrid1).

Call DisableControls method can significantly accelerate the process of enumeration records because the application does not make spending time on redrawing DBGrid1 content control with every change of the record.

Property

ADOTable1.RecordCount

indicates the number of records in the table.

Method

ADOTable1.First

sets the first record of table as active.

Method

ADOTable1.FieldByName()

allows to get the object of type TField for selected field. Then you can get the information about current record. In the program, to get access to the records of “Salary” field, is used the additional variable f of type TField. To read the value of current record of “Salary” field you need to enter

f.Value

Property

ADOTable1.Eof

becomes “true”, if it is reached the end of the table.

Method

ADOTable1.Next

implements the transition to the next table record.

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

The average is outputted in the Caption property of component StaticText2.

 

11. Run the application.

Now, you can run the application.

 

Related articles:

013 – An example of calculation of sum in the table of database Microsoft Access using a SQL-query. The component TDBText