Creation of calculated fields. Create a field that is calculated in a form You can create a calculated field in a form

Vidomo what's in the database Microsoft Access The fields that are calculated can be created in a query, form or name. Let's take a look at the creation of calculated fields at the base data Access 2003. To enter Virase into the form, you need to open the Design mode and create a free text field, the vikory button " ab" (Field) on the toolbar. To the left of the created free field, there is an icon associated with the text field, where the serial number of the entered field is displayed.

A free field means that it is not connected with the same field. In this field, you can enter the DB table field or Viraz. The label is displayed to enter the field name. When you enter the “Virazi” field, which calculates the necessary values, it becomes calculated. For the fields that are calculated, you can have vikory functions, for example, the function date or hour, and you can enter a expression that will vikory as input data the values ​​of the fields in the Access database table. You can insert a stand-alone text label on the form. The label is not associated with the text field, for which you need to use the " button" Aa" (Write) on the toolbar.

Let's take a look at the creation of the calculation field in the Students form on the Training_students_VP database. Statement of the problem: in the Access database form Students_Designer, create one field in which the exact date can be displayed, and another field in which the student’s nickname, name and father’s name are displayed. To complete this task, open the Student_Constructor form in the Design mode (Fig. 1).


Rice. 1.

To create a calculated field in an Access database form that displays the exact date, you must enter the following operations:

  • press the button " ab" (Field) on the toolbar, and then click on the form where you plan to insert a free text field;
  • then place the cursor near the "Vilniy" field (Fig. 2) and enter the function: =Date();
  • upcoming deadline - change label name text field(Small 2), for this requirement, remove the serial number (for our application, Field 30) and enter the word " Date of:";



Rice. 2.

  • change the length of the labels and text fields so that they fit together;
  • format the calculated field (if necessary), for which you need to right-click on the field that is being calculated, and in context menu Select the Power command and the “Field” dialog box will open. Formatting occurs in the “Field” window on the “All” tab in the “Field Format” row (Fig. 3);


Rice. 3.

  • close the Field window;
  • look at the Access database form in form mode by clicking on the Open button (Fig. 4);



Rice. 4.

To create another calculated field in the Access database form, which is displayed in the form that is displayed as input data for the table field values ​​(Name, Name, By Father), you need to enter the following steps:

  • Add the form to the left field (Figure 5);



Rice. 5.

  • change the name of the text field label to PIB;
  • Click on the alarm clock, for which click on the toolbar on the "Wake Up" icon, the Weekday window will open, in which you need to see "virus" (Figure 6) and click OK;

The simplest field to be calculated is the field in which the exact date is displayed. To create this field:

  1. Create a free element of ceramic type Field(Text Box) in the required form.
  2. For the field, enter =Date(). A sign of zeal is obligatory.
  3. Open the window Powerful(Properties) for this element, set the power Field Format(Format) default date format, for example Long date format(Long Date).
  4. Insert the last number of fields in such a way that each one has the required number of characters.
  5. Change the name of the text field label. The field was created as shown in Fig. 9.47.

Rice. 9.47.

Select for the creation of fields that are calculated, select the control element itself text field, want ce y neobov'yazkovo. It is allowed to vikorist for whichever elements of the keruban that may be in power Dani(Control Source).

Himself in power Dani(Control Source) there are a number of virus tasks that calculate the value of this element. The introduction of the virus is allowed in the control element as it was created in the application. However, since it is difficult to do this for a long time, it is not easy to enter directly into the field. You can enter the word from the comrade of power Dani(Control Source) at вікні Powerful(Properties), and if you don’t want to be in the middle of power, just press the key combination +open the window Input area(Zoom). You can still quickly contact Pobudovachem Viraziv.

Calculation fields are most often created in tabular or line forms to display the values ​​associated with calculation values. For example, the quality of the product is insured according to the value of the Price * Quantity. There are two ways to visualize such dynamic data:

  • The first way is based on the fact that the form will be based on the query, which includes counting items. A text field is created in the form, in which, as a result of the data, you can specify the calculations in the first line. The field is blocked so that the user cannot change the data, even if the data is calculated during the registration process.
  • In other cases, the form may be located directly behind the table. It creates a text field in which the data contains the following expression, for example = [Price per unit] * Quantity. In this case, the virus does not necessarily specify fields that are included in the form, or fields in the base table.

The first method in most cases is short-lived, and the remainder of this sequence may be in one form, but also in other forms. An example of such organization of orders can be the form “Orders Subform” of the “Northwind” database. It is based on the entry “Order Details Extended”, in which the “ExtendedPrice” field is created to fill in the formula.

Before creating the fields that are being calculated, you must open or generate a table. The application has a table, shown in Fig. 5.6.

Now that the table has been created in our order, the time has come for the creation of the field to be calculated. To create a field, activate the Insert Calculated Field dialog box.

