You can use a query in access. Creating various queries in Microsoft Access. Query by condition

4. Formation of queries to the database

4.1 General concept requests

A query is a requirement to retrieve data from database tables, to perform calculations on the data, to make changes to the database.

A query can serve as a data source for forms, reports, and data access pages.

The Access DBMS allows you to create three types of queries: select queries, cross-queries, and queries to make changes to the database.

A select query is the most commonly used query type. Its result is a dynamic table that can be viewed and analyzed. A sample request allows you to:

Include fields from one or more tables in the resulting table in the desired order;

Select records that meet the selection conditions;

Perform calculations on database fields;

Group records and find total values ​​for certain fields for groups of records or for all records using statistical functions: Sum – sum of field values, Avg – average of field values, Max – maximum of field values, Min – minimum of field values, Count – number field values, etc.

A type of selection request is a request with parameters - this is a request that, when executed, displays in its own dialog box a prompt to enter the value of the record selection criterion that interests the user.

A cross-query is a special summary type query. It displays the results of final statistical calculations over the values ​​of some field in the form of a crosstab. In it, the values ​​of one or more columns on the left form the row headings. Column headings represent the values ​​of a particular field, and at the intersection of rows and columns are the total values.

There are four types of database change requests:

1) delete request - deletes a group of records from one table or several interrelated database tables for which cascade deletion of related records is specified;

4) table creation query - creates a new table based on all or part of the data from one or more database tables.

The Access DBMS allows you to create queries using Wizards and using the Designer. Wizards are used to create specific queries. Using the Designer you can create any request.

4.2 Creating a selection query

To create a selection query using the Designer, you must open the Query Designer window by following these steps:

DB window - Queries object - [Create] - New query window - select Designer - Add a table window - select query source tables - [Close].

The Query Builder window looks like in Fig. 4.1.

Rice. 4.1 Query Builder window

It is divided into two panels.

The top panel contains the query data schema. It provides lists of fields selected as the source of the table and query query. If a relationship was previously created between these tables, it is shown in the data diagram. Otherwise, you may see a relationship that was automatically created by Access. The user can install it himself new connection between tables.

The bottom panel contains a sample request form (QBE request). It is presented in the form of a table designed to determine the structure of the resulting query table and set the conditions for selecting data from the tables. Each column of the form refers to one table field. The lines of the form have the following purpose:

· Field – indicates the names of the fields involved in generating the request;

· Table name - indicates the names of the tables to which these fields belong;

· Sorting – makes it possible to sort records in the resulting query table;

· Screen display – allows you to control the display of fields in this table;

· Selection condition – used to set the conditions for selecting records;

· or – allows you to combine selection conditions logical operation OR. In this case, the selection conditions can be indicated in several lines of the request form.

You can include individual fields in the request form in one of the following ways:

· drag a field from the list of fields to the leftmost free cell of the Field line;

· double-click on the field name in the list of fields;

· click in the Field line cell and select the desired field from the drop-down list.

You can include all table fields in the request form if:

· double-click on the table name and drag all selected fields into the cell of the Field line;

· drag the asterisk located under the table title into the cell of the Field line. In this case, only the table name with an asterisk will be displayed in the cell, but all the fields of the table will be included in the query result.

To clear individual columns of the request form, use the command - Edit/Delete Columns.

To clear the entire request form, use the command - Edit/Clear form.

To create a query to display fields from one or more tables, only those fields that should be present in the resulting query table, or all fields of all tables, are dragged from the lists of fields into the Field row, and then in the Display term, only the necessary ones are checked with checkboxes.

Forming a query using sorting.

Sorting is possible by one or more fields at the same time. To sort by multiple fields, the fields are arranged on the request form in the order in which you want to sort. In Access, records are sorted first by the leftmost margin, then by the field in the next column to the right, and so on. To set the sort order, click in the Sort line for the field by which records are sorted, and select ascending or descending from the drop-down list. For an example of creating a query using sorting, see Appendix 9-10.

Generating a request with selection conditions.

Filter conditions are restrictions that are placed on a query to determine which records are included in the resulting query table. They are specified by expressions in the lines Selection condition, or.

If expressions are entered into several cells of the same Selection Condition line, they are automatically combined using the logical And operator. If expressions are entered on different lines of the query form, Access combines them logical operator Or. An example of creating a request with a condition for created base For data, see Appendix 11-12.

In the selection condition, the Between construction can be used. For example, Between 100 and 200. For the result of constructing a query using the Between construct, see Appendix 13-14.

Generating a query with a calculated field.

To create a calculated field, enter the name of the calculated field followed by a colon in the empty cell of the Field line, followed by an expression. For example,

COST: [PRICE]*[QUANTITY]

If the expression is complex, then it is advisable to use the expression builder to create it.

For the designed database, a query with a calculated field has the form - see Appendix 15-16.

Forming a query with grouping.

Often you need to see not all records in a table, but only the total values ​​for groups of records. Totals for some group fields are calculated using the statistical functions described above.

To create a query with grouping, do the following:

· dragged to the first cell of the line Field is the field by which records are grouped. Then the fields for which the results are summed up are dragged into subsequent cells;

· Enter the command View / Group Operations. Appears on the request form new line A group operation in which all fields are set to Group;

· in the Group operation line, click on the fields for which totals are being summarized and select the required statistical function from the drop-down list.

Generating a request with parameters

If you need to frequently execute the same selection query, changing only field values ​​in the selection conditions, then it is advisable to create a query with parameters. In the Selection Condition lines, or for fields that play the role of parameters, the text of the invitation to enter the values ​​of these fields that interest the user is entered in square brackets. This text will appear in the Enter a Parameter Value dialog box when you run the query. The result of constructing a query with parameters is shown in Appendix 17-18.

4.3 Creating a cross-query

Creating a cross-query using the Designer begins by opening the Query Designer window. In this window, the following are sequentially dragged into the request form:

· fields whose values ​​will be the row headings of the crosstab;

· a field whose values ​​will be the headings of the crosstab columns;

· the field for which the total is summarized using a statistical function.

Then the Query/Cross command is executed. As a result, two new rows will appear in the request form: Group Operation and Crosstab. In the Crosstab row, for fields with values ​​as row headings, select Row Headings from the drop-down list, and for fields with values ​​as column headings, select Column Headings. In the Group operation line for the field for which the total is being summarized, the required statistical function is selected from the drop-down list.

The result of creating a cross-request is presented in Appendix 19-20.

4.4 Creating action requests

Generating a request to create a database table.

After opening the query designer window, enter the command Query / Create Table

In the Create Table window that appears, indicate the name of the table to be created and where it should be placed - in the current database or in another database. Then, from the lists of fields, the fields that should be in this table are dragged into the request form, and, if necessary, the conditions for selecting records are set. After executing the query, the new table will be in the list of tables in the database window.

Generating a request to update database table fields

After opening the request designer window, enter the Request / Update command in the request form, a new line Update will appear. Then all the fields of the table are dragged into the request form, and for the fields to be updated, expressions are specified in the Update line, the values ​​of which will be the new values ​​of the fields being updated. If it is necessary to update field values ​​only in some records, record selection conditions are set.

Generating a request to add records to a database table.

When you open the query designer window, the source of the query is the table from which records are added to another table. The source table records must contain the same fields as the database table being updated. In the query designer window, enter the Query / Add command. The Add dialog box will appear, in which you need to specify the name of the table to be updated and where this table is located - in the current database or in another database. In addition, a new line Addition will appear in the request form.

Then those fields from the list of fields of the source table that coincide with the fields of the updated table are dragged. Access will automatically indicate their names in the Add line as the names of the fields of the updated table.

Generating a request to delete records from a database table.

If a relationship is established between the tables, ensuring data integrity, but without cascading deletion of records, then a request is first made to delete records from the subordinate table, and then from the main one.

In this case, when deleting records from a subordinate table, enter the Query / Delete command in the query designer window. As a result, a new line, Delete, will appear in the request form. Then the asterisk symbol (*) is dragged from the list of fields of the subordinate table into the request form, after which the value From is displayed in the Delete line for this field. Then, from the list of fields of the main table, the fields participating in the selection condition for records to be deleted are dragged, and for them the value Condition will appear in the Delete line. After this, the conditions for selecting records to be deleted are set.

If the relationship between tables ensures data integrity and cascade deletes of records, then a request is created to delete records only from the main table.

When composing a query to delete records from the main table (in both cases), enter the Query / Delete command in the query designer window.

The fields involved in the conditions for selecting records for deletion are dragged into the request form, and the selection conditions are set.

4.5 Executing and saving a request

After generating a request, it must be executed. From the Query Builder window, this can be done by entering one of the commands.

Let's assume that the user needs to see all documents in information base one list. Despite protests from the developer due to the negative impact on the productivity of this development, under pressure from management, it was decided to make such a report.

Well, since the task requires implementation, let's do it!

How to do it

Let’s immediately make a reservation that the report will display two fields: “Link” and “Passed”. All documents have these standard details. We will generate a request to obtain all documents programmatically, bypassing the “Metadata.Documents” metadata collection. For each element of the collection, we will create a query to its table using the details “Link” and “Posted”, and then combine its result with the result of a similar query to another document.

" SELECT | Doc . Link AS Link,| Doc . Conducted AS Conducted |FROM| Document . ABCClassificationBuyers AS Doc. Link AS Link,| Doc . Conducted AS Conducted |FROM| Document . AdvanceReport AS Doc| COMBINE ALL | SELECT | Doc . Link AS Link,| Doc . Conducted AS Conducted |FROM| Document . Letter of Credit Transferred AS Doc| COMBINE ALL | . . . "

In the case where you need to display all the details available in all documents, then programmatically generating a request will look much more complicated. In order not to complicate the example, we will display only the named standard details.

When composing the report result, it will be necessary to place the generated query in the main data set. The remaining actions when generating a report programmatically will be standard.

Implementation

