Sometimes there is a need to present subtotals in the form, or to do any other calculations. For these purposes, you can create a calculated field in the form.

A calculated field can be present in both forms and reports. In this case, in the form, it is created in the data area using the "Field" control. For this:

1. Open the form in which you will create the calculated field in design mode.

2. In the toolbox, select the Field element and place it in the desired location in the data area.

3. Make the alignment of the field label and the field itself as you wish.

4. Double-click the mouse button to enter the field label editing mode and enter the name this field (the name of what the formula will count).

5. Click the right mouse button to open the context menu for the field itself and select Properties.

6. Select the Data tab and using the Expression Builder in the Data field, enter the required formula, starting with an equal sign:

· Double-clicking on the name of the form field (middle area of \u200b\u200bthe builder) will place it in the created formula;

· Clicking on an arithmetic sign will also allow you to place it in the created formula.

7. Note: You can not use the Expression Builder to create a formula, but enter it in the editing mode of the "Data" part (where "Free" is indicated) of the selected field. In this case, the names of the fields of the tables are enclosed in square brackets, and they must fully correspond to the name specified when creating the table, respecting the case and all characters, including spaces.

8. Save the created form and open it in form mode. Return to design mode if errors are found.

Tasks

1. Create a field that calculates the cost of each shipment for Library Instances.

2. To do this, use the above sequence of actions and the following notes:

· A calculated field must be created in the "Delivery subform" form;

· Open this form in the "Design" mode;

· Select the "field" control;

· In the field caption enter "Delivery cost", in the field the formula \u003d [Price] * [Quantity] (where [Price] and [Quantity] are the names of the corresponding fields of the "Delivery" table that are used in the form to display the required data).

3. Open the "Delivery Subform" form and verify that the calculated field is working correctly.

4. Open the "Instance" form, see if the newly created field is visible. If necessary, resize the subform so that all data is displayed.

5. Save the changes, show the result to the teacher.

Creation of the main button form

The main button form is the form that is loaded first when the database is opened. This form should contain buttons, by pressing which you can have access (open, print, etc.) to all objects of your database: forms, queries, reports. In addition, it must have an exit button (as on each of the forms), or a return button to the main form.

When creating the Main Button Form, you can use:

1) Constructor mode ();

2) Manager of button forms (menu Service / Utilities / Manager of button forms).

To create a button form in Design mode you need to follow the sequence of actions presented below.

1. Creating a "layout" of the form:

· On the Form tab, select the Create command;

· Specify the Design Mode as the form creation mode;

· Do not specify the data source for the form (the newly created form has only a data area);

· Save the form as "Home".

2. Inserting a graphic object into a form:

· On the Toolbox select the Picture control;

· Place it in the data area;

· In the dialog box that appears, select a graphic file (* .bmp) to insert it into the data area;

· You can change the properties of an object by calling the context menu for it and using the required commands.

3. Creating a button:

· On the toolbar, select the button control;

· Place it in the data area.

· Select from the list of categories that you need (for example, Working with a form);

· From the list of actions, select the one that you intend to carry out using this button (for example, Closing the form). Click "Next;

· Select the most suitable, in your opinion, picture, it will be displayed on the button. If you want, you can select text as the designation of the function of the button you are creating. Click "Next.

· Give a name to the newly created button. It is recommended to keep the system numbering (the default name is "N button", where N is a number in order).

· Click the Finish button;

· You can change the appearance and location of the newly created button using the context menu and its corresponding commands;

· Open the form in form mode, check the button operation.

Tasks

1. Build the main button form for the educational task, guided by the information in the item "Creating the main button form" and the following requirements:

· On the form there should be a drawing corresponding to the topic of the educational problem;

· Each elements of the created system should be accessible from a separate tab: forms, reports, requests;

· The form must have buttons for launching elements and closing the form;

· Buttons for launching queries and reports may be absent or exist in the form of "stubs", that is, buttons without an assigned action;

· On each of the previously created forms there should be buttons to close the form and go to the Main form (create the necessary buttons on the "Supplier" and "Instance" forms you created: create a new record, delete a record, close the form and go to the main form) (see. fig. 5, 6).

Note:buttons and other controls that allow you to work with the rest of the database objects will need to be added when performing subsequent laboratory work (11 and 12).

2. Save the Main Form.

3. Check its work.

4. Using the newly created forms, create one new record in each of the tables.

Questions for self-control

1. What is a form used for as an object of MS Access?

2. What areas of the form do you know, what is the purpose of each of them?

3. What is the difference between the strip method of data composition on the form ( appearance) from Tabular?

