Formation of requests. Requests to the tax authority How requests are made

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

Let's try to figure out what a request is in general. In what format is it presented in 1C and on the IS server.

Simplistically, we can distinguish the following levels of work with a request:
1. Request in the DBMS language. At this level, a query is a text string in the syntax of a particular DBMS.
For example "SELECT field1 FROM table1 WHERE table1.field2 > 100".
2. The driver of 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 passes it to the DBMS.
At this level, 1C works with external data sources.
3. Universal programming interface. This is a universal component that allows the program to access data in various IBs. It can also be called a driver manager. Drivers of specific DBMS used on the computer are connected to it in the form of a DSN. Depending on the system used, there may be an ODBC component, OLE DB, JDBC, ADO.NET, or any other standard. 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 work of the application server with the request is performed.
As far as I understand, the C language platform describes the query object for which the SQL text generation methods are written. Platform objects work with this query object (query constructor, builder, etc.)
5. Request in 1C language. At this level, the query is a text written in a pseudo-SQL language of the form "CHOOSE Catalog.DirectoryName.FieldName FROM Catalog.DirectoryName"

As far as I understand, with the introduction of the Request Schema object, nothing new has been invented in the platform.
Previously, we could only work with the text representation of the request (level 5). This is the most simple and understandable way, although not always convenient.
Now we have been given access to an object (level 4), which previously only platform mechanisms worked with.
If you analyze the Query Schema object, then its properties intersect very clearly with the query builder.
Each element and property can be found visually in the Query Builder.
Each click in the constructor can be matched with a similar command.
For instance:
Bookmark "Request Package" = Request Scheme. Request Package;
"Tables and fields" tab = RequestScheme.QueryPackage.AvailableTables;
Click on the table "Catalogs" - "Nomenclature" = Query Scheme.Package of Requests.Operators.Sources.Add("Catalog.Nomenclature");

Thus, we can programmatically form almost any request that we can make with a constructor.
Of course, programmatic creation is much more complicated and time consuming than just working with the constructor or even writing the query text manually.
For static queries whose text does not change, there is no point in using a schema.
Programmatic work requires a deeper understanding of query work.
It is convenient if we need to change the request text dynamically depending on the system parameters, the user's choice, 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 the following code:

RequestSchema = New RequestSchema; RequestScheme.SetQueryText(Request.Text); //Here are our request modification commands Request.Text = RequestScheme.GetQueryText();

Request 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 in this set of objects.

To understand the general structure of the query scheme, we open the necessary section of the joint venture and drive all the links between objects into the graph plotter.
It turns out such a graph.
I can’t say that it is very clear, but it shows the general idea. It is convenient to peep at the request analysis.
The graph indicates:
Yellow - query schema entities;
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, but it also allows you to create a query text from scratch.

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

SELECT ALLOWED Items.Reference AS Nomenclature, Purchases.Period AS Period, ISNULL(Purchases.SumTurnover, 0) AS SumPurchases, 0 AS SumSales PUT Table Turnovers FROM Handbook.Nomenclature AS Items LEFT JOIN Accumulation Register.Purchases.Turnovers(&Start, &End, Month) AS Purchasing BY Purchasing.Nomenclature = Items.Reference WHERE NOT Items.ThisGroup COMBINE ALL SELECT DIFFERENT FIRST 100 Items.Reference, Sales.Period, 0, ISNULL(Sales.AmountTurnover, 0) FROM Catalog.Nomenclature AS Items LEFT JOIN Accumulation Register.Sales .Volumes(&Start, &End, Month) AS Sales BY Sales.Nomenclature = Items.Reference WHERE NOT Items.ThisGroup INDEX BY Item, Period; /////////////////////////////////////////////////// ////////////////////////////// SELECT Amount of Purchases, SUM (Table Turnovers. Amount of Sales) AS Sum of Sales FROM Table Turnovers AS Table Turnovers GROUP BY Table Turnovers. Nomenclature, Table Turnovers. ) ON GENERAL, Nomenclature ONLY HIERARCHY; /////////////////////////////////////////////////// /////////////////////////////// DESTROY TableTurns

Let's analyze the batch of requests.

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

Let's start programmatically creating a batch of requests.

To develop the code, we will use the query tree. Comfortable for him define data paths. It is advisable to periodically check the resulting request text after code modifications.

