Development of inquiries to a database

Occurrence even very small table instantly leads to occurrence of the whole complex of the problems connected with necessity of processing of data containing in it. To the elementary problems of processing can be carried:

• Search of record on a condition (see Editing> To find function of the menu)
• Sorting of records in the demanded order (see function of the menu of Record Sorting)
• Reception of sample of records of the table, satisfying to the set condition, or as still speak, the task of the filter for the table (Record > the Filter).

The listed functions also are accessible from the contextual menu which is becoming more active after pressing of the right key of the mouse. The given interface is represented especially convenient at practical work with tables Access. However it is obviously not enough these opportunities for problems of data processing which arise in real economic appendices. For their decision in Access the toolkit of inquiries to a database serves developed. The concept of inquiry in Access is used in the plan. It should be treated as some command on a choice, viewing, change, creation or removal of data. Also it is necessary to note value of inquiries for the decision of problems of the analysis of data.

The most widespread and if so it is possible to be expressed, natural type of inquiries is the inquiry about sample. The given type, as a matter of fact, also is established(installed) by default for again created inquiry.

At work with system of data very often there is a problem(task) of connection of data from the various connected tables in one. So, within the limits of our example natural the problem of construction of the table containing the information under the maintenance of portfolios and having following structure is represented.

• The name of a paper
• The name of the agent
• Type of a paper
• The face-value of the package calculated as product of a nominal price of quantity of papers of the given kind which the current agent possesses

For its decision it is necessary to pass Inquiries of the main window of a database to section, to press the button To create and choose a mode the Designer. Process of creation of inquiry begins with a choice of tables (including Other inquiries) on the basis of which the inquiry is under construction. In the further the structure of this set can be changed. Our inquiry will be constructed on the basis of the given tables Portfolios, Agents and Papers. We shall notice, that at addition of tables to inquiry communications between them, set in the scheme(plan) are by default added also. During formation of inquiry it is possible to allocate a number of basic stages

• The description of structure of inquiry (that is the instruction of what information it should be deduced in columns of the table of inquiry)
• The task of the order in which data should be deduced at performance of inquiry
• The task of conditions of a conclusion of records in inquiry

On fig. the window of the designer of inquiry is shown.

Let's note, that columns of the table of inquiry contain both fields of tables, and the expressions constructed on the basis of fields. In particular, last column (to it name НоминСтоим is appropriated) contains expression [Face value] * [SumValue], thus records will be deduced(removed) sorted as papers.

By analogy to principles of the organization of the interface of work to tables of data, at designing inquiries also there is an opportunity of operative transition from a mode the Designer in the Mode of the table. At the first input in the Mode of the table there is an invitation to keep again created inquiry. In this case to it it is named. On fig. the window in which the records corresponding this inquiry are deduced is shown.

It is necessary to pay attention to exclusively important role of the mechanism of inquiries in the decision of a problem of maintenance of the minimal redundancy of the information kept in base. Really, with their help we can receive any quantity of the virtual tables representing in the most various kinds and cuts unique is real хранимую a data set.
Let's consider one more case of application of inquiries for the decision of problems of data processing. Enough (including ля appendices of financial and economic character) the problem of a grouping of data to this or that attribute is typical. For example, within the limits of the database constructed by us there can be a task in view of definition total (or an average) a supply and demand under the securities circulating in the market. To solve it it is possible, having constructed the inquiry containing group operations. For activization of an opportunity of their task in a window of the Designer of inquiries it is necessary to include function of the menu the Kind> Group operations.

On fig. the window of the designer during creation of the inquiry deducing the information on a total supply and demand on securities is shown. Operation of convolution of several records from the table of the Application in one результирующую the record which is carried out for each name of a paper, is defined by the command the Grouping located in line Group operation. For two subsequent columns of inquiry (SumSpros and SumPredl) operations of summation on group (Sum), located in the same line are certain, and in a line the Field there are derivative expressions, which sums we wish to receive in inquiry. According to earlier accepted agreements the volume of total demand is defined by set of all records on the given paper, having positive value in field SumZaw, and volume of the total offer - the records containing in the given floor negative size. Thus, for calculation СуммСпрос it is necessary просуммировать:

If[SumZaw]>=0; [PriceZaw]*[0бъем3аявки];0), and for calculation SumPredl - If[SumZaw]<=0;-1* [PriceZaw]*[SumZaw];0).

THE NOTE

The built in function lf (bArg; Arg1; Arg2) returns value of argument Arg1 if value of argument bArg which can contain only logic size, is true (bArg = TRUE), and value Агд2, if bArg = LIE.

Also it is necessary to pay attention of the reader to such important opportunities of the designer of inquiries, as

• The task of the parameters required at opening of inquiry
• The built in statistical functions accessible at the task of group operations. They do(make) inquiries by the powerful tool of the analysis хранимой information.

In end of the review of construction tools of inquiries in СУБД Access it is necessary to specify as well that besides the powerful and effective visual designer is built in it(her) as well a mode of direct input of the SQL-expressions defining(determining) inquiry. The given mode exists in parallel and the Kind> Mode SQL (and also from the pictogram the Kind on the panel of tools) is accessible from the menu. Having passed in it(him), in particular, it is possible to see(overlook) the SQL-expression corresponding(meeting) earlier constructed inquiry СводнСпросПредл. It looks(appears) so:

SELECT Бумаги.НаимБум,
Sum(IIf([ОбъемЗаявки]>=0,[Цена3аявки]*[0бъем3аявки],0)) AS СуммСпрос,
Sum (IIf ([ОбъемЗаявки]<=0,-[ЦенаЗаявки]*[0бъемЗаявки],0)) AS СуммПредл
FROM Бумаги INNER JOIN
(Агенты INNER JOIN Заявки ON Агенты.КодАг = Заявки.КодАг)
ON Бумаги.КодБум = Заявки.КодБум
GROUP BY Бумаги.НаимБум
ORDER BY Бумаги.НаимБум;

The user owning syntax of language SQL, can modify the given expression in a manual mode. It is obvious, that such technics(technical equipment) of work demands essentially greater qualification, but simultaneously it(she) yields the developer powerful and universal management personnel data.

Speaking about communication(connection) between a mode of the visual designer of inquiries and a mode of construction of SQL-expressions, it is necessary to note, that there is a natural and logical communication(connection) between types of inquiries and SQL-operators realizing them. In particular:

• To inquiry about sample there corresponds operator SELECT
• To inquiry about creation CREATE
• To inquiry about updating UPDATE
• To inquiry about removal DELETE etc.

The author: Y.N.Antonov Is added: 18.06.2008