4. What is the difference between the data composition method on the "PivotTable" form and the "PivotChart"?

5. What is the difference between the data composition method on the "One Column" form and "Aligned"?

6. Is it possible to change the selected form style, if so, how to do it?

7. If you want to add a calculated field to the form, which control will you use and in which area of \u200b\u200bthe form will you place it?

8. How much of a field control can and cannot be changed, and why?

9. What is the algorithm for creating a subform in the Wizard mode?

11. Can I use the names of other calculated fields of this form when writing a calculated expression (justify the answer)?

12. Determine the purpose of the main button form.

13. What is the sequence of actions when working with the Manager of Button Forms? What are the advantages and disadvantages of this mode?

14. Does the Form Design mode allow you to create forms based on multiple tables or queries, if so, how is this done?

15. What fields when creating forms based on several tables must be entered to ensure the correct operation of the corresponding tables.

LABORATORY WORK No. 11
Designing queries in MS Access DBMS

1. Designing a query for selection.

2. Construction of requests for deletion and updating.

3. Construction of a query with a parameter.

Purpose: Learn to design select, delete, and parametric queries.

The query allows you to select the required data from one or more interconnected tables, perform calculations and get the result in the form of a table. Through a query, you can update, add and delete data in tables.

The query is built either on the basis of one or more database tables, or tables obtained from other queries are used.

Sequential execution of a number of queries allows you to solve fairly complex problems without resorting to programming. Several types of queries can be created in MS Access. As part of this lab, we will consider the features of working with queries for select, delete and update, as well as with parametric queries.

Calculated fields are designed to display expression values \u200b\u200bbased on source data in reports. Calculated fields in reports can be used to obtain totals or perform special calculations, for example, to display the total price of an order by multiplying the quantity of an item by the price of a single item. Let's look at the procedure for creating calculated fields using the example of the "Goods in stock" report that was created earlier.

To add the Amount calculated field to your report:

1. Open the Stock Items report in Design view.
2. On the toolbox, click Inscription (Label) and place a new label to the right of Units in Stock in the header section. Enter the word "Sum" for the caption text.
3. To create the Sum calculated field, click Field (Text Box) in the toolbox and add a text box to the right of the UnitsInStock text box in the data section. Remove the label for the new text box.
4. Click inside the text box and enter the expression as its value:

   \u003d [In Stock] * [Price] (\u003d * ).

   Open the properties window fields by double clicking on it and in the tab Layout (Format) in the combo box properties Field format (Format) select a value Monetary (Currency).

Comment

There are several ways to enter an expression into a text box: select it and press the key combination +to enter edit mode for the field content, or double-click on the field to display the dialog Properties (Properties) and then enter expression as property value Data (Source).

Figure: 6.27.

1. Repeat steps 3 and 4 to create a text box in the Notes section of the Category group, but enter: \u003d Sum ([InStock] * [Price]) (\u003d Sum (* )). Click the button Bold (Bold) in the format bar to set the text to bold in the new field. Open the dialog Properties (Properties) and open the tab in it Others (Other), in the field Name (Name) enter a name for the Sum text box. Then expand the tab Layout (Format) and combo box properties Field format (Format) select a value Monetary (Currency).
2. As the width of the data area of \u200b\u200bour report has increased, reduce the width of the page margins so that all the margins fit on one page. To do this, select the command File, Page Setup (File, Page Setup) and change the default left and right margins to 10mm (see the section "Viewing and Printing Reports" in this chapter).
3. Click the button View (View) on the toolbar Report Designer (Report Design) to evaluate the results of their work. The report will look like the one shown in Fig. 6.27. The column on the far right has been added to the previous data, with the values \u200b\u200bof the value of each product in the available quantity in stock and subtotals for the value of the goods for each category.

The simplest calculated field is the field that should display the current date. To create such a field:

1. Create a loose control like Field (Text Box) in the right place on the form.
2. Enter the expression \u003d Date () right in the field. The equal sign is required.
3. Open the window Properties (Properties) for this item and set the property Field format (Format) the desired date format like Long date format (Long Date).
4. Set the length of the field to fit the required number of characters.
5. Change the label name of this text box. The created field will look as shown in Fig. 9.47.

Figure: 9.47.

Typically, the control is chosen to create calculated fields. Text field, although this is optional. It is allowed to use for this any controls that have the property Data (Control Source).

It is in the property Data (Control Source) must be an expression that evaluates the value of this element. Expression can be entered directly into the control, as we did in the example. However, if the expression is long enough, it is inconvenient to enter it directly into the field. You can enter an expression into a property cell Data (Control Source) in the window Properties (Properties), and if the expression does not fit in the property cell, just press the key combination + and open the window Input area (Zoom). You can also use the Expression Builder.