First, let's create a RequestScheme object.

RequestSchema = New RequestSchema;

The generated query schema already contains one batch and one select statement.
For convenience, we will save them in separate variables.

RequestFromIBSelection = RequestScheme.PacketRequests; PurchasingChoiceOperator = RequestChoiceFromIB.Operators;

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

SourceNomenclature = PurchasingChoice Operator.Sources.Add("Directory.Nomenclature","Goods"); Purchase Source = Purchase Selection Operator. Sources. Add ("Accumulation Register. Purchases. Turnovers", "Purchases");

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

We need a reverse connection. Change the connection type.

Purchasing.Connection.ConnectionType = RequestSchemaConnectionType.RightOuter;

Table options:
For each table added as a source, we can set additional parameters.
The options 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.
Table "Reference. Nomenclature" is simple table directory. She has no options.
The table "Accumulation Register. Purchases. Turnovers" is a table of turnovers of the accumulation register.
According to the joint venture, it has 4 parameters: the beginning of the period, the end of the period, the frequency, the condition.
We need to set the first 3 parameters:

PurchaseSource.Source.Parameters.Expression = New QuerySchemaExpression("&Start" ); PurchaseSource.Source.Parameters.Expression = New QuerySchemaExpression("&End") ; PurchaseSource.Source.Parameters.Expression = New QuerySchemaExpression("Month") ;

Specify the columns to be selected from the tables listed.
To do this, we add fields to the collection of selectable fields of the select statement.

PurchasingChoice Operator.SelectableFields.Add("Goods.Link"); PurchasingSelection Operator.SelectableFields.Add("Purchases.Period"); PurchaseSelection Operator.SelectableFields.Add("ISNULL(Purchases.AmountTurnover, 0)"); PurchasingChoice Operator.SelectableFields.Add("0");

Specify at the request level as a whole aliases for selectable columns:
Please note that we select the table columns for each subquery of the join, we assign names to the columns as a whole for the "Columns" collection at the package query level.

QuerySelectionFrom VT.Columns.Alias = "Nomenclature"; QuerySelectFrom BT.Columns.Alias = "Period"; QuerySelectionFrom VT.Columns.Alias = "Purchase Amount"; QuerySelectionFrom VT.Columns.Alias = "Sales Amount";

Add a condition for selecting data