Now, in order. Having created an external report, we will add a data composition scheme to it. Let's add the following query as a query in the data set:

In general, in our case it does not matter what kind of query there will be for the data set. We added it only to make it convenient to customize the report structure in the designer, since otherwise there would be no fields on the “Settings” tab. The final setup of the report structure is as follows:

Now in the event handler "When Composing Result" we will disable standard processing and generate the report programmatically. The program code for generating a report on the access control system is presented in the following listing:

Procedure When Composing Result (DocumentResult, Decryption Data, StandardProcessing) StandardProcessing = False ; // disable standard report output - we will output it programmatically Settings = SettingsComposer. Settings; // Get report settings DecryptionData = newDataCompositionDecryptionData; // Create decryption data LayoutLinker = newDataLayoutLayoutLinker; // Create a layout builder // Initialize the layout layout using the data layout schema // and previously created settings and decryption data" ) ; Layout Layout = Layout Linker. Execute (Data Layout Schema, Settings, Decryption Data) ; // Compose the result CompositionProcessor = newDataCompositionProcessor; ProcessorLayout. Initialize(LayoutLayout, , DecryptionData) ; DocumentResult. Clear() ; // Output the result to a spreadsheet document OutputProcessor = New OutputProcessorDataCompositionResultInTabularDocument; Output Processor. SetDocument(DocumentResult) ; Output Processor. Output(LayoutProcessor) ; End of Procedure

Everything is standard. Now the main thing. We must replace the query text for the dataset before composing the layout. As mentioned above, the request code will be generated when traversing the document metadata collection. The following listing shows the program code for dynamically generating the request text:

// .................................................................. DataLayoutSchema = GetLayout(" MainData Layout Schema" ) ; // Get the data layout scheme DataComposition Schema. Datasets. Dataset1. Request = " " ; // Clear the query in the dataset DocumentsConfigurations = Metadata. Documentation; // Get a collection of document metadata Number ofDocuments = ConfigurationDocuments. Quantity() ; // Determine the total number of documents in the configurationСч = 1; RequestText = " " ; For Each Doc From DocumentsConfiguration Loop Query Text = Query Text + // For each document, we form the text of the query for its table" SELECT | Doc . Link AS Link,| Doc . Conducted AS Conducted |FROM| Document . " + Doc. Name + " AS Doc " ; // We only need to substitute the name of the document table in the metadata tree into the request text If Account Number of Documents Then // If the document is not the last one in the collection, add the instruction "COMBINE ALL" for RequestText = RequestText + // combining the results of queries for documents into a single list" |UNITE ALL | " ; EndIf ; Sch = Sch + 1 ; EndCycle ; DataComposition Schema. Datasets. Dataset1. Request = RequestText; // Place the new request text into the data set LayoutLayout = LayoutLocker. Execute (Data Layout Scheme, Settings, Decryption Data) ; // Compose the layout // ....................................................................

The report is ready for testing.

What's the result?

Let's run the report in 1C:Enterprise mode. The report will be executed successfully and we will see a list of all documents in the infobase.

For all actions taken, the report has everything standard features SKD: decoding, setting up the report structure, selections, conditional design, etc.

Querying tables of all documents is not the best optimal solution to view full list documents in the information base, since it generates a relatively “heavy” DBMS query. It would be most correct to use the “Document Log” configuration object, but this is beyond the scope of this article.

In 8.3.5 we now have the ability to work programmatically with the request text.
The news about this can be read on the 1C website "Managed Query Designer and Query Scheme Object Model".
To analyze a specific request, I recommend

Let's try to figure out what a request actually is. In what format is it presented in 1C and on the information security server?

Simplified, the following levels of working with a request can be distinguished:
1. Query in DBMS language. At this level, the query is a text string in the syntax of a specific DBMS.
For example "SELECT field1 FROM table1 WHERE table1.field2 > 100".
2. Driver for a specific DBMS. This is a utility that serves as a link between the program and the server. It receives a request from the program and transmits it to the DBMS.
At this level, 1C works with external data sources.
3. Universal software interface. This is a universal component that allows the program to access data in various information security systems. It can also be called a driver manager. The specific DBMS drivers used on the computer are connected to it in the form of a DSN. Depending on the system used, there may be an ODBC, OLE DB, JDBC, ADO.NET or any other standard component. Judging by http://its.1c.ru/db/metod8dev#content:2926:1, 1C currently uses OLE DB. Platform 7.7 used ODBC.
4. Object on the 1C server. At this level, the application server handles the request.
As far as I understand, the C platform describes a query object for which methods for generating SQL text are specified. Platform objects (query designer, builder, etc.) work with this request object.
5. Request in 1C language. At this level, the query is a text written in pseudo-SQL language of the form “SELECT Directory.DirectoryName.FieldName FROM Directory.DirectoryName”

