Database Management System (DBMS) isspecial program or a set of programs that are necessary for the organization and maintenance of relevant databases. One of such programs is MS Access.
Concept of requests
They are used to sort, add,changes, filtering, deleting certain information in the database. With their help, the selection of those records that are needed for the preparation of certain reports or forms, that is, work with part of the database.
When creating queries in Access, tables can be connected, records can be grouped and selected, calculation operations can be connected.
Query classification
There are the following types of requests:
- A request for a sample in which data is extracted by the specified condition. Within the framework of this request, records are grouped, and calculations performed in the fields of the table are presented.
- A change request that changes the data in the original tables. Using them, the data can be adjusted and new tables can be created.
- A query with parameters whereby certain conditions or data are entered.
- Cross-queries designed to calculate and provide data, usually in the form of spreadsheets to facilitate analysis.
- SQL queries are requests to obtain certain information, their construction is based on the observance of certain rules using a certain syntax.
Requests for sample
Creating queries in Access of this type involves the construction of a table containing the same structural elements as the usual one. It is created based on actual data.
The results are a dynamic setdata, and therefore when you close a set of records "disappear", remaining in the original tables. Saving query data means storing their structure.
These requests are formed by specifying the fields andtables containing them, included in the request, descriptions of calculated fields, group operations performed on the original records, and the formation of selection conditions (for example, on what date was the realization of a certain group of products).
Creating queries in MS Access of this type assumes that they can be created manually or using the "Query Wizard".
To determine the fields and tables included in the query, go to design mode.
To switch to design mode in Access 2013in the navigation area, click the context button on the form name and select "Constructor". By pressing ALT + F8, you can call the "List of fields", from which the fields can be dragged directly into the form.
Change requests
These types of queries represent somea variation of the first kind, but they are needed to change the data that was extracted. They are in Access labeled with an exclamation mark. In Access, creating change requests is possible in four types:
- creating new tables;
- adding records to source tables;
- change data in source tables;
- delete records from source tables.
Therefore, these actions can destroy the database, and therefore it is necessary to first check the result of the query by switching to table mode.
Creating new tables is used when archiving data, making backups or exporting data.
A variety of change requests areupdate requests that, when used, update absolutely all records that satisfy a given condition. If it is necessary to make changes in the data complex, then use this type.
Another type of deletion is delete requests that destroy all records that satisfy any given conditions.
Another type of queries considered are add requests in which data is added from one table to another.
Cross inquiry
These species are used for the purpose of combininga crosstab of monetary or numeric data stored in the source table. A cross-query in Access is created using the query wizard, using the instructions from which you can easily create this view. At its core, a crosstab query is similar to Excel PivotTables.
A cross table is created by specifying row headers, columns, values, and a group operation.
The construction scheme of this type of table is reduced tothe following. With the help of the DBMS, data is grouped by group fields. This operation is performed on numbers in the value field. The total value of this group is placed in the table cell located at the intersection of the column and rows.
Cross queries in Access are usually used to create reports and charts.
Requests with parameters
This type of requests specifies the condition of the user before performing.
To create a query in Access, it is necessary in the query column, in the "Condition" cell, to place the expression for entering parameters in square brackets.
If you run this query, the dialog box will appear, which will contain a suggestion for entering parameters.
SQL queries
In Access 2013 and other versions, queries are performed using the structured query language SQL.
To create SQL queries, go todesign mode, then on the "View" button select "SQL mode". As a result, a dialog box appears in which in the Select field we enter the names of the columns, and in the From field - what will be displayed in rows, for example, the names of the suppliers (if the first column is "Name").
Access does not use pure SQL, but itsJet-SQL dialect. The main instructions for queries in this language are: SELECT, which selects from records according to certain conditions (the names of the fields of the original tables that are transferred to the resulting table), UPDATE — used to edit the records, DELETE — to delete any specified records, CREATE - to create new database objects. MS Access also uses TRANSFORM for building cross-queries, WITH OWNER-ACCESS OPTION to create special queries by a user who does not have access to the tables to which this query should have access, IN (to provide communication with a remote database), DISTINCTROW (creating a query with the possible merging of data). The resulting SQL functions, Access functions, and VBA functions can also be used.
Finally
Создание запросов в Access происходит для generating records for other requests, reports or forms. Using certain actions, data is collected from several tables. Requests allow you to include selected fields in the table being created, perform calculations in each new record, select records that are necessary to satisfy certain conditions, group expressions with the same values in some or one field, create a new data table based on existing ones, add, delete or update some entries.