SalesChoice Operator.Selection.Add("NOT Goods.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 (an element of the "Nomenclature" reference book, a date and two numeric fields).
When combining columns from two operators for selecting a column from the directory and the date, the system will match itself.
There are two numeric columns. The schema may itself map them incorrectly to the columns in the first query.
We indicate which column of the result table corresponds to which expression.

QuerySelectFrom.Columns.Fields.Set(1,PurchasingExpression); QuerySelectFrom.Columns.Fields.Set(1,SaleExpression);

We repeat everything in the same way for the second request of the package.

Of the differences, it uses the selection by total values of the type "HAVING".
Such a condition is added similarly to the usual selection for detailed records.
The schema will determine which condition section to put ours in depending on the use of the grouping functions.

OperatorSelect.Selection.Add("SUM(Table Turnovers.SumPurchases) > 0");

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

DestructionRequestVT = QuerySchema.RequestPacket.Add(Type("DestructionRequestRequestSchemaTable")); Destruction RequestBT.TableName = "TableTurnovers";

We also exhibit additional properties requests:

RequestChoiceFromIB.TableForLocation = "TableTurnovers"; QuerySelectionFromIB.SelectAllowed = True;

final full version request generation:

//Create the request schema RequestSchema = New RequestSchema; RequestFromIBSelection = RequestScheme.PacketRequests; //Setting request properties RequestChoiceFromIB.TableForPlace = "TableTurnovers"; QuerySelectionFromIB.SelectAllowed = True; //Add selection operators for the first batch requestPurchaseChoiceOperator =ChoiceFromIB.Operators; SourceNomenclature = PurchasingChoice Operator.Sources.Add("Directory.Nomenclature","Goods"); //Add a selection request from the purchase registerPurchaseSource = PurchaseSelection Operator.Sources.Add("Accumulation Register.Purchases.Turnovers","Purchases"); PurchaseSource.Source.Parameters.Expression = New QuerySchemaExpression("&Start" ); PurchaseSource.Source.Parameters.Expression = New QuerySchemaExpression("&End") ; PurchaseSource.Source.Parameters.Expression = New QuerySchemaExpression("Month") ; //Change the connection typePurchaseSource.Connection.ConnectionType = ConnectionTypeRequestSchema.RightExternal; //Specify the fields to be selectedPurchaseChoice Operator.SelectableFields.Add("Goods.Link"); PurchasingSelection Operator.SelectableFields.Add("Purchases.Period"); PurchaseSelection Operator.SelectableFields.Add("ISNULL(Purchases.AmountTurnover, 0)"); PurchasingChoice Operator.SelectableFields.Add("0"); //Specify aliases for the selected fieldsChoiceFromIB.Columns.Alias = "Nomenclature"; QuerySelectFromIB.Columns.Alias = "Period"; QueryFrom IB.Columns.Alias = "Purchase Amount"; QueryFrom IB.Columns.Alias = "Sales Amount"; //Add selection PurchasingChoice Operator.Filter.Add("NOT Goods.This isGroup"); /////////////////////// //Selecting sales dataSalesChoiceOperator = QueryChoiceFromIB.Operators.Add(); SourceNomenclature = OperatorChoiceSales.Sources.Add("Directory.Nomenclature","Goods"); SalesChoice Operator.SelectableFields.Add("Products.Link"); //Add the PH source and set the parameters SourceSales =SalesSelect Operator.Sources.Add("Accumulation Register.Sales.Turnovers","Sales"); SaleSource.Source.Parameters.Expression = New QuerySchemaExpression("&Start" ); SaleSource.Source.Parameters.Expression = New QuerySchemaExpression("&End") ; SaleSource.Source.Parameters.Expression = New QuerySchemaExpression("Month") ; SaleSource.Connection.ConnectionType = QuerySchemaConnectionType.RightOuter; //Specify the fields to be selected and set the numeric fields to match the fields of the first request SalesChoiceOperator. SelectableFields.Add("Sales.Period"); Purchasing Expression = SalesChoiceOperator.SelectableFields.Add("0"); SalesExpression = SalesSelect Operator.SelectableFields.Add("ISNULL(Sales.SumTurnover, 0)"); QuerySelectFrom.Columns.Fields.Set(1,PurchasingExpression); QuerySelectFrom.Columns.Fields.Set(1,SaleExpression); //Add selection OperatorChoiceSales.Filter.Add("NOT Goods.This is a Group"); //Index dataChoiceFromIB.Index.Add(ChoiceFromIB.Columns); QueryFrom IB.Index.Add(QueryChoiceFrom IB.Columns); //Set data selection parameters SalesChoiceOperator.SelectDifferent = True; SalesChoice Operator.Number of Received Records = 100; //////////////// //Second request packetChoiceFromVT Request = RequestScheme.RequestPacket.Add(); OperatorSelect = RequestSelectionFromVT.Operators; //Set the temporary table formed in the previous query as a source Source = OperatorSelect.Sources.Add("Table Turnovers","Table Turnovers"); OperatorSelect.SelectableFields.Add("Table Turnovers.Nomenclature"); OperatorSelect.SelectableFields.Add("Table Turnovers.Period"); OperatorSelect.SelectableFields.Add("SUM(Table Turnovers.AmountPurchases)"); OperatorSelect.SelectableFields.Add("SUM(Table Turnovers.SumSales)"); //Selection condition OperatorSelect.Selection.Add("SUM(Table Turnovers.SumPurchases) > 0"); //Set column aliases QueryFromVT.Columns.Alias = "Nomenclature"; QuerySelectFrom BT.Columns.Alias = "Period"; QuerySelectionFrom VT.Columns.Alias = "Purchase Amount"; QuerySelectionFrom VT.Columns.Alias = "Sales Amount"; //Sorting order of data SelectionFromVT.Order.Add(SelectionFromVT.Operators.Sources.Source.AvailableFields.Fields); QueryChoiceFromVT.Order.Add(RequestChoiceFromVT.Columns); //Query totals TotalNomenclature = QuerySelectionFromVT.CheckpointsTotals.Add(QuerySelectionFromVT.Columns); TotalNomenclature.CheckpointType = QuerySchemaCheckpointType.HierarchyOnly; QuerySelectionFrom VT.CommonTotals = true; Query SelectFrom VT.Total Expression.Add(Query SelectFrom VT.Columns); Query SelectFrom VT.Total Expression.Add(Query SelectFrom VT.Columns); //////////////// //Last batch request - deleting a temporary tableDestroyRequestVT = QuerySchema.QueryPacket.Add(Type("RequestDestroyRequestSchemaTable")); Destruction RequestBT.TableName = "TableTurnovers";

After executing this set of commands, we get the same request that we previously received by the constructor.

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

Bonuses appear only if we need to modify this request further depending on the settings.
Consider a few examples of modifying our package of 3 requests:

Example 1

Let's say we have "Organization" and "Warehouse" variables. And if they are filled, we need to add conditions on these variables to the data selection.
When working with text, we will have to wedge into the query text and write complex conditions like "If the scaled is full, then add a condition." at what to break in several places and combine 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 query with the constructor, and its further modification is very difficult.

When working with data programmatically, we simply add commands:

If ValueFilled(Organization) Then PurchasingSelection Operator.Selection.Add("Purchasing.Organization = &Organization"); SalesSelect Operator.Selection.Add("Sales.Organization = &Organization"); EndIf; If ValueFilled(Warehouse) Then PurchasingSelection Operator.Selection.Add("Purchases.Warehouse = &Warehouse"); OperatorChoiceSales.Selection.Add("Sales.Warehouse = &Warehouse"); EndIf;

Example 2

We need to select goods for which the last price is above 1000 rubles.
Those. necessary
1. Add to the query text a choice in a 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).
How to wedge into a text query to perform these actions, you can imagine yourself.
Programmatically, we simply add lines of code:

//Add a temporary table RequestFromPriceRegister = RequestScheme.RequestPackage.Add(); //Set up a temporary table RequestFromPriceRegister.TableForLocation = "VT_NomenclaturePrices"; OperatorSelect = QueryFromPriceRegister.Operators; Source = OperatorSelect.Sources.Add("Information Register.Nomenclature Prices.SliceLast","Nomenclature PricesSliceLast"); OperatorSelect.SelectableFields.Add("Nomenclature PricesSliceLast.Nomenclature"); OperatorSelect.Selection.Add("Item PricesLast Slice.Price > &Price"); //Move a new table before the data selection requests RequestSchema.QueryPack.Shift(QueryScheme.QueryPack.Index(RequestFromPriceRegister),0); //Add conditions to the original queries PurchasingSelection Operator.Selection.Add("Purchases.Nomenclature IN (SELECT BT_NomenclaturePrices.Nomenclature FROM BT_NomenclaturePrice AS BT_NomenclaturePrice)"); OperatorSelectSales.Selection.Add("Sales.Nomenclature IN (SELECT VT_NomenclaturePrices.Nomenclature FROM VT_NomenclaturePrice AS BT_NomenclaturePrice)");

IMHO, even for these examples program work with a query is more convenient than direct work with text.
At the same time, we take into account that these modification options are still quite simple.
With the complication of modification options, an increase in the number of requests in a package, the choice of various modifications depending on the conditions (for example, adding conditions either by price register, or by type of item, or by production plans), program work looks more and more convenient compared to working with text string.

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

ps 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 of using a query schema. Formation of a query that searches for double values of predefined data. Without accessing metadata, it generates one general request to all directories, charts of accounts, PVC, PVR as a whole by configuration. Used in processing

