In what cases are requests with a parameter used? Using parameters to enter data when running a query

Queries are a special database management tool that can be used to collect the necessary information based on certain criteria. A query with a parameter in Access is performed in cases where selection must be made repeatedly, changing only some conditions.

Unlike a custom query, a parametric query allows you to create and save the main form, changing only the conditions in it immediately before launching.

Step by step instructions

id="a1">

If you need to solve a problem in which the query criteria are not known in advance or can change, you should use a query with a parameter in Access.

To set a parameter input instead of a specific value, enter a name or phrase enclosed in square brackets in the “Selection Condition” section. Only after this, Access will consider the information and display it as a comment to the parameters. To use several mutable criteria, you should come up with unique names for them.

1. As an example, create a query that displays a list of teachers working in a specific department. It is this criterion that will be changeable, therefore in the line “Selection conditions” you must enter the value =[Enter the name of the department].
2. Save the resulting filter under the name “Selection of teachers by departments.”
3. Now, after launching, you will see a dialog box in which you will need to enter the required name, after which a list of teachers who are enrolled in this department will appear.

A query with a parameter in Access can be used in any type of selection: summary, cross-section, or in an action query.

As a rule, queries with a parameter are created in cases where this query is supposed to be executed repeatedly, changing only the selection conditions. Unlike a selection query, where a separate query is created for each selection condition and all these queries are stored in the database, a parametric query allows you to create and store one single query and enter a selection condition (parameter value) when running this query, each time receiving a new result . The parameter can be any text, the meaning of which determines the value of the data that will be displayed in the request. The parameter value is specified in a special dialog box. In the case when the value of the output data should be greater or less than the specified value of the parameter, the corresponding sign is placed in the “Selection condition” field of the request form before the parameter enclosed in square brackets. You can also create a query with multiple parameters that are related to each other using the logical operators AND and OR. When the execution request is launched, MS Access will display a dialog box for each of the parameters on the screen. In addition to defining the parameter in the request form, you must specify using the Request command – Parameters corresponding to its data type:

1. Open a query window in Design mode and add a table to it. Create a request by dragging and dropping the required fields into the request form and specifying the selection condition.

2. As a condition, enter a parameter enclosed in square brackets (for example, [Enter a title] or >[Taller than what?]).

3. Select the Query command > Options.

4. In the Query Parameters window that appears, enter the parameter without square brackets (for accuracy, use the shortcut keys for copying and pasting from the clipboard) and specify the corresponding data type. Click OK.

5. Click the Launch toolbar button.

6. In the window that appears, specify the parameter value.

7. The query result will contain only those records that satisfy the specified parameter value.

Calculated fields in queries

The query can be used to perform calculations and summarize results from source tables. Mathematical and string operators are used to create calculated fields. When you do this, Access checks the syntax of the expression and automatically inserts the following characters:

Square brackets ([...]) enclose the names of controls;

Number signs (ft), they contain recognized dates;

Quotation marks ("") enclose text that does not contain spaces or punctuation.

A field whose contents are the result of a calculation based on the contents of other fields is called calculated field. A calculated field exists only in the result table. The general format of a calculated field is: Calculated Field Name: An expression to create a calculated field.

For example: Profit: [Revenue]-[Expense].

Creating a calculated field is accomplished by simply entering a calculation expression in the “Field” cell of an empty column of the request form. After the query is executed, a calculated field based on this expression displays the result of the calculation rather than the expression itself.

1. In the “Field” line of the empty column of the request form, enter an expression starting with the “=” sign and consisting of field names written in square brackets and some arithmetic or other operation.

2. After executing the query, a new field with the name “Expression!” will appear in the resulting table, used as the name of the expression calculation.

3. In query design mode, change the name to "Expression!" to something more meaningful.

To enter complex calculations, use the Expression Builder window, which is called up by clicking the Build button on the toolbar, or by using the corresponding context menu command. The expression builder makes it easier to create expressions by allowing you to select its constituent elements (arithmetic operations, built-in functions, field names of tables and queries in the database, etc.) using buttons and lists.

