Selection in queries on a specific Access column. How to set the settings for posting records in the query? Logic operations "and", "or"

To limit the list of records received as a result of the request, only satisfying specific conditions - in the query form, fields are provided for the selection conditions. Briefly about it was told in "Step 22 - creating a request for a sample", now it's time to figure out in more detail.

The most important thing is that you should remember, this is that for each query field, you can create your selection condition. If this numeric fieldYou can specify the range of the values. For example, in the field Price You can set a condition & GT20, which will allow you to choose all the books whose prices exceed the number 20.

For text field A string is set, the contents of which will be compared with the values \u200b\u200bof the corresponding table field. The matches of the values \u200b\u200bwill lead to the addition of the current entry into the final table. When drawing a string sign * means any sequence of characters, and ? One any symbol. For example, condition "Novikon" in field Publishing house, I will give a list of books printed only in this publisher. Condition "New *" corresponds to the values \u200b\u200bbeginning with Nov., "*but*" Let's give all publishers with the letter but in the name, "?????" will deepen all combinations of five characters, and "??*" Corresponds to the values \u200b\u200bconsisting at least two characters.

This can be done if the selection condition for the request is in advance known and the need to change it. In practice, in many cases, the user needs to provide an independent choice of what he wants to find in the database tables. To do this, the selection condition parameter must be requested at each request session. Suppose that the buyer wants to learn about the presence in the store of books Aizek Azimov. All other books are not interested in it, but to spend his time to view the entire base in search necessary information He, of course, does not intend. Then, in the request, it is simply necessary to envisage the opportunity to get this information from the buyer and give him only records that Author's name Aizek, A. Surname of the author Azimov.

For this purpose, a special language team is served SQLwhich looks like this:

Like [ Text message user ]

In square brackets, the text displayed in parameter input windowReferring to the screen immediately after the start of the relevant request. The input field takes the value typed on the keyboard and transmits it as the selection condition. Further DBMS Looks at all database entries in search of matches of values \u200b\u200band outputs the search results in the final table.

Default Access Determines the type of data entered as text. If the parameter specifies the selection condition from the type of data type Numerical or Date Time You must manually assign a data type. This is done as follows:

You can specify several selection conditions for the query field. The second condition is placed in the string OrThird to the string is lower and so on until all the necessary values \u200b\u200bare specified. Failure to fulfill at least one of the selection conditions, prohibits adding an entry to the resultant table. Therefore, extreme attention is necessary during the set of values. Targets or inaccuracies can radically affect the operation of the created request.

Tip: Before converting any type of request to a request with parameters, fully complete the design and query testing process. Use in the test query (exactly present in the table records) The selection conditions ensures the coherence of the testing process. In addition, no parameter is applied in the query, you can easily switch between the design and query operation modes. Having finished testing and checking the performance of the request, edit the selection conditions and add the necessary prompt texts for the options input parameter values.

In Access provided a large number of Options for selecting records from the database. We will analyze some widespread setting conditions used in the query.

Operators

The operator is a symbol or word, for example, \u003d or OR, indicating the operation of the operation on one or more items. Operators allow you to perform operations on the elements of the formula. Access allows you to use different types Operators for computing on the sheet:

arithmetic operators - serve to perform arithmetic operations over numbers (table);
comparison operators are used to compare two values. The result of comparison may be a logical value: either true, or false;
aMPERSAND (&) concatenation text statement is used to combine multiple text strings to one line;
reference operators are used to describe references to cell ranges.

Request records in a specific range of values \u200b\u200bcan be performed using the following comparison operators: \u003d - equal; \u003e - more than;< - меньше, чем;
<> - not equal;
\u003e \u003d - more or equal;
<= - меньше или равно.
Suppose you are interested in employees whose salary exceeds 300 rubles. In the Line of the Selection Terms (Criteria), enter:\u003e 300.
As a rule, comparison operators are used in numeric fields or dates fields, but they can also be used in text fields. For example, if you enter the selection condition in the text field< "П", то будут выбраны значения поля, начинающиеся после буквы «П».

Operator Like

If you are not confident in writing any word, you can use the LIKE operator and wildcard. Recall that the question mark replaces one character, and the asterisk * is a group of characters. For example, the expression: LIKE P? L performs the search for words beginning with the letter P. Expression: Like performs the search for words, sang, pal, floor, etc.
Under the expression, there is any combination of operators, constants, text constants, functions, field names (columns), controls or properties, the result of which is a specific value.

Performing standard calculations in field values

One of the widespread methods for analyzing tabular data is the use of the following total functions for numeric fields:

SUM - calculating the amount of field values;
AVG - definition of the average field value;
Min - finding the minimum field value;
Max - finding the maximum field value;
Count - counting the number of field entries (can be used for all fields);
STDAV - calculation of standard field deflection;
Var - calculation of the change in field values.

Group operations execution

The result column may contain final data for all tables of table or grouped by some principle. For example, we may be interested in the maximum or average price of goods (field price) of each type (type field) presented in the table. To use the outcome operators in the specified task, follow these steps:

in a query form in the line, display the screen (Show) Check boxes in the fields: the type and price that will be used for computing;
select the View Command (Totals) command in the View menu (Totals) or JM-click the Toolbar on the toolbar;
install the cursor in the field over the values \u200b\u200bof which will be calculated, and select the group operation in the cell (Total) required function;
in the Query menu, select Start (RUN).

Using the query, the sum (SUM) and the average arithmetic (AVG) can be calculated, the minimum (MIN) and maximum (max) values \u200b\u200bin the field are found. Having finished working with the query, you can save it under some name.

Fig. 24.11 Compiling a query using a group operation

Expanding the selection conditions

For example, you want to know the volume of sales expensive (more than 2500 rubles) and cheap (less than 500 rubles) orders. Enter the selection (Criteria):\u003e 2500, in the cell or (OR)<500. Условия, заданные в ячейке или, будут восприниматься как дополнительные.

Using the calculated field

The calculated field displays the data obtained in the query on the results of the calculation of the expression. The field value is recalculated each time the expression change.
For example, if each order costs 5 rubles and you want to calculate the cost of all orders made by each customer, then enter the expression field into the cell: Cost: [Number of orders] * 5.

Builder expressions

To display the expression builder window (Fig. 24.12), follow these steps:

go to the query designer mode;
in the Line of the selection condition (Criteria), right-click the column for which you want to set the selection criteria, and select Build (Build) in the context menu, or click the same button on the toolbar.

Fig. 24.12 Builder of expressions

At the top of the expression builder dialog box, the field displays the expression as it is created. Access often places in this field prototypes enclosed in double angular quotes, instead of which the user must substitute the necessary elements. You should either enter the appropriate value or highlight the prototype, and replace it with an item from the right list.
In the middle of the builder window there is a section designed to create an expression elements. It contains the buttons with frequently used operators. When you click on one of these buttons, the builder will insert the corresponding operator to the current position of the expression field. For example, you can click the Like button so as not to enter this word from the keyboard.
At the bottom of the builder window are three fields. In the left field, folders containing tables, requests, forms, database objects, built-in and user-defined functions, constants, operators and general expressions. In the middle, the field is set to certain elements or types of elements for the folder specified in the left field. The right field displays a list of values \u200b\u200b(if they exist) for elements, for example, if you select built-in functions in the left field, then the middle field will appear a list of all types of Microsoft Access functions, a list of all built-in functions specified by the left and middle will be displayed. Fields.
To display a complete list of operators, select the Operators folder in the lower left field and the desired type in the middle field. All operators of the selected type will be displayed in the right field. It is possible to directly enter a part of the expression in the upper field.
To return to the request form, click OK, the constructed expression will be inserted into the field where the cursor is located. Field names when entering the request form, enter into square brackets

Recording conditions can be set for one or more fields in the corresponding query form string.

The selection condition is an expression that consists of comparison operators and compaable operators. As comparison and logical operators can be used as follows: \u003d,<, >, < >, Between, In, Like, and, OR, NOT, which determine the operation over one or more operands.

If the selection condition does not contain an operator, then the default statement is used \u003d.

Literals, constants and identifiers (links) can be used as operands.

The literal is the value perceived literally, and not as the value of the variable or the result of the calculation (for example, the number, line, date).

Constants are not changing values \u200b\u200b(for example, True, Falls, yes, no, NULL).

[Table name]! [Field Name]

The selection conditions specified in one line are associated with the help of a logical operation and, and specified in different lines - using a logical operation or. These operations can be specified explicitly in the selection condition using And and OR operators, respectively.

You can form the selection condition using the expression builder. You can go to the Expression Builder window by clicking the [Builder) button on the toolbar or selecting the command to build in the context-dependent menu. At the same time, the mouse cursor must be installed in the input cell of the selection.

After entering the expression in the form and press the Access key performs a syntax analysis of the expression and displays it in accordance with the results of this analysis.

To execute the request, you must press the [Run] button or [Representation Presentation] on the query toolbar.