RequestSchema = New RequestSchema; CollectionOperators = QueryScheme.RequestPackage.Operators; For eachTableGroupFromQuerySchema.QueryPack.AvailableTables Cycle IfTableGroup.View = "References" OR TableGroup.View = "Charts of Accounts" OR TableGroup.View = "PlansofCalculationTypes" ORTableGroup.View = "PlansofCharacteristicTypes" Then For each TableFromTableGroup.Composition Loop For Each TableField From Table.Fields Loop IfTableField.Name = "PredefinedDataName" Then NewOperator = CollectionOperators.Add(); NewSource = NewOperator.Sources.Add(Table,"CatalogName"); NewOperator.SelectableFields.Add(""""+Table.Name+""""); NewOperator.SelectableFields.Add("NUMBER(DIFFERENT DirectoryName.PredefinedDataName)"); NewOperator.Grouping.Add("CatalogName.Name of PredefinedData"); NewOperator.Selection.Add("CatalogName.Predefined"); NewOperator.Selection.Add("NUMBER(DIFFERENT DirectoryName.Reference) > 1"); Continue; EndIf; EndCycle; EndCycle; EndIf; EndCycle;

How to correctly formulate a client request for placement? This is often asked by clients. Some of them even ask for a preliminary consultation: "Help me figure it out and formulate a request, I'm confused."

Let's figure it out.

There have historically been several approaches to the correct constellation query. This is due to how constellations as a method have developed, along with other areas of psychotherapy and traditions of spiritual practice. When the constellations had just entered practice (in the late 70s - early 80s of the last century), the so-called methods were at the peak of popularity. "short-term solution-oriented therapy". In contrast to long psychotherapeutic work (including psychoanalysis), therapists began to develop methods for quick, concrete help to the client. Such assistance required a clear request. The criteria for achieving this request must be clearly understood by both the client and the therapist. "I want to get a bigger salary, but if I go to the boss to ask for a raise, I get cold, I sweat and my legs buckle". Task: go to the boss for a promotion, ask him and get what you want. We will work on this task, for example, 10 sessions. At the end, the client will be asked how much more confident he feels (at least). Successful Therapy: Promotion received.

Since constellations are, of course, short-term work, they began to “by analogy” refer to the group of short-term therapy methods and apply to it the same requirements for demand and effectiveness that are accepted in these methods. This was also facilitated by the interest in the constellations of well-known specialists in short-term therapy (Matthias Varga von Kiebed and Inse Sparrer). And, implicitly, I think Hellinger's enthusiasm and his tendency to talk about constellations in terms like "solution found" also contributed to this. In his early writings, he often spoke of "eliminating symptoms," "solving the problem of alcoholism," and so on.

Until now, you can meet the "old" constellators who ask the client for a specific wording desired result. I even met colleagues (for example, Thomas Hafer) who write down the client's request on a piece of paper and at the end of the arrangement check that this particular request has been fulfilled, i.e. the state of the substitutes corresponds to the desired state of the client.

Along with these historical reasons, there is a not always conscious desire of the client to formulate something specific, simply because the constellation will now be one, it is expensive, and perhaps it has been waited for a long time and "we must make good use of our chance."

However, there is another, almost opposite approach to the request. It consists in the fact that request is not needed at all.. There are also historical reasons for this, primarily related to Bert Hellinger's style of work.

In early constellations, Hellinger always placed the current and/or parent family of the client. Hellinger asked what the client's problem was, but no matter what he said, the constellation always came down to working with family members. A fair question arises: why ask? Later, when Hellinger formulated that the work is with the system as a whole (and not with the client as such), the request began to lose its meaning of "find out what the client wants" and acquired a different meaning. The request is needed in order to establish in a dialogue with the constellator contact. Those who are familiar with Hellinger's work can certainly give many examples of how Hellinger refused to do the placement after barely listening to two or three phrases from the client. "You don't respect me, I can't work with you."

Often, Hellinger worked without exchanging a single word at all regarding the client's request.

How to understand all this diversity for an ordinary client? The main thing I would like to convey to [my] clients is that any confusion (as well as any clarity) regarding the request is completely normal. Your inner process is expressed in a way that is familiar and natural to you. And on the method side, there are no restrictions on how the request can be expressed. If you can "just" sit next to the constellator and burst into tears, that's perfectly enough. If you thought about a clear wording for a week and outlined it to me, this is also normal. Even if there are fifteen points in this wording and everything is confusing. Even if this wording fell apart exactly at the moment when you were on the chair next to me.

Following Hellinger, I believe that the main thing to start work is respect and contact between the client and the constellator. The way in which this is achieved is not important. Technically, I can work both on a clear formulation and without a request at all. Usually in a long group I have a lot of both jobs.

Like every constellator, I have my own preferences, states in which I feel more comfortable. When working with a request, I rather tend to wording of low clarity, which the client is ready to explore in the arrangement and change, if necessary, right in the process. For example, for me the wording "I want to marry a normal man" harder to work than "I am very lonely and would like to understand how I can gain the ability to enter into harmonious relationships, or what prevents this". The first formulation implicitly puts me in the role of "the one who will bring results", and the second turns the client to where she hurts... At the same time, I respect the fact that the degree of contact with pain can be such that it is visible only through the absence of the desired result, and more so far in no way.

In my observation and experience, the field responds best to requests that address the exploration and transformation of pain. I feel that this is the basic movement that exists in the field - to approach the painful, compressed, rejected state, to touch it and thereby make it visible, accepted, passable. In the broad sense of the word, this is the implementation of the First Order (the law formulated by Hellinger) of systems: no one is excluded. If wider, nothing is excluded. If pain is excluded, it works. If the pain is gone, it loses its influence and strength.

Based on this understanding, it does not matter at all how the client and I found "where it hurts." Was it wording and was it clear. Has this wording changed five times in a group or is it still solid? Or we just sat in silence. Or the client was crying, and I was sitting next to her. I am always looking for non-verbal contact (more precisely, it is always there, even if we are not aware of this), I tune in to the client and through my body I can feel what is happening to him. Sometimes this sensitivity is very clear, sometimes it falters, but in any case it is there and brings some degree of contact, in addition to or in addition to any formulations. Additional contact and clarity will then be brought by the deputies when we begin the set-up.

You can help good contact answering your own question "where it hurts" or "what's wrong". The answer might be: "here"(in the heart) or "Mother"(relationship with mother causes suffering) or Husband doesn't want a baby but I do or "my co-worker is stalking me" or any other clear or unclear wording. Please note that even in the complete absence of verbal formulation, you "somehow" know that something is wrong, otherwise you would not have come to the group. This knowledge usually has its place in your body, you can just point to this place - and it will be a very good, very clear "request statement"!

Thus, for me, the answer to the question "what is a good constellation query" sounds like this: it can be any wording or no wording if, as a result of our interaction, the client and I both feel contact and trust in each other in order to start work.

Every day we have to deal with many problems. For their successful solution, answers to questions are required: starting from "what is the weather like today" and ending with "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: they must be asked correctly.

Instruction

Search engines (Google, Yandex, Rambler, Mail, Yahoo, etc.) are designed to provide users with the 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 the site, which may contain information of interest to him. For example: "Website of Moscow State University named after M.V. Lomonosov".

Transactional. The user is ready to take some action and needs information about it. For example: "formatting a disk". So, the first thing 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 "decomposed" into shelves - keywords. After you ask search query, keywords are searched and the search result is returned. A common mistake novice users make 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 to remove a stain from a brand new blouse? She silk". Naturally, the benefits of such a request will be minimal. A much more effective request would be: "white silk blouse to remove the 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 return the search results. As a rule, all the most relevant results are on the first two or three pages. This is followed by search results that only partially satisfy the query. However, there are situations when very rare information is needed - then you have to sort through several combinations of keywords in the search query and filter the received data very carefully.

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

Topic 2.3. Presentation software and basic office programming

Topic 2.4. Database management systems and expert systems

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

DBMS and expert systems

2.4. Database management systems and expert systems

2.4.4. Creation (formation) of requests

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

QBE - sample query- a tool for finding the necessary information in the database. It is formed 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 to do with the input dataset to generate the output dataset. Access builds all queries on the basis of SQL queries; to view them, you need to execute 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 the select query. Select queries are used for selection the user needs information contained in the tables. They are created only for related tables.

2.4.4.1. Creating a Select Query Using the Wizard

When creating a query, you must define:

fields in the database that will be searched for information;
subject of search in the database;
list of fields as a result of query execution.

In the database window, select the Queries tab and double-click the Create a query using the wizard icon, the Create Simple Queries window appears.

Rice. one.

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, it is necessary to repeat the steps for each source table.

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

2.4.4.2. Creating a select query using the Designer

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

Simple.
By condition.
Parametric.
Final.
With calculated fields.

To call the Query Builder, you must go to the database window. In the database window, select the Queries tab and double-click the Create Query in Design View icon. The active Add Table window appears in front of the inactive Query: Select Query window.

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

The Constructor window consists of two parts - upper and lower. The upper part of the window contains the query data schema, which contains a list of source tables and reflects the relationship between them.

At the bottom of the window is the QBE (Query by Example) Query Building Form, in which each line performs a specific function:

Field - specifies the names of the fields that are involved in the query.
Table Name - The name of the table from which this field is selected.
Sort - Specifies the sort type.
Display to screen - checkbox to view the field on the screen.
Selection conditions - search criteria are set.
Or – additional selection criteria are set.

Rice. 2.

Sample Request

In the "Query: query for selection" window, using the tools, we form a query:

Select a table - the source from which the selection of records is made.
Move field names from source to Request Form. For example, from the Student Groups table, drag the Name field to the first field of the Request Form, from the Students table, drag the Surname field to the second field of the Request Form, and from the Progress table, drag the Grade field to the third field, and from the Courses table, drag the Name field to the fourth field of the Request Form.
Set the sort principle. Move the mouse cursor to the Sorting line for any field, a button will open the list of sorting modes: ascending and descending. For example, set the sorting mode in the Last name field to ascending.
In the display line, the checkbox for viewing the found information in the field is automatically set.
In the "Conditions" line of the selection and the "Or" line, you must enter the limited search conditions - search criteria. For example, in the Grade field, enter - "excellent / A", i.e. display all the names of students who received exc/A grades.
When the request is completed, close the Select Request window. The dialog box Save - answer Yes (type the name of the generated 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, highlight the query name and click the Open button, a select query window with the desired name will appear on the screen.

Rice. 4.

To make changes to a query, select it with a mouse click in the database window, click the Constructor button, and make changes. Save the request, repeat its execution.

Parametric queries

Queries that are variants of the base query and differ slightly from each other are called parametric queries. In a parametric query, a criterion is specified that can be changed by the user's order.

The sequence of creating a parametric query:

Create a query in Design view, or open an existing query in Design view, such as Sample Query in Design View.
In the Request form, in the Selection conditions line, enter the selection condition in the form of an invitation to square brackets, such as [Enter last name].
Close the Select Request window, answer the question about saving the change - Yes. Return to the database window, where the created query will be highlighted.
Execute 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 progress information you want to receive, click on the OK button.

Queries are one of the types of documents used in the Access DBMS, which are designed to process data stored in tables

Queries can be created in constructor and with the help masters. The constructor allows you to create any type of query yourself, but this mode is recommended for users who already have some experience creating queries.

Query Wizard collects the information necessary to generate a request in several steps, and then automatically composes it depending on the user's answers to the questions posed.

Using the wizards in Access, you can create the following types of queries:

§ Simple request.

§ Cross query.

§ Duplicate entries.

§ Recordings without subordinates.

To create any of them, select the Queries object in the database window and click the Create button. The New Query window will open, the view of which is shown in Fig. one.

Figure 1 DB window State and window for selecting query types

Simple Request allows you to create with Masters sample request data from certain fields of tables or queries, it is most convenient for novice users. When it is selected, the Wizard is launched, in the first window of which (Fig. 2) you need to select a table in the Tables and queries list, for example, Staff of teachers, select from the list of its available fields those that should be present in the query and translate each by pressing the [> ]. Similarly, fields from other tables of the same database are added to the query.

Note. The query can be made only by tables or only by database queries. Combining fields from a table and a query in a query is not allowed.

Figure 2 Selecting fields for a query.

Cross query looks like a table , in which up to three fields (columns) of the source table are displayed, the cells of one of the remaining ones are converted into new columns, and at their intersection one of the values specified by the user is displayed - Dispersion, Minimum, Maximum, Average, Deviation, Number, First, Last, etc. For example, the query in Fig. 4 is a cross-query of table 1 in fig. 3, in which the distance values became the names of the columns:

Option Duplicate entries creates a query to search for duplicate records (rows) in one table or query, for the table in fig. 3 with the given fields Distance and Fare, it looks like (Fig. 5)

MS Access allows you to create such a query for only one table or query (not for several database tables), and you need to specify in it

Figure 3 Table 1 for creating a cross query

Figure 4 Cross query tab. one

only those fields in which there is a complete simultaneous match of data from records (for example, the Transport field cannot be included in this request). Additionally, for recognition, you can enable a non-repeating field (City).

Records without subordinates – a query to search for records that do not correspond to any record in the compared table. This query is used for multi-table databases.

Figure 6 Main table 1

Query without subordinates for table comparison fig. 3 and fig. 6 will display the mismatched line (Fig. 7):

These requests are the basis for creating more complex queries, for which Design mode is applied.