Final queries

Queries allow you to make final calculations. For these purposes, Access 97 provides SQL statistical functions(Fig. IV.6). The statistical function is specified in the Group operation line of the query form, which appears when executing the View command > Group operations.

SQL function	Action
Sum	Summing the values ​​of a specific field
Avg	Calculate the average of a specific field's data
Min	Calculating the minimum value of a field
Max	Calculating the maximum value of a field
Count	Calculating the number of records selected by a query based on a condition
First	Determines the first value in the specified field of the records selected by the query
Last	Determines the last value in the specified field of the records selected by the query
StDev	The standard deviation of the values ​​of this field is calculated for all records selected by the query
Var	The variation of the values ​​of this field is calculated for all records selected by the query

Rice. 6 SQL functions

To run the final query:

1. While in Query Design mode, select the View command ä Group operation or click the Group operation button on the toolbar. As a result, the line “Group operation” will appear in the request form.

2. For the corresponding field, select the desired function from the Grouping list.

Cross request

A cross-query is used when you need to combine data in a row-column format. When designing such queries, you can specify the values ​​of some fields or expressions as column headings:

1. In Design mode, create a query by adding the table that should be its basis.

2. Select the Query – Cross command. The Display prompt line on the request form will change to a new Crosstab string and will be preceded by a Group Operation string.

3. In the “Field” line, specify the field whose values ​​should appear as strings in the new table; a field whose values ​​should appear as columns in the new table, and a field whose contents should be displayed as a value in the crosstab. There can be several fields that will be used as headings.

4. Click in the “Crosstab” row and select the options corresponding to the values ​​of these fields from the drop-down list.

5. For the field whose contents are displayed as values, in the “Group operation” line, enter the required function, for example, auto-sum (Sum), determine the average value (Avg) or quantity (Count). Based on cross-query data, you can build diagrams presented in the form of a form (see section IV. 14.1. Creating a form).

You can design a query that prompts you to enter multiple pieces of data, such as two dates. Microsoft Access can then return all records that fall within the time interval between these dates.

Example

1. Create a query in Design view based on a table "Töötaja"(fields: Nimi, Perekonnanimi) and tables "Amet"(field: Nimetus).
2. To define a query parameter, enter in the line Criteria for the column " Amet» (job title) instead of a specific meaning, a word or phrase and enclose it in square brackets, e.g. Enter your position. This phrase will be presented as a prompt in the dialog box when you run the request.

3. If you want Access to validate data entered as a query parameter, you must specify a data type for the parameter. Usually this is not necessary when working with text fields, because by default the parameter is assigned a data type Text. If the data in the request field is dates or numbers, it is recommended to specify the data type for the parameter. To do this, right-click on the free field at the top of the request and select the button
(Options) A dialog box appears Query Parameters(Request parameters).

4. To column Parameter you need to enter the name of the parameter exactly as it is defined in the request form (the easiest way to do this is by copying), but you don’t have to enter square brackets. In column Data Type Select the required data type from the drop-down list. Click the button OK.

5. Click the button (Launch) to complete the request. When you run the query, a dialog box appears Enter Parameter Value,

in which you need to enter a value, for example õpetaja. As a result of executing the request, we will see only those names and surnames whose position is teacher.

You can enter multiple parameters in one request. When executing such a request, dialog boxes will be displayed for each of the parameters in turn. Enter parameter value in the order in which the parameters are listed on the request form.

Creating Parametric Queries

Parametric query Each time it is executed, it requires entering certain parameters (selection conditions).

To install parameter instead of specific data in the QBE form, in the selection condition line, you need to enter a name or phrase enclosed in square brackets, i.e. parameter.

Advantages of a parametric query:

There is no need to constantly modify the query in Design mode;