Calculated fields are most often created in tabular or striped forms to display a column of some calculated value. For example, the cost of an item is calculated as the value of the expression Price * Quantity. To display such calculated data, two methods are used:

• The first method is based on the fact that the form is built on the basis of a query, which includes calculated columns. A text box is created in the form with a calculated query column specified as the data source. The field is blocked so that the user cannot change the data in it, because this data is calculated during the execution of the request.
• In the second case, the form can be built directly on the basis of the table. It creates a text box that specifies an expression as the data source, for example \u003d [Unit Price] * Quantity. In this case, the expression does not necessarily indicate those fields that are included in the form, but any fields of the base table.

The first method is preferable in most cases, since such a request can be used not only in one form, but also in reports and in other forms. An example of such an organization of calculations is the "Orders Subform" form in the Northwind database. It is based on an Order Details Extended query that created an ExtendedPrice calculated field that contains a formula.

In the process of analyzing data using pivot tables, it is often necessary to report values \u200b\u200bresulting from calculations that were performed outside the original dataset. Excel provides the user with the means to perform calculations in pivot tables using calculated fields and calculated members. In fig. 1 shows how a calculated field can represent data in a different perspective. The pivot table displays the volume of sales and the time (in hours) spent on the sale of equipment in each sales area. A calculated field that calculates average revenue per hour enhances your ability to analyze data in a PivotTable.

Figure: 1. You need to create a calculated field in the existing pivot table Average revenue per hourthat will give new meaning to the analyzed data

Download a note in format or, examples in format

A calculated field is a data field that results from calculations based on existing fields in a PivotTable. The calculated field is added to the dataset as a virtual column. This column is not included in the source data, contains the values \u200b\u200bdetermined by the formula, and interacts with the data in the PivotTable in the same way as with the rest of the fields in the PivotTable.

Calculated fields allow you to insert a formula into your PivotTable to create your own field. The newly created data will become part of the pivot table, interacting with the existing data. Refreshing pivot tables recalculates calculated fields and populates the pivot table with information that was not in the original dataset.

Now look at fig. 5.1 and ask yourself, “Why did you need to add calculated fields? Why not use the usual cell formulas or do the necessary calculations directly in the original table to obtain the required information? " To answer these questions, let's look at the various methods you can use to create the calculated field shown in Figure 1.

Method 1: Add a calculated field to the data source

You can add a calculated field to your data source, as shown in Fig. 2, allowing the pivot table to use this field as a standard data field. If the arrangement of the information on the sheet with the data source allows this, then what may be the best solution. By the way, adding a new column will require not only updating the pivot table, but changing the area on which the pivot is based. To do this, click on the summary and go through the menu Analysis -\u003e Data Source -\u003e Data Source.

I am trying to turn the raw data into a Table (which is done in Figure 2 and in the attached Excel file). At the same time, firstly, headers are highlighted, secondly, buttons with filters appear, which is often useful for data exploration, thirdly, rows are colored one after another, and, finally, most importantly, when adding rows and columns is not required change the data area for the pivot table. It is not a rectangular range that is specified as such an area, but the name of the Table. In our case - Source (fig. 3).

Figure: 3. Formation of a summary based on a special tool Excel - Table

But this method also has disadvantages: firstly, it is not always convenient to add a column to the source data, and secondly, the possibilities are limited in case of changes in the structure of the source data (for example, you export data from 1C, and in the new export there is one more column; this new column will overwrite your computed column).

Method 2: Using a Formula Outside a PivotTable to Create a Calculated Field

You can add a calculated field next to the pivot table. In fig. 4 each cell in a column Average revenue per hour provided with a formula that refers to a pivot table. By the way, if, when you try to refer to a cell in a pivot table, the GET.DATA.PERSONAL.TABLES function "pops out" in your formula, then you will not be able to "drag in" the formula. To overcome this difficulty, read the note.

Figure: 4. When you enter a formula after creating a pivot table, you are essentially adding a calculated field that changes when the pivot table itself is updated

Although this method adds a calculated field that is updated when the PivotTable is refreshed, any changes to the structure of the PivotTable can render the formula useless. For example, as a result of dragging and dropping the Sales field into the FILTERS area of \u200b\u200bthe PivotTable field list, the report structure changes, resulting in an error message in the calculated field (Figure 5). If you add another field to the COLUMNS pivot table area, the Average revenue per hour will be overwritten.

Method 3: Directly insert a calculated field into a pivot table