As far as I understand, with the introduction of the “Query Schema” object, nothing new was invented in the platform.
Previously, we could only work with the text representation of the request (level 5). This is the simplest and most understandable method, although not always convenient.
Now we have been given access to an object (level 4), which previously only worked with platform mechanisms.
If you analyze the Query Schema object, its properties very clearly overlap with the query designer.
Each element and property can be found with a visual analogue in the query designer.
For each click in the constructor, you can select a similar command.
For example:
Tab "Query Package" = RequestScheme.QueryPackage;
Tab "Tables and fields" = QueryScheme.QueryPackage.AvailableTables;
Click on the table "Directories" - "Nomenclature" = Request Schema.Query Package.Operators.Sources.Add("Directory.Nomenclature");

This way we can programmatically generate almost any request that we can make with a constructor.
Of course, programmatic creation is much more complex and time-consuming than simply working with a constructor or even writing the query text manually.
For static queries, the text of which does not change, there is no point in using the scheme.
Programmatic work requires a deeper understanding of working with queries.
It is convenient if we need to change the request text dynamically depending on system parameters, user choice, and the result of the previous request.

To modify the text of an existing request, this text must be set for the schema and received back after modification. To do this, use code like:

RequestScheme = New RequestScheme; RequestScheme.SetRequestText(Request.Text); //Here are our request modification commands Request.Text = RequestScheme.GetRequestText();

Query object model includes the main object "Query Schema" and about 50 related subordinate objects, collections and specific data types.At first, it is quite difficult to navigate this set of objects.

To understand the general structure of the request scheme, open the required section of the SP and enter all the connections between objects into the plotter.
It turns out this is the graph.
I can’t say that it is very clear, but it reflects the general idea. It is convenient to peek when analyzing a request.
The column indicates:
Yellow - object entities of the request scheme;
Red - collections of objects;
Green - standard types 1C (string, number, boolean);
Turquoise - primitive types specific to the request scheme;

The query scheme is most convenient for modifying a ready-made query; it also allows you to create the query text from scratch.

For example, let's consider creating a request package from scratch, similar to the request from

SELECT ALLOWED PRODUCTS.Link AS Nomenclature, Purchases.Period AS Period, ISNULL(Purchases.AmountTurnover, 0) AS AmountPurchases, 0 AS AmountSales PLACE TableTurnover FROM Directory.Nomenclature AS Products LEFT JOIN Register Accumulations.Purchases.Turnover(&Start, &End , month) HOW Purchasing Software Purchasing.Nomenclature = Products.Link WHERE NOT Products.ThisGroup COMBINE ALL SELECT DIFFERENT FIRST 100 Products.Link, Sales.Period, 0, ISNULL(Sales.AmountTurnover, 0) FROM Directory.Nomenclature AS Products LEFT JOIN Register Accumulations.Sales .Turnover(&Start, &End, Month) AS Sales BY Sales.Nomenclature = Products.Link WHERE NOT Products.ThisGroup INDEX BY Nomenclature, Period; //////////////////////////////////////////////// //////////////////////////// SELECT TableTurnover.Nomenclature AS Nomenclature, TableTurnover.Period AS Period, SUM(TableTurnover.AmountPurchases) AS AmountPurchases, SUM(TableTurnover.AmountSales) AS AmountSales FROM TableTurnover AS TableTurnover GROUP BY TableTurnover.Nomenclature, TableTurnover.Period HAVING SUM(TableTurnover.AmountPurchases) > 0 ORDER BY TableTurnover.Nomenclature.Name, Period TOTAL AND SUM(Amount of Purchases), SUM(Amount of Sales ) IN GENERAL, Nomenclature HIERARCHY ONLY; //////////////////////////////////////////////// ///////////////////////////// DESTROY TableTurnover

Let's analyze the request package.

A package is a set of three requests.
In the first request, data is selected from the IS by two operators (purchase and sales requests), each of which uses two sources (the "Nomenclature" directory and the corresponding accumulation register). The data will be combined from the two statements and placed in a temporary table on the server.
In the second request, we receive data from a temporary table, group it and get the result for processing on the 1C server.
The third query is used to destroy the temporary table created by the first query.

Let's start programmatically creating a package of requests.

To develop the code, we will use a query tree. It's convenient define paths to data. It is advisable to periodically check the resulting request text after code modifications.

First, let's create a "Request Schema" object.

RequestScheme = New RequestScheme;

The generated query schema already contains one package and one select statement.
For ease of use, let's save them into separate variables.

SelectionRequestFromIB = RequestScheme.RequestPackage; PurchaseSelectionOperator = SelectionRequestFromIS.Operators;

Adding sources into the first select statement.
To do this, we use the source collection method “Add()”. In the method parameters, we indicate the name of the table to be added, its alias, by which we will refer to it in the future.
As the table to be added, you can use either the name of the table or a link to it in the collection of available tables.

SourceNomenclature = PurchaseSelectionOperator.Sources.Add("Directory.Nomenclature","Products"); SourcePurchases = PurchaseSelection Operator.Sources.Add("Accumulation Register.Purchases.Turnover","Purchases");

By default, the second source is added with a left connection to the first.

We need a reverse connection. Changing the connection type.

SourcePurchase.Connections.ConnectionType = RequestSchemeConnectionType.RightExternal;