Use in forms and reports, because Each time they are opened, MsA prompts users for the required parameter.

For example :

Between [Enter start date:] And [Enter end date:].

To search for words starting with a specified character:

LIKE [Enter first character to search:] & "*" Comment: if you need to change the data type of a parameter, you need to run the command Query ® Parameters and in the Query Parameters dialog box, enter the parameter names in the Parameters column as they were entered in the QBE form, as well as the Data Type from the list. By default, the Parameter Type is Text.

29.Creating a result. requests. Group settings. operations. Group assignment. Examples.

Queries that perform calculations on groups of records are called final requests .

To create the final query, you need to add the line Group operation to the QBE sample request form in Design mode

Add line Group operation can be traced way:*Menu item View, Group. operations;*Button Σ (group operations) on the toolbar.

Summary queries for all records

Summary queries for one group of records

When you use the grouping option, records are grouped based on the same values ​​in the field where this option is specified, and Access then performs calculations separately for each group.

Summary queries for multiple groups of records

You can perform calculations on grouped data from several fields and from several tables.

Grouping using criteria

In addition to allowing you to group records in a summary query, you can also set limits on the number of records that will be processed or displayed.

These restrictions can be applied to three types of fields:

Ø field processed with the Grouping setting;

Ø summable field; non-summarizable field.

31.Group functions. Features of their use in expressions for calculated fields.

Ø Group functions

Sum - Calculates the sum of all field values.

Avg - Calculates the average value of the field.

Min - Finds the smallest value of the field.

Max - Finds the largest value of the field.

Count - Returns the number of records in which the values ​​of this field are not empty.

StDev - Calculates the standard deviation for all values ​​of a given field.

Var- Calculates statistical variance. If there are less than 2 rows in a group, the function returns 0

Ø Note 1: Function Count returns the count of all records with non-blank values ​​in a field (that is, fields that do not contain a value Null).

Ø Note 2: Functions Min, Max, Count applicable to all field types; Sum, Avg, StDev, Var– only to numerical and monetary ones, and First, Last to text.

32. Calculating grand totals in queries. Examples.

Summarizing queries for all records. These types of queries are created using group functions without using the grouping option.

Example: Calculate the number of orders ordered, the average discount value, the maximum and minimum value of the cost of services.

34.Cross request. Purpose. Rules and methods of creation

Cross requests

With a cross request type, the line Group operation is always active and the option is set in it Grouping, which is used to specify row and column headings. So the fields that will be used to plot the rows and columns must contain the option Grouping.

A cross-query, unlike other queries, uses values ​​from tables, which is used when creating a request.

Creating a cross-request.

There are 2 ways to create a cross-request:

Ø Query ® Create ® constructor;

Ø select tables for which a cross-query will be created;

Ø select the required fields;

Ø menu item View ® cross or field on the toolbar – Query View ® cross;

Ø Fill in the Crosstab line

Create a Cross Query Using the Wizard

ü query ® create ® cross query

ü select a table or query from which we will display fields in the query;

ü select the field for which you want to perform final calculations and a group function; at the same step we confirm the creation of total values ​​for each line

ü set the request name; ready.

Comment:

If, when creating a cross-query using the wizard, you need to use fields from several tables, you should first create a query that will contain all the necessary fields.

Thus, a new row appeared in the query: Crosstab, in which you need to select at least 3 fields:

Ø row headers (Grouping);

Ø column headers (Grouping);

Ø values ​​(group function).

The result will be a table:

row headers: order acceptance date values,

column headings: city names

values ​​- the number of orders ordered by the client from each city for each day.

Selection conditions in a cross-query

Conditions can be set for the following field types:

Ø for any new field;

Ø for the row headers field;

Ø for the column headers field.

A query that makes changes can be thought of as a regular select query that must also perform some operations on a specified group of records in the resulting table.

When creating a request, MsA by default creates sample request. To change the request type, you must use the request designer menu.