Inserting a calculated field directly into a PivotTable will often be the best solution... If you use this method, then you will not have to customize the formulas. It will also become possible to automatically adjust the table in accordance with the changed data source. A necessary degree of flexibility will be achieved to allow automatic recalculation of fields in the event of a change in units of measure.

Another advantage of this method is that, if applied, you can change the structure of the PivotTable and even the data fields for the different units of measure used in the calculated fields. At the same time, you can be sure that there will not be errors in the formulas and cell references will not be lost.

The pivot table report shown in Fig. 6 is a pivot table as shown in Fig. 1, except that it has been revamped to output the average hourly revenue for each service and each market.

Figure: 6. The previously created calculated field remains “valid” even if the structure of the pivot table is changed, resulting in the display of the average revenue per hour for each service and each sales area

Creating a calculated field

Before creating a calculated field, you need to open or generate a PivotTable. Our example uses the pivot table shown in Fig. 1, but without column D. To create a calculated field, activate the dialog Insert a calculated field... To do this, click on the pivot table to activate the group of contextual tabs Working with pivot tables... Go to the tab Analysis, into the group Calculations, click the button Fields, Items, and Sets and select the Computed Field command from the menu (Fig. 7).

A dialog box appears on the screen Insert a calculated field (fig. 8). There are two text boxes at the top of the dialog: Name and Formula... In these fields, you must specify a name for the calculated field and create a formula by specifying the required data fields and mathematical operators. Calculated field has a descriptive name Average revenue per hour... The name should be chosen to accurately describe the type of mathematical operation being performed. Default text box Formula dialog box Insert a calculated field contains expression \u003d 0. Remove zero before entering formula. Select one of the fields in the area Fields; in our case - Volume of sales and click Add field... The name of the field will appear in the line Formula... Enter the division sign / and then select the second field - Sales period (in hours)... Click Add to, and then OK to activate a new calculated field.

A new calculated field appears in the pivot table Average revenue per hour (see fig. 1). A new calculated field has just been successfully added to the pivot table. You can now change the settings for this new field just like you would for any other field (for example, field name, number format, or color).

Does this mean that you added the column to the data source? Not. Calculated fields are like items Intermediate amount and total amount the default pivot table because they are all mathematical functions that recalculate data when the pivot table changes or is updated. Calculated fields simply mimic explicitly defined fields in the data source. You can drag and drop them, change field settings, and use them in conjunction with other calculated fields.

Please note that in fig. 8 the format of the formula you entered is similar to that used in the standard line excel formulas... The main difference is that instead of using strong cell references or numeric values, you reference the data fields of the PivotTable to assign values \u200b\u200bto the arguments used in the calculations.

Creating calculated members

A calculated item is essentially a virtual row of data that is added as a result of calculations performed on other rows in the same field. Very often, you can achieve a result similar to what you get when you create a calculated member by grouping the data. Indeed, in many cases, grouping provides a great alternative to calculated members.

In the pivot table shown in Fig. 9, the total sales volume for certain reporting periods is indicated (field Reporting period). Imagine you want to compare the average sales of the past six periods with the average sales of the previous seven periods. To be more precise, we need to determine the average for the periods P01 – P07 and compare it with the average for the periods P08 – P13.

Place the pointer anywhere in the field Reporting period,go to the contextual ribbon tab Analysis to the group Calculations, and click on the button Fields, Items, and Sets... In the menu that opens, select the command Calculated object (fig. 10).

A dialog box will open (fig. 11). Notice that at the top of the dialog box it is indicated which field you are working with. In this case, this field Reporting period... In addition, the list contains all the elements of the field Reporting period... Give the calculated member a name, and then create a formula specifying the desired combination of data members and operators that will produce the correct result. The formula is entered in the field Formula, and the necessary data elements used in it are selected in the list The elements... Click on the button OK, and the calculated item is added to the pivot table (Figure 12).

Figure: 11. Dialog box Inserting a calculated member

Similarly, create a calculated member that represents the average sales for periods P08 – P13. Then hide the individual posting periods, leaving only two calculated members. After some formatting, our calculated members will be used to compare the average sales for the previous and next six months (Figure 13).

If you do not hide the data members used in calculating calculated members, the subtotals and grand totals may not be correct.

Advantages and Disadvantages of Calculating Pivot Tables

There are some drawbacks to using calculated fields and members. It is important to understand what happens when calculating data in PivotTables, but it is much more important to consider the limitations of calculated fields and calculated members so that you can prevent errors during data analysis.