Sorting data. For convenience of viewing, you can sort records in the table in a specific sequence. Sort button on the toolbar (or commands of the recording menu \\ Sort, sorting ascending (sorting descending) allow you to sort the columns ascending or descending. Before clicking on the sort button, select the fields used for sorting. Modern DBMS (such as Access) Never assort the tables physically, as it was done before. Data sorting tools (as well as filtering, searching and replacing) are implemented in Access as automatically generated requests. Table entries are always located in the database file and the order in which they have been added. in a table.

Logical operators are applied to combine two logical values \u200b\u200band return the values \u200b\u200bof "Truth", "Lies" or NULL. Logical operators are also sometimes called Boolean.

The main goal when creating a database is an ordered organization of information in the table, avoiding redundancy of information and achieving logic when they are associated. Naturally, the user is not interested in all the data in the documents and customer more interested in specific information than its location. It is for such filtering information and there are requests. A request for a sample in Access is one of the most used, it allows you to extract necessary information, Cut out calculations, create cross-references, but without changing information in the main tables.

How to create a request to select ACCESS using a designer

For example, try creating a list of employees indicating their position, salary and allowances based on the experience of the work. Naturally, the data you are interested in will be in different tables, so the sample in Access will be carried out from related objects. To create such actions should be performed:

The figure is clearly seen that the data is not ordered, and they are not very convenient to view them in this form. When forming a query, this parameter should be borne in mind. To do this, in the form you should use a string called "Sorting", with which you can build a list for sustabouts starting from the maximum, ending with the minimum value.

In addition, you can create in Access request To the sample with the condition: for this, in the field "Conditions of Selection" should be asked required parameter (A certain amount of the salary, to view the allowances only in project engineers, etc.).

Literal - Specific values \u200b\u200bperceived by Access as they are recorded. As literals, numbers, text lines, dates can be used. Text lines consist in double quotes, dates - in signs (#). For example, 567, "Informatics", # January 1, 99 #.

Constants - non-changing values \u200b\u200bthat are defined in Access, for example, TRUE, FALSE, yes, no, null.

Identifier - reference to the field value, control or properties. Identifiers may be the names of the fields of tables, forms, reports, etc., which should be in square brackets. As a rule, Access manufactures automatic substitution of brackets.

In many cases, the reference to a specific value should indicate its exact location in the hierarchy of the database objects, starting from the top-level object. If you need to specify a link to the field in a specific table, form, report, then in front of the field name is the name of the table, form, report, also enclosed in square brackets and separated on behalf of the field exclamation familiar. For example, the link to the field in the table will take the form: [Table name]! [Field Name], and link to the property DefaultValue control date of birth in the form Student: Forms! [STUDENT]! [Date of birth] .DefaultValue

Operators of comparison I. logical operators The use of which is allowed to express the selection conditions are:

These operators determine the operation over one or more operands.

If the expression in the selection condition does not contain an operator, then the default operator is used \u003d.

Text valuesIf they contain gaps or punctuation marks are entered in double quotes. Otherwise, quotes can not be entered, they will be added automatically.

It is allowed to use template operators - asterisk (*) and question mark (?).

The Between operator allows you to set the interval for numeric and date. For example:

Between 10 and 100

specifies the interval from 10 to 100; You can set the dates interval:

Between # 01.01.1997 * And # 31.12.1997 *

The IN operator allows you to check for equality to any value from the list that is set in parentheses. For example:

In ("mathematics"; "Informatics"; "stories")

Operator Like. Allows you to use samples using template characters when searching in text fields. For example: Like "Ivanov *"

You can form the selection condition using the expression builder. Go to the window Builder expressions You can by pressing the button Build On the query constructor toolbar or by selecting the command Build In the context-dependent menu. The mouse cursor must be installed before in the input line of the selection condition.

After entering the expression in the form and press the Access key performs a syntax analysis of the expression and displays it in accordance with the results of this analysis.

Logical operations

Logical operations "And", "or"

The selection conditions specified in the same line bind by default using the logical operation and specified in different lines - using a logical operation or. These operations can also be specified explicitly in the expression of the selection condition using Operators and OR, respectively.

Calculated fields

In the query above the fields can be calculated. The result of the calculation forms the calculated field in the query table. Each time the query executes, calculates using current field values.

In addition to the names of the fields, in the expressions of the calculated fields can be used literals and embedded functions. As a result of the processing of the expression, only one value can be obtained.

The expression is entered into the request form in an empty line field Field. After pressing the key or move the cursor to another string field, the field name is added before expression in this line field. Expressing N.. N is an integer that increases by one for each new created field calculated in the query. The name of the calculated field, standing in front of the expression, is separated from it with a colon. For example:

Expression!: [Price] * [Number] Where the price and the number - Field names.

The name of the calculated field - expression1 It becomes the column heading in the table with the results of the execution of the query. This name can be changed.

For the calculated fields, as for any other, sorting, setting the selection conditions and the calculation of the final values.

Built-in functions

ACCESS and VBA defined approximately 150 built-in featureswhich can be used in the calculated fields and selection conditions. List some grouped functions.

Date and Time Functions. Used when processing dates and time in the fields and literals. Return the date and time in whole or in part (year, month, day), for example, the Date function forms current dateThe MONTH features a month from the field value containing the date.
Text processing functions. Used when working with symbol strings.
Data type conversion functions. Provide the ability to the user to set the data type for numerical values, which avoids the selection of the most suitable type data system.
Mathematical and trigonometric functions. Perform operations numerical valuesthat cannot be performed using standard arithmetic operators.
Financial functions. Like excel features, serve to calculate the percentage of return on investment, depreciation deductions, annual rent, etc.
Statistical functions. Used when working on a subset fields for records to calculate the average value, the sum, the minimum, maximum value.

To write an expression can be used a builder of expressions that is called by a button Build Toolbars.

Assigning user names calculated fields

The user has the ability to assign a new name to the calculated field using one of the following ways:

Changing the field name in the query. In the query designer mode in the query form instead Expression Enter a new name.
Changing the signature of the field in the field properties. Install the cursor on the calculated field in the query form and open the window. Properties of the fieldBy right-clicking and selecting the item in the context menu Properties. In the window Properties of the field On the tab General Enter the desired signature text in the string Signature field.