Table options:
For each table added as a source, we can set additional parameters.
The settings collection is populated automatically when you add a table, depending on the table type.
You cannot add a parameter to it manually; you can only set the value of an existing parameter.
The "Directory.Nomenclature" table is a simple directory table. It has no parameters.
The table "Accumulation Register.Purchases.Turnover" is a table of turnover of the accumulation register.
According to the SP, it has 4 parameters: the beginning of the period, the end of the period, periodicity, and condition.
We need to set the first 3 parameters:

SourcePurchasing.Source.Parameters.Expression = New QuerySchemaExpression("&Start") ; SourcePurchasing.Source.Parameters.Expression = New RequestSchemaExpression("&End") ; SourcePurchases.Source.Parameters.Expression = New QuerySchemaExpression("Month") ;

Let's indicate the columns, which must be selected from the tables listed.
To do this, we add fields to the collection of selectable fields of the selection operator.

PurchaseSelectionOperator.SelectableFields.Add("Products.Link"); PurchaseSelectionOperator.SelectableFields.Add("Purchases.Period"); OperatorSelectingPurchases.SelectableFields.Add("ISNULL(Purchases.AmountTurnover, 0)"); PurchaseSelectionOperator.SelectableFields.Add("0");

We indicate at the request level as a whole aliases for selectable columns:
Please note that we select table columns for each join subquery; we assign column names as a whole for the “Columns” collection at the package query level.

RequestSelectFromVT.Columns.Alias ​​= "Nomenclature"; QuerySelectFromVT.Columns.Alias ​​= "Period"; SelectionRequestFromVT.Columns.Alias ​​= "PurchasesAmount"; RequestSelectFromVT.Columns.Alias ​​= "SalesAmount";

Let's add a condition for selecting data

SalesSelectionOperator.Selection.Add("NOT Products.This is a Group");

We add everything similarly for the second part of the request.

Column mapping:
In each selection statement, we select 4 fields (the directory element "Nomenclature", a date and two numeric fields).
When combining columns from two operators selecting a column from a directory and a date, the system will compare it itself.
There are two numerical columns. The schema may itself map them incorrectly to the columns of the first query.
We indicate which column of the result table corresponds to which expression.

SelectionRequestFromISB.Columns.Fields.Set(1,PurchaseExpression); RequestSelectFromIB.Columns.Fields.Set(1,SalesExpression);

We repeat everything similarly for the second request of the package.

Of the differences, here we use selection based on final values ​​of the type “HAVING”.
This condition is added similarly to the usual selection for detailed records.
The scheme itself will determine in which section of the conditions to place ours, depending on the use of the grouping functions.

OperatorSelect.Selection.Add("SUM(TableTurnover.AmountPurchases) > 0");

The last step is to add a request to destroy the data table

DestructionRequestVT = QuerySchema.QueryPackage.Add(Type("DestroyTableRequestSchemaQuery")); DestructionRequestVT.TableName = "TableTurnover";

We also exhibit additional properties requests:

QuerySelectFromIB.TableForRoom = "TableTurnover"; RequestSelectFromIB.SelectAllowed = True;

The final complete version of the request:

//Creating a request schemaRequestSchema = NewRequestScheme; SelectionRequestFromIB = RequestScheme.RequestPackage; //Setting the query properties QuerySelectionFromIB.TableForRoom = "TableTurnover"; RequestSelectFromIB.SelectAllowed = True; //Add selection operators for the first request of the package PurchaseSelectionOperator = SelectionRequestFromIS.Operators; SourceNomenclature = PurchaseSelectionOperator.Sources.Add("Directory.Nomenclature","Products"); //Add a selection request from the procurement register SourcePurchases = PurchaseSelection Operator.Sources.Add("Accumulation Register.Purchases.Turnover","Purchases"); SourcePurchasing.Source.Parameters.Expression = New QuerySchemaExpression("&Start") ; SourcePurchasing.Source.Parameters.Expression = New RequestSchemaExpression("&End") ; SourcePurchases.Source.Parameters.Expression = New QuerySchemaExpression("Month") ; //Change the connection type Purchasing Source.Connection.ConnectionType = RequestSchemeConnectionType.RightExternal; //Specify the selectable fields PurchaseSelection Operator.SelectableFields.Add("Products.Link"); PurchaseSelectionOperator.SelectableFields.Add("Purchases.Period"); OperatorSelectingPurchases.SelectableFields.Add("ISNULL(Purchases.AmountTurnover, 0)"); PurchaseSelectionOperator.SelectableFields.Add("0"); //Specify aliases for the selected fields Selection RequestFromIB.Columns.Alias ​​= "Nomenclature"; SelectionRequestFromIB.Columns.Alias ​​= "Period"; SelectionRequestFromIB.Columns.Alias ​​= "PurchasesAmount"; SelectionRequestFromIB.Columns.Alias ​​= "SalesAmount"; //Add selection SelectionOperatorPurchases.Selection.Add("NOT Products.This is a Group"); ////////////////////// //Select sales data SalesSelectionOperator = SelectionRequestFromIS.Operators.Add(); SourceNomenclature = SalesSelection Operator.Sources.Add("Directory.Nomenclature","Products"); SalesSelectionOperator.SelectableFields.Add("Products.Link"); //Add the pH source and set the parameters Sales Source =SalesSelectionOperator.Sources.Add("Accumulation Register.Sales.Turnover","Sales"); SalesSource.Source.Parameters.Expression = New QuerySchemaExpression("&Start") ; SalesSource.Source.Parameters.Expression = New QuerySchemaExpression("&End") ; SalesSource.Source.Parameters.Expression = New QuerySchemaExpression("Month") ; SourceSales.Connections.ConnectionType = RequestSchemeConnectionType.RightExternal; //Specify the fields to be selected and set the numeric fields to correspond with the fields of the first request SalesSelection Operator. SelectableFields.Add("Sales.Period"); ExpressionPurchases = SalesSelectionOperator.SelectableFields.Add("0"); SalesExpression = SalesSelectionOperator.SelectableFields.Add("ISNULL(Sales.AmountTurnover, 0)"); SelectionRequestFromISB.Columns.Fields.Set(1,PurchaseExpression); RequestSelectFromIB.Columns.Fields.Set(1,SalesExpression); //Add selection SalesSelection Operator.Selection.Add("NOT Products.This is a Group"); //Indexing the data Selection Request From IB.Index.Add(Selection Request From IB. Columns); RequestSelectFromIB.Index.Add(SelectRequestFromIB.Columns); //Set data selection parameters SalesSelectionOperator.SelectVarious = True; SalesSelectionOperator.NumberofRecordsReceived = 100; //////////////// //Second request package SelectionRequestFromVT = RequestScheme.RequestPackage.Add(); OperatorSelect = QuerySelectFromVT.Operators; //Set the temporary table created in the previous request as a source Source = OperatorSelect.Sources.Add("TableTurnover","TableTurnover"); OperatorSelect.SelectableFields.Add("TableTurnover.Nomenclature"); OperatorSelect.SelectableFields.Add("TableTurnover.Period"); OperatorSelect.SelectableFields.Add("SUM(TableTurnover.AmountofPurchases)"); OperatorSelect.SelectableFields.Add("SUM(TableTurnover.AmountSales)"); //Selection condition OperatorSelect.Selection.Add("SUM(TableTurnover.AmountPurchases) > 0"); //Set column aliases Selection QueryFromVT.Columns.Alias ​​= "Nomenclature"; QuerySelectFromVT.Columns.Alias ​​= "Period"; SelectionRequestFromVT.Columns.Alias ​​= "PurchasesAmount"; RequestSelectFromVT.Columns.Alias ​​= "SalesAmount"; //Order of data sorting QuerySelectFromVT.Order.Add(QuerySelectFromVT.Operators.Sources.Source.AvailableFields.Fields); RequestSelectFromVT.Order.Add(SelectRequestFromVT.Columns); //Results of the request TotalNomenclature = Request for Selection From VT. ControlPointsTotals.Add(Request for Selection From VT. Columns); ResultNomenclature.ControlPointType = RequestSchemaControlPointType.HierarchyOnly; QuerySelectFromVT.GeneralTotals = True; QuerySelectFromVT.ExpressionTotals.Add(QuerySelectFromVT.Columns); QuerySelectFromVT.ExpressionTotals.Add(QuerySelectFromVT.Columns); //////////////// //The last request of the package is deleting the temporary table DestructionRequestVT = RequestSchema.RequestPackage.Add(Type("DestroyTableRequestSchemaTable")); DestructionRequestVT.TableName = "TableTurnover";

Having executed this set of commands, we receive the same request that we previously received from the constructor.

It is clearly evident that software creation is much more difficult than text creation.
The question arises, what is the point of fooling around with programmatic creation instead of convenient design.
If this request does not change again, then there is no point.

Buns appear only if we need to modify this request further depending on the settings.
Let's look at a few examples of modification of our package of 3 requests:

Example 1.

Let's say we have the variables "Organization" and "Warehouse". And if they are filled, we need to add conditions for these variables to the data selection.
When working with text, we will have to wedge ourselves into the request text and write difficult conditions like “If the scalad is full, then add a condition.” what does it have to do with breaking it up in several places and combining the options: both the warehouse and the organization are filled, only the warehouse is filled, only the organization is filled, nothing is filled.
After such a modification, it will be impossible to open the request by the designer, and its further modification will be very difficult.

When working with data programmatically, we simply add commands:

If ValueFilled(Organization) ThenPurchaseSelectionOperator.Selection.Add("Purchases.Organization = &Organization"); OperatorSelectionSales.Selection.Add("Sales.Organization = &Organization"); endIf; If ValueFilled(Warehouse) ThenPurchaseSelectionOperator.Selection.Add("Purchases.Warehouse = &Warehouse"); OperatorSelectionSales.Selection.Add("Sales.Warehouse = &Warehouse"); endIf;

Example 2.