The priority of the operations.Just like in spreadsheets, you can use any operator in calculated member and field formulas: +, -, *, /,%, ^, and so on. Moreover, like spreadsheets, PivotTable calculations are performed according to operator precedence. When you perform a calculation that combines multiple operators, such as (2 + 3) * 4/50%, Excel evaluates the expression and calculates in a specific order. Knowing this order will save you many mistakes.

So, the order of performing operations in Excel is as follows:

• processing expressions in parentheses;
• range processing (:);
• processing of intersections (areas);
• processing unions (;);
• performing a negation operation;
• percentage conversion (for example, 50% converts to 0.50);
• exponentiation (^);
• multiplication (*) and division (/); these operations have equal priority;
• addition (+) and subtraction (-); these operations have equal priority;
• concatenation of text data (&) this operation is also called concatenation;
• comparison operations (\u003d,<>, <=, >=).

Equivalent operators in one expression are always executed in order (from left to right).

Let's look at a simple example. As you know, the expression (2 + 3) * 4 returns the result 20. If you remove the parentheses and leave the expression 2 + 3 * 4, then Excel will calculate: 3 * 4 \u003d 12 + 2 \u003d 14.

Let's look at another example. If you enter 10 ^ 2 as the formula, i.e. if you specify to square the number 10, the program will return the value 100 as an answer. If you enter the expression –10 ^ 2, then you can expect the return of –100. However, Excel will return 100 again. The reason is that the program performs a negation operation before exponentiation; the value 10 is first converted to –10, and the result of –10 * –10 is indeed 100. Using parentheses in the formula - (10 ^ 2) ensures that the program first performs exponentiation and only then negates, returning the expected result - one hundred.

Cell references and named ranges.Cell references or named ranges cannot be used when creating calculated fields and members, because when you create calculated objects in a PivotTable, you are essentially working outside of object space. The only data available to users is in the pivot table cache. Since you cannot go outside the cache, you cannot reference cells or named ranges in the formula you create.

Worksheet functions.You can use any worksheet function that does not use cell references or named objects as arguments. In fact, you can use any function in your worksheet that does not require you to apply cell references or named objects. Functions such as COUNT, AVERAGE, IF, AND, NO, and OR fall into this category.

Constants.Any constants can be used in PivotTable calculations. Constants are static values \u200b\u200bthat do not change over time. For example, in the following formula, the number 5 is a constant: [Units Sold] * 5. Despite the fact that the value Units sold may change based on available data, 5 will always be the same.

Links to totals.Formulas that perform calculations cannot refer to PivotTable subtotals or totals. In other words, you cannot use the result of a running or grand total calculation as a variable or argument in a calculated field.

Special rules for calculated fields.Calculations in calculated fields are always performed on totals, not on individual data items. Simply put, Excel always calculates data fields, subtotals, and grand totals before evaluating a calculated field. This means that your calculated field always applies to totals for the original data. The example shown in fig. 14 shows how this can affect data analysis.

Figure: 14. Despite the fact that the calculated field is valid for individual data items, it is not mathematically correct to use it for subtotals.

For each quarter, you need to get the total sales for each product by multiplying the number of units sold by the unit price of that product. If you look at the first quarter data first, you immediately identify the problem. Instead of calculating 220 + 150 + 220 + 594, which is 1184, the sum of the number of units of the item is multiplied by the sum of the prices of the items, which returns an incorrect value. Unfortunately, this problem cannot be solved, but it can be worked around. Exclude the subtotals and grand totals from the pivot table, and then calculate a new grand total at the bottom outside of the pivot table.

Special rules for using calculated members.You cannot use calculated members in a PivotTable that calculate means, standard deviations, or variances. Conversely, you cannot use averages, standard deviations, or variances in a PivotTable that contains a calculated item. You cannot use a page field to create a calculated member, nor can you move calculated members to the filter area of \u200b\u200bthe report. You cannot add a calculated member to a report that contains a grouped field, and you cannot group any field in a PivotTable that contains a calculated member. When creating a calculated member formula, you cannot reference a member from an external field.

Managing and maintaining calculations in pivot tables

When working with pivot tables, there are often situations where it makes no sense to store the pivot table longer than it takes to copy individual values. However, there are often situations when it is more profitable to keep the pivot table and all its elements unchanged. If you maintain and manage a PivotTable by changing requirements and increasing the amount of source data, then you need to manage calculated fields and calculated members.

Modify and delete calculations in pivot tables.When you change the parameters of a calculation, or you don't need a calculated field or calculated member, you can open the appropriate dialog box to edit or delete the calculation. Activate the dialog box Insert a calculated field or Inserting a calculated member (see description for Fig. 7 and 10) and select the drop-down list Name (fig. 15).

