Query with parameter (parametric query)
As a rule, queries with a parameter (Figure 10.34) are created in those cases when it is supposed to execute this request many times, changing only the selection conditions.
To define a parameter, enter the Selection condition instead of a specific value, the name or phrase enclosed in square brackets (). What is enclosed within square brackets, Access is treated as a parameter name. It is displayed in the dialog box when the query is executed, so it is reasonable to use a meaningful phrase as the parameter name. You can specify in the query
Fig. 10.34. Example of creating a query with a parameter
several parameters; while the name of each of them must be unique and informative.
For example, you need to create a parametric query to display processors costing up to a certain amount, their name, price and the phone of the warehouse where they are stored (the parameter is the price of the product).
A cross request is a special type of the resulting query. It allows you to display the calculated values in a crossover table that resembles an electronic table.
To create a cross-query, follow these steps:
In Design view, generate a selection query.
Change the request type to Cross (the Work with requests tab, the Request type group) (Figure 10.35).
Fig. 10.35. Button Cross-Request
At the bottom of the query form, you will see the lines Group operation and Crosstab (Figure 10.36).
In the Cross-table For each query field, select one of four settings: Headings of the lines, Column headers.
For a cross-query, define the field (you can have several) as the row headers. Define one (and only one) field that will be used as the column headers.
Define one (and only one) value field. This field must be final (i.e., in the string "Group operation", one of the summary functions must be specified, at -
Fig. 10.36. Cross-Request Designer
Sum (Sum), Average (Avg), or Count).
As an example, consider creating a cross-request that displays the number of items stored in warehouses. In the headings of the rows we indicate the names of the goods, in the column headings - the numbers of the warehouses (Figure 10.37).
Fig. 10.37. Example of creating a cross request
Calculated fields in queries
You can use the query to perform calculations and summarize the results from the source tables.
You can use arithmetic operators and any built-in Access functions to create calculated fields.
The computed field can also contain the result of concatenating (combining) values of text fields. To do this, enclose the text in quotes, use the & amp; as the concatenation operator. For example, you can create a field that will contain the result of combining the [Last Name] field and the [Name] field.
The field whose contents are the result of calculating the contents of other fields is called the calculated field. The calculated field exists only in the resulting table.
Creating a calculated field is done by simply entering an expression for calculation into the Field the empty column of the request form (Figure 10.38).
Fig. 10.38. Creating a calculated field
After the query is executed, a new file will appear in the resulting table named "Expression1", used as the name of the computed expression.
In Query Design mode, change the name of the Expression! on more substantial. In the Expression! example, changed to Price .
To enter complex calculations, use the Expressions Wizard window (Figure 10.39), which is called by the command on the tab Working with queries (in Design view) in the Configure query group or the appropriate shortcut menu command.
Fig. 10.39. Expression Builder
At the top of the window is an empty input area for creating an expression; in the bottom are three lists designed to find the required fields and functions.
The builder will help you build the expression correctly. Click the OK button and the entered expression will be moved to the request form.