We need to select products for which the last price is above 1000 rubles.
Those. necessary
1. Add to the request text a selection in the temporary table from the price register of the item for which prices are above 1000 rubles.
2. When selecting data, add conditions for this temporary table to both data selection queries (purchases and sales).
You can figure out how to insert yourself into a text request to perform these actions.
Programmatically, we simply add lines of code:

//Add a temporary table QueryFromPriceRegister = QueryScheme.QueryPackage.Add(); //Set up the temporary table RequestFromPriceRegister.TableForRoom = "VT_PricesItems"; OperatorSelect = RequestFromPriceRegister.Operators; Source = OperatorSelect.Sources.Add("Information Register.Item Prices.Last Slice","Last Item Prices"); OperatorSelect.SelectableFields.Add("Item PricesSliceLast.Item"); OperatorSelect.Selection.Add("ItemPricesSliceLast.Price > &Price"); //Move the new plate before the data selection requests QueryScheme.QueryBatch.Shift(RequestSchema.QueryBatch.Index(QueryFromPriceRegister),0); //Add conditions to the initial queries Procurement Selection Operator.Selection.Add("Purchases.Nomenclature IN (SELECT VT_PricesNomenclature.Nomenclature FROM VT_PricesNomenclature AS VT_PricesNomenclature)"); OperatorSelectionSales.Selection.Add("Sales.Nomenclature IN (SELECT VT_PricesItems.Items FROM VT_PricesNomenclature AS VT_PricesItems)");

IMHO, even for these examples program work with a request is more convenient than working directly with text.
At the same time, let us take into account that these modification options are still quite simple.
With the complication of modification options, the increase in the number of requests in the package, the selection of various modifications depending on the conditions (for example, adding conditions either by price register, or by types of items, or by production plans), program work looks more and more convenient compared to working with text line.

You can practice with queries using processing. It is convenient for developing commands for modifying an existing query. It also contains several examples of request code modification and a graph of the request type diagram.

z.y. Everything written above is just my personal understanding of the topic. Perhaps I'm wrong somewhere. Comments and clarifications are welcome.

upd: Another small example using a query scheme. Formation of a query that searches for duplicate values ​​of predefined data. Without accessing metadata, it generates one general request to all directories, charts of accounts, PVC, PVR as a whole according to the configuration. Used in processing