Change the order in which calculations are performed in calculated fields.If the value of a cell in a PivotTable depends on the result of calculating multiple calculated members, you can change the order in which operations are performed in the calculated fields. In other words, you can specify the order in which the individual calculations are performed. To open a dialog box , position the pointer anywhere in the pivot table and click the icon Fields, Items, and Sets... In the drop-down menu, select the Calculation order command (Fig. 16).

Figure: 16. Open the dialog box Calculation order

In the dialog box Calculation order (Figure 17), all calculated members that are added to the pivot table are displayed. Select any calculated item in the list and use the buttons Up, Down and Delete... The order of formulas in the list determines the exact order in which calculations are performed in the PivotTable.

Documenting formulas.Excel provides you with an excellent tool that displays the calculated fields and calculated members used in a PivotTable, and also tells you how to perform calculations and apply formulas. This tool is very useful for analyzing a third-party PivotTable or when you need to quickly determine which calculations are applied in it and which fields and items they affect. To generate a pivot table calculation report, hover the pointer anywhere in the pivot table, click the icon Fields, Items, and Sets and select the command from the dropdown menu Output formulas. Excel will create a report of calculated fields and members on a separate sheet (Figure 18).

Figure: 18. Team Derive formulas allows you to quickly and easily document the calculations in the pivot table

Note based on Jelen's book, Alexander. ... Chapter 5.

The word table is written with capital letteras it is not just a spreadsheet, but a separate Excel tool.

The first way to create a calculated field is based on the fact that the form is built on the basis of a query, which includes calculated columns. A text box is created in the form with a calculated query column specified as the data source. The field is locked so that the user cannot change the data in it. The data is calculated during the execution of the request. To lock the field in the mode Constructorin the window Propertiescalculated field on the Data in Row tab Blockingenter YES.

In the second case, the form is built on the basis of a table. A new field is created in the form, in which an expression is specified as the data source, for example, \u003d [Unit price] * Quantity. When referring to fields in expressions, it is mandatory to use square brackets if the field name consists of more than one word. Expression can be entered directly into the control (field) if it is short and the field length allows it. Otherwise, the expression is entered into the property cell Datawindow Properties.However, you can use the Expression Builder. If the expression does not fit in the property cell, you need to press the + keys and open the window Input area. If necessary, set the field format when displaying.

6. Creating a button form

Select a menu command Service / Utilities / Button Forms Manager.

In the dialog box asking if you want to create a button form click YES.

A window will appear on the screen Button Form Manager... This window lists the pages of the button form. At the beginning of work in the window, one page - Main button form... In the window, click Edit.

A dialog box opens Changing the page of a button form. Correct the name of the form.

Click the button Create a... A window appears Modifying a button form element.

In field Textenter the name of the created button. The name of the button should be associated by the user with the objects with which he intends to work. Second field Commandallows you to define the action to be performed after clicking this button. Select the command required for your application from the drop-down list.

Add buttons for all forms and reports by structuring them by type.

Add a button that will exit the application, as well as a button for quick access to Button Form Manager.

Configure the app to Main button formopened when the database was opened. To do this, use the menu command Service / Startup Options.

EXAMPLE:

Figure: 28. Top-level button form

Lab 3 Building Queries

Objective -get practical skills in developing database queries.

The task:

Test the database schema on typical queries. You must create at least one request of each type described below. In the report, queries must be formulated in natural language, in SQL, and an example of the query result must be provided.

Work sequence

1. Create a select query on a single table using the Query Builder

IN dB window click on bookmark Inquiries.

Click on the button Create a.

In the window New request choose Constructorand press the button OK.

In the window Adding a tableselect from the list box the table (or query), which will be used to build the query and click the button Add. Close the current window. Next, a window will appear on the screen Query Builder.

Add the field to your request. For this in source tablelocated in the upper part of the window З sample request, select the required field. Double-click the left mouse button on the highlighted field. Moreover, at the bottom of the window Requests a column corresponding to the selected field will appear in the query form.

You can also add a field to a request in the following way: press the left mouse button on the selected field and, without releasing it, drag the field to the required place of the request form; click on a cell field in the request form to display a drop-down list of fields and select the required field from it; click on the cell field in the query design and enter the field name.

To add all table fields to a query, select the field marked with an asterisk (*). An asterisk makes it easier to construct a query, but it makes sorting and entering a condition for selecting fields more difficult.

By default, all query fields are not sorted. If necessary, you can specify in the line Sortingof the query form the way of displaying the column values: ascending or descending. When you specify a sort order in multiple fields, records are sorted first by the leftmost column, and then by each subsequent column from left to right.

