Queries are powerful tools for manipulating the data stored in Access tables. You can use queries to view, analyze, and modify data from multiple tables. They are also used as a data source for forms and reports. Queries allow you to calculate totals and display them in a compact spreadsheet-like format, as well as perform calculations on groups of records.
Queries can be created independently and with help (about the wizards. Query wizards automatically perform basic actions depending on the user's answers to the questions posed. You can develop queries yourself in design mode.
You can create the following types of queries in Access:
V cross query displays the results of statistical calculations (such as amounts, number of records, averages) performed on data from one field of the table. These results are grouped into two datasets, one in the left column of the table and the other in the top row. For example, we need to find out the average work experience of assistants, associate professors and professors in different departments (based on the Teachers table). A cross-reference query will make it easy to solve this problem by creating a table in which the titles of the rows will be positions, the headers of the columns will be the names of departments, and the cells will calculate the average values of the length of service of teachers.
Change request is a query that makes changes to multiple records in one operation. There are four types of change requests: delete, update, and add records, and create a table.
Delete request removes a group of records that match the specified conditions from one or more tables. With a delete query, you can only delete the entire record, not individual fields within it.
Update request Records makes general changes to a group of records in one or more tables. For example, the salaries of assistants have increased by 10 percent. An update request allows you to quickly make these changes to the Teachers table.
Add request adds a group of records from one or more tables to the end of one or more tables. For example, several new teachers have appeared, as well as a database containing information about them. Instead of entering all the data manually, you can add it to the Teachers table.
Request to create a table creates a new table based on all or part of the data from one or more tables. For example, based on the Teachers table, you can create a new table that contains data only about professors.
SQL Query is a query created using SQL statements. This type of query is rather difficult for novice users and is usually used by advanced users with programming skills and communication with database servers. Due to the complexity and specifics, we will not consider the SQL query in this chapter.
Sample request used most often. When it is executed, data satisfying the selection conditions are selected from one or of several tables and displayed in a specific order. For example, you can display data on the names of associate professors with more than 15 years of experience (based on the Teachers table). You can also use a select query to group records to calculate sums, averages, recalculations, and more. For example, using a select query, you can get data on the average seniority of associate professors and professors (based on the Teachers table). Simple select queries are virtually indistinguishable from filters. Moreover, filters can be saved as queries.
Request with parameters is a query that prompts the user in a dialog box to enter data on the basis of which the query will be executed. For example, data is often required on what disciplines are taught by teachers. To avoid creating separate requests for each teacher, you can create one request with parameters, where the teacher's last name will be used as a parameter. Each time you call this request, you will be asked to enter the teacher's last name, and then all the fields that you specified in the request will be displayed on the screen, for example, the teacher's last name, first name, patronymic and the discipline he reads.
To create a new query, select the Queries tab in the database window and click on the button<Создать>... The "New Request" window will open.
In the window, you must select one of five items:
Constructor,
Simple request.
Cross query,
Duplicate entries.
Records without subordinates.
Constructor will allow you to create any type of request yourself, but this mode is recommended for users who already have some experience in creating requests.
Simple Query allows you to create a query using the Wizard to select from certain fields of tables or other queries. (This is the best way to create a query for beginners.)
When choosing an item Duplicate entries a request will be created to search for duplicate records in a simple table or in a query, and when you select the item
Records without subordinates - a request to search for records that do not match any record in the subordinate table. This query is used for multi-table databases.
You may be wondering how to create queries with parameters and change requests if not explicitly specified when creating the request? It should be noted that the basis for all these queries is the fetch query, i.e. you first need to define the dataset you want to work with. Then, for the generated select query, you need to go to design mode. Parameters are set in the Criteria line for the corresponding fields. This will be discussed in more detail below when completing the assignment. To access change requests, open the Request menu item - in the list that opens, you will see all types of change requests.
When you run a select query, Access retrieves the records from the tables and forms the resulting dataset. It looks like a table, although it is not. The result dataset is a dynamic (or virtual) recordset and is not stored in the database.
When a query is closed, the result dataset of that query ceases to exist. Although the dynamic dataset itself no longer exists, remember that the data it contained remains in the underlying tables.
When you save a query, only the structure of the query remains - a list of tables, a list of fields, sort order, restrictions on records, type of query, etc. When storing in a database, a query has a number of advantages over the resulting dataset:
the physical storage medium (usually a hard disk) requires less space;
the query can use updated versions of any records that have changed since the last time the query was run.
Each time it is executed, the query accesses the underlying tables and re-creates the resulting dataset. Since the result dataset itself is not saved, the query will automatically reflect any changes that have occurred to the underlying tables since the last time the query was run (even in real time in a multi-user environment).
Follow these steps to save your request. Run the command File, Save or click on the button<Сохранить>on the toolbar. If this is your first time saving a query, enter a new name for the query in the Save dialog box.
The best way to create a query is to use the Constructor (QBE language). There is a special icon in the window for this. Database ... It is called Create a query in design mode and opens a special window in QBE language mode (see figure 1). The window consists of two parts. The upper one displays the structure of the tables to which the query is addressed, and the lower area is divided into columns - one column for each field of the future resulting table.
Rice. 1 Window for creating a query in the modeQBE
|
Memo "Conditions of selection" |
|
|
Operator |
Description |
|
? Street |
A question mark replaces one character |
|
43rd place * |
An asterisk replaces multiple characters after a specific word |
|
Value less than 100 |
|
|
The value is greater than or equal to 1 |
|
|
<>"Moscow" |
All cities except Moscow |
|
Between 1 and 10 |
Values between 1 and 10 |
|
Is Null Is Not Null |
Finds empty records or finds all records except empty ones |
|
Like "a *" |
All words starting with the letter a |
|
> 0 And<=10 |
All values greater than 0 and less than 10 |
|
"Bob" Or "Jane" |
Values equal to either Bob or Jane |
Lecture 16. Creating queries by means MSAccess 2000
Queries are the only means to retrieve the information that users need, as well as a means of managing data. Based on the logic of relational operations, queries allow you to:
§ joining data from multiple tables;
§ display only required fields;
§ formation of complex criteria for the selection of records;
§ calculations using data from source tables;
§ grouping of information according to any criteria;
§ modification of data in tables.
MS Access provides convenient mechanisms for quickly creating queries without special training, while other DBMS often require knowledge of language tools to create queries. MS Access offers the following tools for creating queries:
§ Query constructor - a tool for constructing queries using QBE (Query by example - a query by example), requires minimal knowledge. A remedy inferior to the regime SQL.
§ Simple Query - allows you to quickly create queries based on one or more tables, including summary queries.
§ Cross query - allows you to create resulting tables based on the calculation results obtained by analyzing a group of tables;
§ Duplicate Record Request - a tool for creating queries to identify duplicate records, made in the form of a wizard.
§ Records without subordinates - allows you to create a query that finds those records of the main table that do not have subordinate records in the child tables.
Query creation begins by selecting the Queries category of the Databases dialog box. Then, by clicking the "Create" button, the "New request" dialog window can be called, in which one of the options for creating a request is selected (Figure 16.1).
Figure 16.1 - Dialog box "New query"
Making Simple Queries Using the Wizard
The Query Wizard allows you to form a query based on one or several tables with the ability to group (final query), but without defining the selection conditions, which is why it was named "Simple Query".
Figure 16.2 - Window of the query creation wizard
The process of creating a request using the wizard boils down to the following steps:
§ The wizard for creating simple queries is launched, for example, with the command - Create a request using the wizard
§ in the "Tables and queries" drop-down list, tables or queries are sequentially selected, information from which is required by the user, and then the required fields are moved from the "Available fields" list to the "Selected fields" list (Figure 16.2).
§ At the second step of the wizard, the type of request is determined: detailed or summary. If a summary query is selected, then it is necessary to define the summary operations on the query fields: Max, Min, Sum, Avg, or Count.
§ At the third step, we specify the name of the request and one of the options: open query to view data or change the request layout, in the first case, the query results will be displayed on the screen, and in the second, the query will open in design mode.
Create a query in design mode
The constructor is rarely used to create queries, more often it is used as a means of modifying existing ones. The query builder uses QBE (Query by example - a query by example) that allows you to form relatively complex queries based on a special form, filling out which allows you to form a query, of course, this method is inferior to the direct use of language constructs SQL , but requires much less knowledge.
You can use the language if necessary SQL , for this it is necessary to switch to the mode SQL , the transition is performed by the command of the context menu of the query designer - "Mode SQL ".
The constructor window (Figure 16.3) is divided into two parts. The upper part is intended for the location of the tables on the basis of which the query is formed. Adding tables to a query is carried out by the command on the toolbar - Show table.
Figure 16.3 - Query constructor window. An example of entering a condition.
The lower part is a request form (QBE),This is where the query itself is built. Formation of a request in QBE is carried out by defining the tables and fields involved in the query, output parameters, sorting, specifying the selection conditions, calculations, etc.
New fields are included in the query by simply dragging and dropping the field name from the table field lists (at the top of the designer) into the required column of the "Field:" line, and the table name is determined automatically.
If it is necessary to sort by any field in the column of this field, the "Sort" property is set to the value Ascending or Descending by choosing from the drop-down list.
String - "Display" controls the display of fields in the result dataset obtained as a result of the query execution.
For any of the fields, you can specify a selection condition, thereby filtering the data.
Query fields inherit the properties set for the corresponding table fields, but you can change the output format of the fields. To change the output format of fields, use the context menu command - Properties.
Number of lines in QBE may vary depending on the type of query you are creating, for example, when using grouping, a new line will appear - "Bulk operation".
Setting criteria for selecting records
Determining the selection criteria in queries is one of the main tasks. To form the data set required by the user, it is necessary to define the values of the selection conditions in the line "Selection condition:" QBE. In the selection conditions, you can use logical operators (or, and, not), comparison operators ( <, >, <=, >=, <>, = ), as well as the operatorsBetween, InandLike.
You can set several selection conditions connected by a logical operator or ( or) , for some field in one of two ways:
1. enter the entire condition in one cell of the line "Selection condition:" , using boolean operator or (or).
2. one part of the condition should be specified in the cell of the line "Selection condition:", and the second part of the condition should be entered into the bottom cell of the line "Or:".
Both options will be equivalent, the second option is shown in Figure 16.4.The result of this query will be a list of students with grades 4 or 5.
Figure 16.4 - Using the logical operator or ( or) .
Logical operation and ( and) is used when both conditions must be met simultaneously and only in this case the record will be included in the resulting data set. For example, the condition > 2 and<5 will give a list of students with grades 3 and 4.
Logical operationandcan be used not only for one field, but also for several fields, the conditions in which must be fulfilled simultaneously, however, in this case it is used implicitly. Figure 16.5 shows the implicit use of a boolean operationand... As a result of such a request, all students living in the city of "Ufa" will be issued and having a rating of 5. In fact, if you look at the code SQL (in SQL mode ) then you can see the following condition:
[Students]. [City] = "Ufa" and [Students and classes]. [Score] = “5”
Figure 16.5 - An example of using a logical operation and ( and)
Operators and and or used both separately and in combination. It should be remembered that the conditions associated with the operator and are met before the conditions combined by the operator or.
Operator B etween allows you to specify a range of values, for example a range from 10 to 20 can be defined as follows:
between 10 and 20
Operator I n allows you to use a list of values specified as an argument for comparison. For example:
in("first second Third")
Operator L ike useful for finding patterns in text fields, and allows you to use patterns:
* - denotes any number of characters;
Any single character;
# - indicates that there should be a digit in this position.
For example, to select a surname starting with the letter P and ending with “ov”, you can write the following condition:
LikeP * s
A special approach to working with date fields. The date value in the selection criteria must be enclosed in # symbols. For example:
>#31.12.1996#
If you need to highlight temporary events of a specific day, month or year, then you can use the built-in functions MS Access:
§ Day(date)- returns the value of the day of the month in the range from 1 to 31;
§ Month(date)- returns the value of the month in the range from 1 to 12;
§ Year(date)- returns a year value in the range from 100 to 9999;
The current date can be determined by the function Now () used with no arguments.
Calculated fields
In queries for selection, you can define calculated fields, the values of which will be obtained as a result of any arithmetic operations on the fields involved in the query. For example, if you need to determine the amount of the allowance equal to 15% of the salary, then you can write the following expression in the "Field" line of the free column of the request form:
Allowance: [Salary] * 0.15
This will create a calculated field named "Markup" containing the calculation result for each row of the resulting dataset.
When creating expressions for calculated fields, you can use"Expression Builder", which can be called by the "Build" button on the toolbar.
At the top of the window is the input area in which the expression is edited. The expression can be entered manually, but the arithmetic and logical operator buttons below the input area are commonly used. The lower, middle list contains a list of fields included in the request, these fields can be used in the expression. Fields are added by double-clicking or pressing the "Insert" button. pi fields of the fields included in the query, which can be used in the expression. expressions
Suppose there is a request “Goods issue from the warehouse”, which contains the fields “ColOp” (quantity issued) and “COP” (sale price), you need to determine the amount of the sale. Open the query in design mode, switch to an empty column and launch the Expression Buildercommand "Build", context menu... Find in the middle list the name of the field "DTTp" and double-click on it with the mouse. Press the "*" button and repeat the same sequence of actions for the "ColOp" field. We get the required expression (Figure 16.6).
Figure 16.6 - The window of the builder of expressions
Before you apply the generated expression, it is recommended that you define a name for the calculated field, such as Amount:
Amount: [ЦОтп] * [KolOtp]
After clicking "OK", the result will be placed in a new column of the QBE blank.
Control questions
1. What is a request?
2. What are the query functions?
3. What are the ways to create queries in MS Access?
4. How do the different ways of creating queries differ?
5. How is a request created using the wizard?
6. What is a request form?
7. How to create conditions for selecting records?
8. What are the main logical operators?
9. What is the b etween operator?
10. What is the purpose of the operator Like?
11. How are calculated fields created?
12. Advantages and Disadvantages of a Query Builder?
Self-study assignments
Exercise 1. Create a request "Expired books", according to the following description:
1. In the "Databases" window, select the "Queries" category.
2. Open the wizard window using the "Create a query using the wizard" command.
3. In the drop-down list "Tables and Queries" select the "Books" table. Move the Name and Author fields from the Available Fields list to the Selected Fields list.
4. In the drop-down list "Tables and Queries" select the "Instances" table. From the "Available fields" list to the "Selected fields" list, move the "Inventory number" fields , "Return date" and "Availability".
5. In the drop-down list "Tables and Queries" select the "Readers" table. From the "Available fields" list to the "Selected fields" list, move the "Number_BHB", "Full name", "Tel_home", "Tel_work" fields. Click the "Next" button.
6. At this stage, select a detailed request and click the "Next" button.
7. Specify the name of the request Expired_Books and click the "Finish" button.
8. Let's open the request "Expired_books" in design mode by clicking the "Design" button (Figure 16.7).
9. In the request form, in the line "Selection condition" for the "Return_Date" field, specify the value < Now().
10. In the request form, in the line "Selection condition" for the "Availability" field, set the value No and uncheck the "Display to screen" checkbox.
11. Let's close the constructor window.
Thus, the selected records must satisfy two conditions at once: the return date must be less than the current one and the book must be absent from the library.
Task 2. Create a simple query to get information about which subject areas the books belong to.
Task 3. Extract information about debtors from the request "Expired_books" (Library card number, full name, home and work phones)
Task 4. Create a request to get a list of books by the same author.
The main goal in creating a database is to organize the information in a table in an orderly manner, avoiding redundancy and achieving logic when combining them. Naturally, the user is not interested in all the data in the documents and the client is more interested in specific information than in its location. It is for this kind of filtering of information that queries exist. The select query in Access is one of the most used, it allows you to extract the necessary information, perform calculations, create cross-references, but without changing the information in the main tables.
How to use the Designer to create a select query in Access
As an example, try creating a list of employees with their position, salary and bonus based on the length of service. Naturally, the data of interest will be in different tables, so Access will select from related objects. To create, follow these steps:
The figure clearly shows that the data is not ordered, and it is not very convenient to view it in this form. When forming a request, this parameter should be kept in mind. To do this, in the form, you should use a line called "Sorting", with which you can build a list of allowances starting from the maximum, ending with the minimum value.
In addition, you can create a selection query in Access with a condition: for this, in the "Selection conditions" field, you must specify the required parameter (a certain amount of salary, view allowances only from project engineers, etc.).
The MS Access DBMS application is a full-fledged assistant for creating and maintaining enclosed in tables and arrays. If the database is too large, it is difficult to quickly find the required values.
This is why Access has a feature called queries. Let's consider what it is, how it works, what features it has.
Building queries in Microsoft Access
To figure out how you need to know the basic provisions of working with a DBMS.
There are two ways to complete this procedure:
- Query constructor.
- Query Wizard.
The first method makes it possible to create any of all available queries in manual mode, but with a small caveat that the user has experience with the Access application. Also, he must understand at least his main tasks. As for the second method, it needs to be considered in more detail.
An easy way for beginners
A knowledgeable person, in a few clicks of the mouse, selects those components that the user will need to fulfill the request, and then quickly forms the registry in accordance with the collected key values. If this is the first acquaintance with the DBMS, and the user has no idea how to create queries in Access, then the Wizard program is selected.
In this mode, you can familiarize yourself with and understand the following types of requests:
- Simple.
- Cross.
- Records without subordinates.
- Duplicate entries.
This choice is made already at the first stage of working with the Wizard. And in the future, following clear instructions, even a novice user can easily create a request. Let's get acquainted with its varieties.
Simple Query
This spreadsheet tool collects the data you want from user-specified fields. As the name suggests, this is the most popular type of query for newbies. Its convenience lies in the fact that such a procedure opens in a new tab. Therefore, the answer to the question of how to create a query in Access 2010 becomes obvious after opening the first menu of the wizard.
Cross query
This type of sampling is more complex. To figure out how to create in Access using the "Wizard" in this mode, you need to click on this function in the first window.
A table will appear on the screen in which you can select up to three columns located in the original.
One of the remaining unselected fields can be used as the headers of the query table. At the third stage of the procedure (intersection), another value is selected with the variability of the function (mean, sum, first, last).
The photo shows that a cross query has been created, and that the necessary actions have been performed according to the specified parameters.
Duplicate entries
As the name implies, the main purpose of this query is to select all the same rows in the table by the specified parameters. It looks like this:
In addition, a selection of additional fields is available to match multiple lines at once.
To select duplicate entries, you need to expand the list of requests and create a new folder there. Next, in the "New query" window, select the "Search for duplicate records" line. Next, you need to follow the instructions of the Master.
Records without subordinates
This is the last type of query available in the "Master - Entries without slaves" mode.
In this case, only those values are selected that are not used in any field of tables and queries, but which have already been created.
This type is relevant only in cases where there are several databases.
All four of these query types provide a basic starting point for working with complex elements, but make it easy to figure out how to create a query in an Access database.
Query Functions in MS Access
Let's figure out why you need to perform the steps described above. The goal of all simple and complex queries in Access DBMS is as follows:
- Collecting the necessary data in tables, their subsequent viewing, editing, adding new values.
- An excellent source material for the preparation of all kinds of reporting forms.
- Conducting mathematical and statistical counting procedures over entire data arrays with the display of totals on the screen (average value, sum, deviation, totals).
Sample request
This type of database work is complex as it requires the participation of multiple tables.
All tables need to have common key fields. Otherwise, the operation will fail.
Let's review how to create a select query in Access. First, you need to create a simple query with the selection of the required fields. Already here you can edit the data to bring it into the desired form. By the way, the changes made will be transferred to the original tables, so this point must be taken into account.
In the designer window that opens, the "Add tables" window is filled. Here you need to add those tables or queries from which you need to pull out the initial values.
After adding, you can start filling out the request conditions. For this we need the line "Field". In it, you need to select those values from the tables that will be displayed during the query.
To complete the operation, you need to click on the "Execute" button.
Request with parameters
This is another type of complex procedure that will require certain database skills from the user. One of the main areas of such action is preparation for creating reports with volumetric data, as well as obtaining summary results. How to create queries in Access 2007 using the designer will be discussed below.
To start this procedure for data selection, you need to create a simple query to select the required fields. Further, through the Constructor mode, it is necessary to fill in the "Selection condition" field and, based on the entered value, the selection will be carried out.
Thus, the answer to the question of how to create a query with a parameter in Access is simple - to enter the initial parameters for the selection. To work with the Constructor, you must use the Query Wizard. There, primary data is created for filtering, which serve as the basis for further work.
Advanced cross-reference query
We continue to complicate matters. Even more difficult to understand is the information on how to create queries in Access when multiple tables of data are present. Cross-reference query has already been considered above, as one of the options for working with the Wizard. However, in the "Design" mode, you can create a similar query.
To do this, you need to click "Query Builder" - "Cross".
The menu for adding source tables opens, as well as the ability to fill in selected fields. The only things to watch out for are the Bulk Operation and Cross Table items. They must be filled in correctly, otherwise the procedure will not be performed correctly.
Cross-queries are the easiest way to find and retrieve information from multiple data sources, plus the ability to generate charts and graphs.
Moreover, when using this procedure, the search is faster, even with several development options.
Of course, there are also "pitfalls" that can interfere with the work. For example, when creating a query to sort a database by column value, the system throws an error. That is, only sorting by standard items is available - "increasing and decreasing".
Summing up, it must be said that it is up to the user to decide how to create queries in Access - using the Wizard or the Designer. Although, for most people who use MS Access, the first option is more suitable. After all, the Wizard himself will do all the work, leaving only a few clicks for the user when choosing the request conditions.
To use the advanced settings, professional-level database experience is clearly required. If large databases are involved in the work, it is best to contact specialists in order to avoid disruption of the DBMS and possible data loss.
There is one point that is available only to programmers. Since the main DBMS language is SQL, the required query can be written in the form of program code. To work in this mode, it is enough to click on the line of the already created query, and in the opened context menu select "SQL mode".
The Query Builder is used to modify existing queries and to create new queries. To open a query in Design view, select one of the existing queries in the list, for example, the query you just created "Employees Query", and click the button Constructor(Design) on the window toolbar Database(Database).
The Query Builder window appears (Fig. 4.5). The top part of the window displays a table (or several tables, if the query is multi-table) in the form in which the tables are displayed in the window Data schema(Relationship). Tables are data sources for a query, we will call basic query tables. At the bottom of the window is request form- a table whose cells are used to define a query. The layout displays all of the columns included in the query result set.
In order to view the entire form of the query and all the source tables, use the scroll bars.
The toolbar appears in the Access toolbar area Query constructor(Queries Design). This panel is shown in Fig. 4.6, and in table. 4.1 describes the buttons on this toolbar and the corresponding menu commands.
Rice. 4.5. Query "People Query" in Query Design view
Rice. 4.6. Toolbar Query constructor
Table 4.1. Description of toolbar buttons Query constructor
Menu command | Description | |
| View | View, Datasheet View | Displaying a query in various modes. To change the display mode of a query, click the arrow to the right of the button and select the required item in the list that appears. If you just click this button, the query will be displayed in Table mode |
| Save | File, Save | Saving an active request |
| Print query results without opening the Print dialog box | ||
| Print Preview | File, Print Preview | Previewing a Query Before Printing |
| Spelling | Service, Spelling (Tools, Spelling) | Spell Checking in Request Text Fields |
| Cut | Edit, Cut | Removing selected objects from a query to the Windows clipboard |
| Copy (Soru) | Edit, Copy | Copying the selected query objects to the clipboard |
| Paste | Edit, Paste | Pasting the contents of the clipboard into a request |
| Format Painter | Copy formatting options from one selected object to another of the same type | |
| Undo | Edit, Undo | Undoing the Last Modified Request |
| Query type (Query Type) | Query, Select Query | Changing the type of request. To change the type of request, click on the arrow to the right of the button and select the required type of request from the list |
| Run | Query, Run | Executing a request |
| Show Table | Query, Show Table | The Show Table dialog box is displayed |
| Group operations (Totals) | View, Totals | Grouping records in a query and calculating totals |
| Top Values | Display only the first records of the request. The number of displayed records is indicated in the input field either in pieces or as a percentage | |
| Properties | View, Properties | Opening property windows of selected objects: query or query field |
| Build | Calling the builder to create an expression. The button is only available when a query property or query field is enabled that accepts an expression, such as Criteria | |
| Database Window | Window, 1 (Window, 1) | Database window display |
| New Object | Creation of a new database object. To select the type of object to create, click the arrow to the right of the button | |
| Microsoft Access Help | Help, Microsoft Access Help | Calling the Assistant and Getting Help for Access 2002 |
You will become familiar with using the toolbar buttons as you study working with queries, both in this chapter and in chap. eight.
Now let's look at how to create a new query using the Query Builder. This requires:
>Examples of different ways to include fields in the query result in the Query Design mode are shown in Fig. 4.9-4.11.
Rice. 4.9.
Rice. 4.10.
To add another table or other query to a query, you need to:
Comment
You can also add a table or another query to the Query Builder window by dragging the table or query name from the database window.
To remove a base table from a query, select it by clicking anywhere in the list of its fields and press the key
To remove a field from a query, select the required column in the query design, and then press
The fields in the table that is the result of the query are displayed in the order in which they appear in the query layout. If you need to change their order, rearrange the columns in the query design accordingly. This is done in a standard way, that is, first the desired column or several columns are selected, and then they are dragged with the mouse to a new location. In this case, you need to release the mouse button when the mouse pointer is in front of the column that is divided to the right of the inserted column. After insertion, all columns to the right of the pointer are shifted to the right. If you are moving columns to the end of the query, release the mouse button when the pointer is in front of the first free column.
In Query Design mode, you can change the names of the query fields. To rename a field, you must position the cursor in the query form in front of the first letter of its name and enter a new name and a colon symbol. An example of a request with changed field names is shown in Fig. 4.12. Changing a field name in a query design changes the column heading when you view the query in table mode. In addition, if you create a new object, such as a form or report, based on a query, the new field name will be used in the new object. This does not change the name of the base table field. This name also does not change in those forms and reports that were generated from the request before the field name was changed. Field names in queries must follow Microsoft Access naming conventions.
Rice. 4.12.
To use the new field name only in column headings in table view, or as a label for fields in forms and reports, set the property to Signature(Caption) rather than renaming a field on a letterhead. ( For setting field properties, see Sect. "Setting properties of fields in a query and properties of the query itself" Ch. eight)
The width of the columns in the query design can also change. This is done by simply dragging the column border or double-clicking on the border line, as is done in an Excel table.
Comment
Changing the column widths in the Query Design window or in the Advanced Filter window does not affect the column widths of the query in Tables mode or of the filtered object.
In line Selection condition(Criteria) and the line or (Or) specifies the criteria for selecting records. Boolean expressions can be such conditions. For example, (> 30), (= "Ivanov"), (= 10), etc. (For creating expressions in query conditions, see "Using Expressions in Queries" later in this chapter.)
Conditions that are on the same line, but in different columns of the form, are combined by the logical And operator. If you need to combine selection conditions by the logical operator Or (OR), place these conditions on different lines of the query form. An example of using several selection conditions is shown in Fig. 4.13.
Rice. 4.13.
If the selection criterion is very complex, you can insert additional condition lines. This requires:
- Select the line that should appear below the inserted line. To do this, move the mouse pointer to the left border of the line (it should turn into a bold arrow pointing to the right) and click the left mouse button.
- Execute command Insert, Rows(Insert, Rows) or press key ... A new blank line is inserted above the selected one. Selection conditions can be entered into it.
To remove a selection criteria line:
- Left-click anywhere on the line.
- Run the command Edit, Delete Lines(Edit, Delete Rows).
To set the sort order of records in a query, use the line Sorting(Sort). For each field by which records are to be sorted, select the appropriate sort order from the list: Ascending(Ascending) or descending(Descending). By default, all query fields are set to (absent)(Not sorted). Example of using a combo box Sorting(Sort) is shown in Fig. 4.14.
If you need to sort the records in a query by several fields, place them in the query layout in such a way that they are sorted from left to right - this is the order in which the sorting will be performed.
You can easily remove a field from the resulting query table by simply unchecking the checkbox Output on display(Show) in this column of the query sheet. By default, this check box is selected for all query fields. The situation where a field should not be displayed in the query result usually occurs when it is included in the query form only to specify a selection or sorting condition for records. An example of such a request is shown in Fig. 4.15. All fields in the Products table are included in the query (this is specified in the first column of the query design), and the TypeID and Brand fields are included only to indicate the sort order. Therefore the checkbox Output on display(Show) is cleared for these fields. Otherwise, these fields would be displayed twice.
Rice. 4.14.
Rice. 4.15.
If you want to clear the request form in order to create a new one, you need to run the command Edit, Clear Form(Edit, Clear Grid).
After the request form is formed, you can save it by clicking on the button Save(Save) on the toolbar or by executing a menu command File, Save(File, Save). In this case, a dialog box appears in which you need to enter the name of the saved request (Fig. 4.16). You can save the request and close it.
You can see the results of the query by switching to Datasheet View using the button View(View) or by clicking Running(Run) on the toolbar.
Width = "351" height = "182">
Rice. 4.16. Dialog window Preservation
The result of executing a query created in Design mode is shown in Fig. 4.17.