To open it, on the contextual tab of the Options page, selected from the group of contextual tabs Working with pivoted tables (PivotTable Tools), go to the Calculations group, click on the button Fields, Items & Sets Sets) and select the Calculated Field command from the pop-up menu, as shown in Fig. 5.7.

A dialog box will appear on the screen: Insert Calculation Field, as shown in Fig. 5.8.

At the top of the dialog box there are two text fields: Name and Formula. For these fields, you need to specify a field name and create a formula that includes the required data fields and mathematical operators. In Fig. Figure 5.9 shows that the calculated field is given the descriptive name Average revenue per year. This name can accurately characterize the type of mathematical operation involved.

Then go to the Fields list and then click on the Sales Agreement field. Enter the oblique boundary symbol / to enter Excel programs, then you will divide the value of the Sales Commitment field to another element.

After entering the Formula text field of the dialog box Insert a field to be calculated, enter the value = 0. Before entering the formula, remove the zero.

From this point on, the dialogue window may look as shown in Fig. 5.10.

Rice. 5.10. Start entering value formula = 'Sales commitment'/

Finish entering the formula by double-clicking on the Sales period (in years) field. The finished formula is shown in Fig. 5.11.

Rice. 5.11. The same formula = 'Sales volume' / 'Sales period (in years)' displays the required field to be calculated

Click on the Add button, and then click on the OK button to activate the new field that is being calculated. Yak Bachimo in Fig. 5.12, as a result you subtract the calculated field in the middle compiled table.

Please note that after the creation of a new field, the average revenue per year is added to the list of fields.

The result is that if you remove the additional fields that are being calculated, they will not be formatted. This data can be formatted using additional techniques described in the section Creating a Basic Spread Table.

Chi means what did you add to the dzherelo tributes? No. The calculated fields are similar to the elements Subtotal and Grand Total of the table that are specified for cleaning, leaving behind the stink. mathematical functions, which will conclude the rearrangement of data when changing and updating the table. The fields that are calculated simply have the strictly specified fields in the data field. You can drag them around, change the field settings, and also change the fields at the same time as they are being calculated.

Take a good look at the fig. 5.11. Please note that the format of the formula you entered is similar to the standard series that is being analyzed. Excel formulas. The obvious significance lies in the fact that, instead of vikoristan, the most important messages were sent to the computers or numerical values You are looking at the data fields of the summarized table for the meaning of the arguments stacked in the breakdowns. If you have already worked with formulas in Excel, you will quickly grasp the concept of creating fields that are calculated.

Calculated fields are designed to display the value of expressions based on the output data. The calculation fields in the market can be used to remove the bag values ​​and carry out special calculations, for example, to show the actual transaction price by calculating the cost of one product per item. Let's take a look at the procedure for creating calculation fields from the example of “Goods in stock”, created earlier.

To add the "Amount" field:

  1. Open the "Items in stock" option in the Design mode.
  2. Click the button on the elements panel Written(Label) and place the new right-hand entry under the “Units in Stock” entry in the header section. Enter the word “Sum” to write the text.
  3. To create the “Sum” field, press the button Field(Text Box) on the elements panel and add a right-handed text field to the “In Stock” text field (UnitsInStock) for the data section. Remove the new text field icon.
  4. Click in the middle of the text field and enter the following value:

    = [In stock]* [Price] (= * ).

    Open the window of the authorities fields, two clicked on new, and on the deposit Layout(Format) in the field with the list of power Field Format(Format) select value Groshovy(Currency).

Respect

You can enter a word in the text field in a number of ways: see it and press the key combination +to enter edit mode instead of a field, or double-click on the field to display a dialog box Powerful(Properties), and then enter the expression as the meaning of power Dani(Source).

Rice. 6.27.

  1. Repeat lines 3 and 4 in order to create a text field in the notes section of the “Category” group, and then to calculate the intermediate value of the next row, enter: =Sum([InStock] * [Price]) (=Sum( *) )). Press button Napivzhirny(Bold) in the Format panel to set the text in the new field to bold. Open the dialog window Powerful(Properties) and open it in the new tab Others(Other), near the field I'm(Name) Enter the name of the text field "Sum" (Sum). Then open the tab Layout(Format) and the field with the list of power Field Format(Format) select value Groshovy(Currency).
  2. Since the width of our data area has increased, we need to change the width of the side fields so that all fields are placed on one side. For whom choose a team File, page parameters(File, Page Setup) and set the width of the left and right margins for the border to 10 mm (div. Divide. “Perspective of the other stars” in this section).
  3. Press button View(View) on the toolbar Designer of sounds(Report Design) to evaluate the results of your work. It looks like it is shown in Fig. 6.27. Until recently, the far right was given to the significant price of skin products in the warehouse and intermediate bags for the price of goods for the skin category.