In the database window, query-actions each have their own icon and! (start button).

Because request actions, input. changes, irreversible, then you should hold. track. sequence of actions:

create a query selection;2. switch to results viewing mode. tables and view the records selected in the query; 3.convert a select request into an action request; 4.execute the request: button!;5check the changes made. Creating a query to change (update) values

To create such a request, you must go through 2 steps:

1.create a selection request and view the data to be changed;

2.convert the select query into an update query and execute.

That. First, let's create a selection query:

Let's transform the fetch request into an update request:

let's choose a team Request ® update, the line Update will be added to the request.

in the Update line in the Discount field, enter 0.15.

on the toolbar, click the button!

To complete the request, you must answer Yes.

Query to create a table

Let's build a selection query;

Select Request from the menu ® creating a table;

In the Table Name field – the new table name

Go to the resulting table view mode, and then to the designer.

It is better to disable date output.

Button! ® Yes.

Create a query to delete records

This is the most dangerous request!!!

A delete query can delete records from multiple tables at the same time.

To do this you need to:

the relationship between the tables has been defined;

the Ensure data integrity option is enabled;

The Cascade deletion of related records option is enabled.

1. It is necessary to build a selection query;
2. Request/Delete;

In the Orders table, select the Order acceptance date field; For this field, set the criterion >=#1/01/08# and<#1.01.09#;Нужно проверить, что результирующая таблица содержит записи, относящиеся только к 2008 году;Режим конструктора;Кнопка!;Кнопка ДА. Записи будут удалены из таблиц.

38.Form as a database object. Name Viewing modes. Methods for designing forms in ACCESS