In line Output on display check the boxes that you want to see in the recordset after the query is run. By default, all fields included in the request form are displayed.

In the request form in the line Selection condition and in the line Or specify the conditions for selecting from the database. Conditions are logical expressions.

A logical operation is performed on selection conditions located in one line AND... Several selection conditions for one field can be set in one of two ways: enter all conditions in one cell of the row Selection conditionby connecting them logical operator OR, or enter each condition in a separate cell of the row Or. Move to the next line in the cell area OR by key<>... Expressions in a cell of the form of a query are entered from the keyboard or used to create them Expression Builderwhich is started by the command Build from the context menu associated with the string Selection condition (or you can press the button Build on the toolbar Access).

View the query results by clicking the button Running (button with an exclamation mark) or button Viewon the toolbar.

In situations where you need to change a query property, double-click the blank area at the top of the window Query constructor -a window will open Request properties.

Queries created in modes Constructor and Mastersgenerate commands in SQL language. You can view these commands by choosing from the menu Viewoption modeSQL.

EXAMPLE:

Select information about products in quantities greater than 100, but less than 300 and costing less than 100 rubles.

Figure: 10. Constructor of a query with a condition

Figure: 11. Result of a query with a condition

2. Internal connection by one field

Select a tab Inquiriesdatabase window, click the button Create. A dialog box will open New request... Select an option Constructor.

In the window Adding a table select sequentially several related tables on which the query will be built. At the top Query Builder related tables are displayed.

Fill out the request form.

Click on the button Running or Viewto reflect the results of the query.

EXAMPLE:

Select the names of the products for the supply of which the contract has been concluded, indicating the names of their manufacturers and the scope of delivery.

Figure: 12. Constructor of multi-table query

Figure: 13. Result of a multi-table query

3. External connections

Follow steps 1 - 3 of section Internal connection one field at a time.

Establish a relationship between tables, if it was not previously registered in Scheme DB.

Click on the field merge line to select it.

Choose from the command bar View option Combine options.A dialog will be displayed Combine options.

Select the type of union, click on the button OK... After this operation, an arrow will appear on the join line indicating the type of join.

4. Inclusion of a group operation in the request form

Click the button Group operations, sign  on the toolbar Query Builder... In the request form above the line Sorting a line will appear Group operation.

Replace in line Group operation installation Grouping to the required group operationby selecting it from the combo box: Sum, Avg, Count, Min, Max, StDev, Var, First, Last, Expression (expression),Where (condition),Group by (grouping).

Expression allows you to enter an expression instead of a field name in a cell , for example, to calculate the range of values \u200b\u200bfor a certain column X:Max([ X]) – Min([ X]).

Condition indicates that this field will be used to specify the criteria for selecting records. The selection condition itself is written to the cell of the row Selection condition.

3. Complete the request.

EXAMPLE:

Select the total number of products for each manufacturer, indicating its name.

Figure: 14. Query constructor with grouping records

Figure: 15. Result of a query with grouping records

5. Including Calculated Fields in the Query Form

Query Builder .

Click in the line Fieldby an empty column of the request form. Enter the name of the column, followed by a colon, followed by an expression. To enter the expression, click on the button Build, to take advantage of Expression Builder.

At the top Expression Builderin the input area, create an expression.

In a calculated field column, move the cursor to the row Group operationand select from the dropdown list Expression (if you do not select the option Expression, when executed, the query opens a dialog box Enter parameter value or returns an error message).

Run the request.

EXAMPLE:

Calculate the total order amount for each contract (defined as the product of the product quantity and price plus transportation costs).

Figure: 16. Query constructor with calculated field

Figure: 17. Result of a query with a calculated field

6. Creating a parametric query

Create a select query in mode Query constructor.

Enter lines in one of the cells Selection condition parameter, not value. For example, if you select records by last name, then in parametric query on the field Surname in the selection conditions, the phrase [enter last name] may be written in square brackets.

Choose from the menu Requestoption Parameters... In the dialog box Request parametersin the left column, enter the parameter in square brackets that matches the parameter in request form Query Builder(the parameter can be copied from the request form). In the right column, specify the data type: press the key , select the data type from the list.

Start an execution request. Access will display a window Enter parameter value, into which enter the required parameter value and press the button OK.

5. Start the execution request.

EXAMPLE:

Select a list of products whose shelf life exceeds the specified value.

Figure: 18. Constructor of parametric query

Figure: 19. Entering the shelf life of the product

Figure: 20. Result of a parametric query

7. Create cross-reference queries