This article talks about calculating fields in Access queries. In an entry, as in a table, for each entry, calculations can be carried out with numeric, serial values, or date values ​​with variable data from one or many fields. The calculation result is added to the table and a new field is added to the calculation. In the view of calculated table fields, fields that are calculated are not created in the output database tables. In case of a skin infection, the calculations are due to the adjustment of the exact values ​​of the fields.

In the types of fields that are calculated, in addition to field names, constants and functions can be added. As a result of virus processing, less than one value may be obtained.

Zavdannya 1. The PRODUCT table has the PRICE and RATE_PDV fields, calculate the price based on the PDV and equalize it with the one found in the calculated field of the Price from the PDV table.

  1. Create in design mode will prompt you to select the PRODUCT table. Drag the form to enter the fields HIRE_TOV, PRICE, RATE_MAP and Price with MPA (Fig. 4.6).
  2. To adjust the price according to the maximum allowance, create a field that is calculated by writing in the empty middle of the Field row the expression [PRICE]+[PRICE]*[RATE_MAY].
  3. To select records with values ​​greater than 5000 from the field that is being calculated, enter > 5000 in the Umov selection row (Criteria).
  4. After entering the virus, the system automatically forms the name of the field that is calculated by Viraz 1, which becomes the heading of the column in the table with the results of the entered entry. This should be inserted before the expression [PRICE]+[PRICE]*[RATE_PDV]. For a new skin field, the entry number increases by one. The names of the fields that are being calculated are reinforced with a double dot. To change the name, place the mouse cursor in the field of the form, enter what is being calculated, and click right button Misha. Select from the contextual menu Powerful(Properties) fields and in the Caption row, enter a new field name - Price with PDV1. Now, in the table with the results of the calculation, the column that is being calculated will be entered into the header, and the name will be displayed. The names of the fields can be corrected out of the middle on the form.
  5. To display the result of the display, press the button Viconati(Run) at the group Results(Results). The table field is calculated and the same values ​​can be entered.
  6. Change the price of the product in one of the entries. The values ​​in both fields that are calculated will be instantly over-inflated.
  7. To form a folding expression in the field that is being calculated, or wash the selection to completely vikorist the alert of the expressions. The wake-up call allows you to select the field names required by the virus from tables, queries, operation signs, and functions. Remove the field that is being calculated, and use the alarm clock for your forming.
  8. Select Expression Builder by clicking the button Budivnyk(Builder) in the group I'll ask you about it(Query Setup) pages Design (Design), or by selecting stay(Build) for the context menu. The mouse cursor is to blame for the installation of the virus in the first place.
  9. The left side has a window Monday morning Viraziv(Expression Builder) (Fig. 4.7) select the PRODUCT table for which the request was made. Right-handed, a list of fields will be displayed. Consistently select the required fields and operators, inserting them into the sub-clicks in the form. The crust will form at the top of the window. Please note that before the field names, indicate the name of the table that is to be followed, and add the name of the field as a sign.
  10. Complete the process by entering the field to be calculated by clicking on the OK button.
  11. Save the request under the name - Price from VAT and close it.
  12. Select your savings by seeing it in the navigation area and selecting the Open command in the context menu.


Zavdannya 2. In the calculation fields and in the minds of the selection, you can select the generated functions. Access has over 150 functions.
You must select all the invoices for which there was a promotion for a given month. In the INVOICE, the date of receipt is stored in the DATE_OTG field with the data type Date/Time.

  1. Create in design mode and select the INVOICE table. Drag the fields NOM_NAKL and CODE_SK from the form (Fig. 4.8).
  2. Create a counting field in the empty middle of the row Field(Field), writing one of the following expressions there: Format([INVOICE]![DATE_VIDGR];"mmmm") - this function is again named month
    or Format([INVOICE]![DATE_VIDGR];"mm") - this function rotates the month number.
  3. To select invoices issued for a given month, in the field to be calculated, in the Umov selection row (Criteria), enter the name of the month, for example, Berezen (small 4.8), or the number of the month, for example, 3 is consistent with the parameter ii Format.
  4. Vicont by pressing the button Viconati(Run) at the group Results(Results) on the page tab Work with questions | Constructor(Query Tools | Design).
  5. Write the function Month (INVOICE! DATE_OTG) to the calculated field and find out that this function rotates the views from the date to the month number.
  6. To select all rows that belong to another quarter, enter the operator Between 4 And 6 in the Umov Selection (Criteria) row, which means that the values ​​fall within the range of the interval indications.
  7. Write the expression MonthName (Month (INVOICE! DATE_OTG)) in the calculated field and go to where the MonthName function converts the month number into the new name.


To reinforce this, watch the video tutorial.

Application © 2022 Mobile and computer gadgets