Forms are a type of database object that is typically used to display data in a database. The form can also be used as a button form that opens other forms or database reports, or as a custom dialog box for entering data and performing actions based on the data entered. The form uses Design mode and Form mode. You can create a form using the wizard and the designer. Wizard: select fields to display in the form, select the appearance of the form (one column, ribbon, table, aligned, pivot table, pivot chart), select the desired style. form mode (Form mode. A window in which a form is displayed to display or enter data. Form mode is the primary mode in which you enter or edit tabular data. In this mode, you can also change the form's layout.)

40-41.Form Designer Mode.View of the Form Designer window.Form areas.Stage of form creation in Form Designer Mode. Form designer mode. When you create a form, only the data area is initially displayed. To add a form title and notes you need to: View – Form title/note. The form can also contain a header/footer. Data header. data areas ( Data section. The main section of a form or report. This section typically contains controls attached to fields in the record source. However, it can also contain free controls, such as labels that define the contents of fields.) By selecting the Field control in the Toolbox, you can perform calculations in the selected area or add data as follows: Right mouse button - Properties - Data. Next, we substitute the data or enter an expression.

43.Control elements, their types and purpose.

The Toolbox is used to place objects on a form. Let's consider panel buttons:

1.Selection of objects( you can select any control element, divide it);2. Masters( There are wizards for creating combo boxes, option groups, buttons, charts, and subordinates. forms);

3.Inscription: allows you to place text in the form in addition to the default one; 4. Switch group: used to place a group of checkboxes, radio buttons, or radio buttons that represent a set of alternative values

Switch, Switch, Checkbox used as: 1. a separate control element associated with a logical field; 2. a free control element that accepts user actions in a special dialog window; 3. option group component into which the values ​​for selection are displayed.

Combo box: a composite control that combines a field and a drop-down list.

List: creates a scrollable list. In Form view, the selected value can be entered into a new record or used to replace an existing record value.

Button: allows you to perform various actions in the form: search for a record, format a report, install/remove a filter, etc.

Drawing: places a picture that is not an OLE object.

Free object frame: allows you to enter a free OLE object that remains unchanged as you navigate through records.

Attached object frame: Allows you to display OLE objects in a form. Designed for objects stored in the form's underlying record source, so that different objects appear as you navigate through the records in the form.

In addition, there are also: Page Break, Set of Tabs, Subform/Report, Line, Rectangle, Other Elements.

Using the Query Window

1. Create a simple query

Let's open the STUD database, as discussed in LR1. Let's create a query that displays information about students who have debts.

To create a query, activate the database window (see LR1). After that, click on the “Request” spine and the “Create” button. In the New Query dialog box that appears, select the Design button. Access opens the Add Table dialog box, allowing you to select the base tables and queries for the query you are creating. Select the "Results" and "Students" tables by clicking the "Add" button, then close this window with the "Close" button.

The query designer window that opens consists of two parts: the upper one contains lists of fields of the selected tables, and the lower one contains the QBE form for creating a query. Each column of the form describes one field involved in the request.

Including a field in a query is done by dragging it from the list of table fields (located at the top of the screen) to the desired column of the QBE form using the mouse. All table fields are enabled by dragging the “*” symbol located at the top of the list of fields for a given table at the top of the screen.
Include the fields from the "Results" table in your query:

Item
Number_С

by dragging them into the QBE form. Using the method discussed above, drag the Last Name field from the “Students” table.

The “Debt” request will look as shown in the figure.
Relationships between tables are established automatically using the relationship structure created when generating the database project. You can specify other table connection options and other types of relationships (outer join, one-to-one, one-to-many, etc.). Changing connections is made in the upper part of the window by selecting a related field in the description of one table and moving it, with the mouse button pressed, to the description of the corresponding field of the related table. The communication type can be changed by activating it by clicking on the communication line.

The condition for selecting the fields we need is made by including this condition for this field in the QBE line "Selection condition". Multiple selection values ​​are entered on one row, separated by logical AND or OR conditions, or entered in subsequent cells of the "or" row.
For the “Results” table, we will set a condition for selecting students who have debts, for which we will enter the line “Debt” from the “Results” table in the field description column, and in the “Selection condition” line we will enter:
Yes

Let's reset the "Show" checkbox for this field, which prohibits the display of this field in the selection (since it is not necessary to display it).
In addition to the standard comparison operators "=", "<", ">", "<=", ">=", Access also supports BEETWEN, IN, LIKE.
The request may contain so-called calculated fields - calculated values ​​over any table fields. Access supports a large number of functions and operators that are used to generate calculated fields.
To view the result of the request, click the button on the panel:

You can also change the title by activating the field description column; and then by executing the “Properties” command of the “View” menu, enter its name in the “Field label” line.

It is advisable to sort the rows of the final table by the “Last Name” field of the “Students” table. To do this, in the column with the description of this field in the “Sorting” line, select the “ascending” item. If you need to sort by multiple fields, Access sorts the data in the order it appears on the QBE form. After viewing the request, you can see that it is necessary to enter the Name field from the “Students” table, because the last name is repeated and the request does not contain the necessary information. To insert a field, drag it into the QBE grid to the place where you want it to be and all other fields move one position to the right.
You can also run the received execution request using the "Execute" command in the "Request" menu. Let's check the results for compliance with the selection criteria. Let's save the received request under the name "Debts". The query is a table:

Let's create a query in which an additional field will be calculated. The Department of Computer Science decided to pay its employees a bonus of 10% of their salary for the new year. Let's create a query based on the “Teachers” table, with the fields Last Name, Department, Position, Salary. To create a calculated field “Bonus” that displays information about the amount of the bonus to the employee, in a new column (next to the filled one), in the “Field” line, enter the expression Salary * 0.1. Let's change the field title by entering its name before the expression. The cell will contain: “Bonus: [Salary]*0.1.”

You can also change the title by activating the column with the field description, and then executing the “Properties” command of the “View” menu, entering its name in the field label line. Enter the “selection condition” for the “Computer Engineering” department in the line.

1.2. Creating a summary query

Summary queries allow you to obtain statistical information on groups of data, such as: sum, arithmetic mean, mathematical expectation, dispersion of table field values; number of field values ​​that satisfy certain conditions, etc.
Let's create a query for the student's GPA. Let’s open a new request form, add the table “Students”, “Results” into it and set the output of the fields “Number_S”, “Last name”, “Grade_T”, “Grade_P”, “Grade_L”.

To calculate the total values ​​in the request, click on the “Group Operations” button on the toolbar. A "Group Operation" row will appear on the QBE form, with each described field on that row set to "Group" by default. Now records for each field will be grouped, but the total will not be summed up. To display the total values, you must replace the word “Grouping” with some kind of group operation (see Appendix 1).
For the fields “Rating_T”, “Rating_P”, “Rating_L”, use the “Avg” operation, which displays the average value of the field for a group of records (we get the average score).

Let's also change the field headings of the output table. To do this, in the description of the fields in the “Field” line, enter their new names, separating them from the description with a colon:
Rating_T: Theory
Score_P: Practice
Rating_L: Labs

The part of the expression before the colon is displayed after running the query as the column header, instead of the default output by Access. In this way, you can generate more understandable field headers than those generated by the database.
Let's save the received request under the name "Result". Run the query and analyze the results.

There are tasks in which query parameters are unknown in advance, or there is a need to change them. To do this, Access provides the ability to enter query parameters immediately before executing them. To set the parameter input instead of a specific value, enter a name or phrase enclosed in square brackets in the “Selection condition” line. Access treats anything entered in square brackets as a parameter name, and appears in the query dialog box as a comment on the entered parameter. You can use multiple parameters in a query, but with unique names.

Let's create a query that displays a list of teachers working in a department specified by the user. Let's take the "Teachers" table as a basis. Let's select the item "Teachers.*" in the output form, displaying all the fields of the table "Teachers". Let's enter the description of the "Department" field again, reset the display flag for it, and in the "Selection condition" line of this field we'll enter the expression:
=[Enter department name]

Let's save the received request as "Selection of teachers by departments." After running the query, we will see a dialog box asking for the name of the department.

After entering the parameter, Access displays a list of teachers whose place of work is the requested department.
Parameter input can be used for any type of query: summary, cross, query-action, etc.

1.4. Creating a Cross Query

Access supports a special type of summary query called cross-query. A cross-query outputs calculated values ​​as a function of existing fields. So in Access you can display a query describing the total earnings by position.
A necessary requirement for creating a cross-query is the presence of a row header field, a column header field, and one calculated value field. Fields that are row and column headings must have the “Group” setting in the “Group operation” line. For the value field, you must select in the “Group operation” any group function, or an expression containing a group operation.

For our query for the “Teachers” table, in the “Query” menu, set the “Cross” pointer. Access will add a "Crosstab" row to the QBE block. Then move the “Position” field to the form and set “column headings”.

The next field is “Department”, set “row headers”
The resulting field is “Salary”, set the “value” for it
"group operation" - Avg,
Let's save the received request under "Salaries by position".

1.5. Creating an Outer Join Query

Another type of ad hoc query is an outer join, which is used in multi-table queries. When using queries of this type, the final set of records is formed not only if related fields of the base tables match, but also from records of one table that do not have a match in another.

Let's create a query that displays information about all students, with and without debt. This query will use an outer join on the "Students" and "Results" tables by matching the "Number_C" field with all records in the "Students" table.
To create an outer join, you must change the join parameters. Let's open the "Debt" query in design mode. Click on the link between the Students and Results tables to open the Join Options dialog box.

By default, this window has the join type set to “displaying only those records for which the associated fields match.” Other join types allow you to display “all records of the main table and only those records of the child table for which the values ​​of the related fields match” and “all records of the child table and only those records of the main table for which the related fields match.”

Select the third type of connection and click on the “Ok” button. After this, an arrow will appear on the table joining line indicating an outer join. Let’s save the modified query (using “Save as..” so as not to overwrite the previous query) under the name “External union by students”.