1. Create with Constructor new query and include the required tables in it.

2. Select the fields whose values \u200b\u200bwill be used to create rows and columns of the dynamic table, and drag and drop them into the query design.

3. Select the command from the menu Request / Cross.Title Constructor will change from Request 1: sample on the Query 1: Cross Query . The query will display the line Cross table .

4. For fields that will be crosstab rows, open the row dropdown list Cross table select an option Row headers. Each field that is a line header must have a setting in the Group operation line Grouping.

5. For fields that will be columns of the crosstab, depending on the query, the following actions are possible:

in line Cross table select option Column headersand in line Group operationinstallation Grouping,or in line Group operation – Condition,in lines Selection conditionand ORwrite down boolean expression.

6. Specify the field on the basis of which the total value will be created. In line Cross table choose Value.In line Group operationselect a summary function or Expression.In the latter case, in the cell Field write the expression.

7. Start the execution request.

EXAMPLE:

Select the total quantities of ordered products of each type produced by each manufacturer. Present the result in the form of a table, where the rows are the names of the types of products, the columns are the names of the manufacturers.

Figure: 21. Cross Query Constructor

Figure: 22. Result of a cross-reference query

8. Request to create a table

To create a table from the results of the query used to select records in linked tables, do the following:

Make a copy of the query to select records from tables (or create a new one).

Open the created query in the mode Constructor,by highlighting its name in the list of queries in the database window and pressing the button Constructor.

Select the command from the menu Query / Create TableA dialog box will open. Creating a table... Enter the name of the new table, leave the switch in the position In the current database.

Click the button OK... The select query is converted into a query to create a table.

Run the query by double-clicking on the query name in the database window. Before running the query, a message appears warning that changes will be made to the new table (even though it has not yet been created).

Click the button Yes... A second warning message appears about the number of records that will be placed in the new table as a result of the query. Click the button Yes.

Expand the Tables tab in the database window; a new table should appear in the list.

9. Request to update records

Create a new query and include the created copy of the table.

Select the command from the menu Request / Update... In the line request form Sorting and Output on displaywill be replaced with the string Update.

In line Updatespecify an expression that represents the new value for the current field.

If the information is updated not in all the records of the table, but only in its part, enter the condition for selecting records.

Complete your request. Check that the records are updated correctly.

EXAMPLE:

Update the cost of products in excess of 500 units to 90% of the original cost.

Figure: 23. Update request constructor

10. Request to delete records

Make a copy of the table in which you intend to update the records.

Create a new query and include the appropriate table in it.

Drag the fields into the request form, by the values \u200b\u200bof which the fields will be selected. In line Selection conditions specify the selection criteria.

Run a fetch query to display the records to be deleted.

Open up database window... Select a tab Tables.

Activate the generated select query and switch to the mode Query constructor.

Select the command from the menu Request / Delete... In the line request form Sorting and Output on displaywill be replaced with the string Deleting.

Click on the button Runningtoolbars. A message box will appear asking for confirmation to delete the entries.

Deleting records in the table “ one", for which the table “ many" there are related records violates the data integrity rule. Therefore, a data integrity option set for a relationship between tables may prevent the deletion of records. Cascade deletion should be applied.

EXAMPLE:

Figure: 24. Constructor of the delete request

11. Request to add records

The add record query is often used to copy data from one table to another.

Select a tab Inquiriesdatabase window, click the button Create. A dialog box will open New request... Select an option Constructor.

In the window Adding a table select the table from which the data will be taken.

Wrap to string Field form for requesting fields whose values \u200b\u200bwill be included in another table.

Select menu commands Request / Add... A window will appear Adding, where it is necessary to specify the name of the table to which the data is supposed to be added, and which database it belongs to. If the database is not the current one, but some other, then you will have to specify the full name of the file containing the database.

In the request form after the line Sortingthe line will be included Adding,in which you need to specify those table fields where data is added. If the field names are the same Afromcess will automatically insert into the line Addingfield names.

Start an execution request. See how the table has changed with new rows.

If the request for Adding there are fields like Counter, then you can work with such fields in two ways:

do not include fields like Counter in the fields to be added; then in the table where the data is added, the numbering of the data sequence will continue, i.e. if there is a table of employees with numbers from 1 to 100 and add to it new entries, then their numbering will start from 101 numbers;

include fields like Counter to the query list as added fields, the values \u200b\u200bthat they had in the original table will be saved in the fields of the added records; if such values \u200b\u200balready exist in the fields of the table to which the records are being added, this may lead to an error. The error message is displayed on the screen until the request is executed. If there is an error, select the button Cancel and fix the error.