RequestScheme = New RequestScheme; CollectionOperators = RequestSchema.RequestBatch.Operators; For each Table Group From Query Schema. Query Package. Available Tables Cycle If Table Group. View = "Directories" OR Table Group. Presentation = "Charts of Accounts" OR Table Group. View = "Calculation Type Plans" OR Table Group. View = "Characteristic Type Plans" Then For each Table From Table Group. Composition Cycle For each TableField From Table.Fields Cycle If TableField.Name = "PredefinedDataName" Then NewOperator = CollectionOperators.Add(); NewSource = NewOperator.Sources.Add(Table,"DirectoryName"); NewOperator.SelectableFields.Add(""""+Table.Name+""""); NewOperator.SelectableFields.Add("QUANTITY(DIFFERENT DirectoryName.PredefinedDataName)"); NewOperator.Grouping.Add("DirectoryName.Name of Predefined Data"); NewOperator.Selection.Add("DirectoryName.Predefined"); NewOperator.Selection.Add("QUANTITY(DIFFERENT DirectoryName.Link) > 1"); Continue; endIf; EndCycle; EndCycle; endIf; EndCycle;

Topic 2.3. Presentation software and office programming basics

Topic 2.4. Database management systems and expert systems

2.4.11. Training database with main push-button form"Training_students" - Download

DBMS and expert systems

2.4. Database management systems and expert systems

2.4.4. Creation (formation) of requests

Query is a means of choice necessary information from the database. A question generated in relation to a database is a query. Two types of queries are used: by example (QBE - Query by example) and structured language queries (SQL – Structured Query Language).

QBE - sample query– a tool for finding the necessary information in a database. It is generated not in a special language, but by filling out a request form in the Query Builder window.

SQL queries– these are queries that are compiled (by programmers) from a sequence of SQL instructions. These instructions specify what must be done with the input data set to generate the output set. All Access queries are based on SQL based– queries, to view them, you need to run the View/SQL command in the active query design window.

There are several types of queries: select, update, add, delete, cross-query, create tables. The most common is a sample request. Select queries are used to select required by the user information contained in the tables. They are created only for related tables.

2.4.4.1. Create a select query using the Wizard

When creating a query, you need to define:

• fields in the database that will be used to search for information;
• subject of search in the database;
• list of fields as a result of executing the request.

In the database window, select the Queries tab and double-click on the Create a query using the wizard icon, the Create simple queries window will appear.

Rice. 1.

In the wizard window, select the required table (source table) from the Tables and queries option and select data fields. If the query is formed on the basis of several tables, you must repeat the steps for each source table.

Then in the Wizard window you need to select a detailed or summary report and click on the Next button. After this, you need to specify a name for the query and select one of the options for further action: Open query to view data or Change query layout and click Finish. As a result, you will receive a ready-made query.

2.4.4.2. Create a select query using the Designer

Using the constructor, you can create the following types of queries:

1. Simple.
2. By condition.
3. Parametric.
4. Final.
5. With calculated fields.

To call the Query Builder, you need to go to the database window. In the database window, select the Queries tab and double-click on the Create a query icon in design mode. will appear active window Adding a table while the Query: Select Query window is inactive.

In the Add Table window, select a source table or several tables from the presented list of tables on the basis of which data will be selected, and click the Add button. After this, close the Add Table window, the “Query: Select Query” window will become active.

The Designer window consists of two parts – upper and lower. At the top of the window there is a query data diagram, which contains a list of source tables and reflects the relationship between them.

At the bottom of the window there is a QBE (Query by Example) query construction form, in which each line performs a specific function:

1. Field – indicates the names of the fields that participate in the request.
2. Table name – the name of the table from which this field was selected.
3. Sorting – indicates the sorting type.
4. Display on screen – sets the checkbox to view the field on screen.
5. Selection conditions - search criteria are set.
6. Or – additional selection criteria are specified.

Rice. 2.

Sample request

In the “Query: sample request” window, use the tools to create a query:

1. Select the table - the source from which records are selected.
2. Move field names from the source to the Request Form. For example, from the Student Groups table, drag the Title field to the first field of the Request Form, from the Students table, drag the Last Name field to the second field of the Request Form, and from the Performance table, drag the Grade field to the third field, and from the Disciplines table, drag the Title field to the fourth field of the Request Form.
3. Set the sorting principle. Move the mouse cursor to the Sorting line for any field, a button will appear to open a list of sorting modes: ascending and descending. For example, set the sorting mode in the Last Name field to ascending.
4. In the display line, the checkbox for viewing the information found in the field is automatically selected.
5. In the selection line “Conditions” and the “Or” line, you must enter limited search conditions - search criteria. For example, in the Score field, enter “excellent/A”, i.e. display all names of students who received excellent/A grades.
6. After completing the query generation, close the Select Request window. The Save dialog box will open - answer Yes (enter the name of the created query, for example, Sample Query in Design mode) and click OK and return to the database window.

Rice. 3.

To open a query from the database window, you need to select the query name and click the Open button; a selection query window with the required name will appear on the screen.

Rice. 4.

To make changes to a query, you need to select it by clicking in the database window, click on the Constructor button, and make changes. Save the request and run it again.

Parametric queries

Queries that are variations of a basic query and differ slightly from each other are called parametric. A parametric request specifies a criterion that can be changed according to the user's request.

Sequence of creating a parametric query:

1. Create a query in Design view or open an existing query in Design view, such as "Sample Query in Design View."
2. In the Request Form, in the Selection Conditions line, enter the selection condition in the form of an invitation in square brackets, for example [Enter last name].
3. Close the Request to select window, answer the question about saving the change - Yes. Return to the database window, where the created query will be highlighted.
4. Execute the query by clicking on the button: Open. In the “Enter parameter value” dialog box that appears on the screen, you must enter, for example, the name of the student whose academic performance information you want to obtain, and click on the OK button.

Every day we have to solve many problems. To successfully solve them, answers to questions are required: from “what is the weather like today” to “the exchange rate of the Polish zloty to the Russian ruble.” The Internet and search engines allow you to find answers to almost all questions, but with one condition: you need to ask them correctly.

Instructions

Search engines (Google, Yandex, Rambler, Mail, Yahoo, etc.) are designed to provide users necessary information. They do this based on a search query. Search queries can be of three types:

Informational. The user is looking for accurate information (no matter what site it is on). For example: "Anthem of Russia".

Navigational. The user is looking for the address of a site that may contain the information he is interested in. For example: "Website of M.V. Lomonosov Moscow State University."

Transactional. The user is ready to take some action and needs information about it. For example: “formatting a disk.” So, the first thing you need to do when forming a search query is to determine what exactly you are looking for.

A search engine is a huge database, all the information in which is “sorted” into sections - keywords. After you set a search query, a keyword search occurs and the search result is returned. A common mistake of novice users is that they perceive the search engine as an interlocutor who knows everything, and set a search query, for example, like this: “Can you tell me, "How do you remove a stain from a brand new blouse? It's silk." Naturally, the benefit from such a request will be minimal. The following request will be much more effective: “white silk blouse to remove a stain.” So, the second thing to do is to formulate the request correctly. It should be as simple as possible and contain keywords.

After the machine processes your request, it will display search results. As a rule, all the most relevant results are found on the first two or three pages. This is followed by search results that only partially satisfy the request. However, there are situations when very rare information is needed - then you have to go through several combinations of keywords in the search query and very carefully filter the data obtained.

Also, sometimes you have to look for rare information that is “contained” in popular keywords. For example, if you search for “Marx photo publisher,” then search system will find many photographs of Karl Marx, Karl Marx's publisher, but in order to find a photograph of A.F. Marx, a book publisher, will have to work hard. So, the third thing you need to do in order for the search to be successful is to sort the information correctly.