A database management system (DBMS) is a special program or set of programs that are necessary for organizing and maintaining relevant databases. One such program includes MS Access.
The concept of queries
They are used to sort, add, modify, filter, delete certain information in the database. With their help, the selection is made of those records that are needed for the preparation of certain reports or forms, that is, work is carried out with part of the database.
When creating queries in Access, tables can join, records are grouped and selected, and settlement operations are connected.
Request Classification
The following types of queries are distinguished:
- A query for a sample in which data is extracted according to the specified condition. As part of this query, records are grouped, and the calculations performed in the fields of the table are presented.
- A change request that changes data in the original tables. Using them, data can be adjusted, and new tables can also be created.
- A query with parameters in which certain conditions or data are entered.
- Cross-cutting queries designed to calculate and provide data are usually in the form of spreadsheets to facilitate analysis.
- SQL queries are queries for obtaining certain information; their construction is based on observing certain rules using specific syntax.
Sample Requests
Creating queries in Access of this type involves building a table containing the same structural elements as a regular one. It is created on the basis of evidence.
The results are a dynamic data set, which is why when the set is closed, the records “disappear”, remaining in the original tables. Saving query data means maintaining its structure.
These requests are formed by indicating the fields and tables containing them that are included in the request, a description of the calculated fields, group operations performed on the initial records, and the formation of selection conditions (for example, from what date the certain group of goods was sold).
Creating requests in MS Access of this type suggests that they can be created manually or using the "Request Creation Wizard".
To determine the fields and tables to be included in the query, we go into design mode.
To switch to the design mode in Access 2013, in the navigation area, right-click on the form name and select "Design". By pressing ALT + F8, you can call up the "Field List", from which the fields can be dragged directly into the form.
Change Requests
These types of queries are a kind of first type, but they are needed to modify the data that was extracted. They are marked with an exclamation mark in Access. In Access, there are four types of change requests:
- creating new tables;
- adding records to source tables;
- changing data in source tables;
- Removing records from source tables.
Therefore, these actions can destroy the database, in connection with which you must first check the result of query execution by going into table mode.
Creating new tables is used when archiving data, conducting backups or exporting data.
A variety of change requests are update requests, which are used to update absolutely all records that satisfy any given condition. If it is necessary to make changes to the data complex, then this variety is used.
Another variation is delete requests, which destroy all records that meet any given conditions.
Another variation of the queries in question are add queries, which add data from one table to another.
Cross queries
These types are used to combine in the crosstab the money or numerical data stored in the original table. Cross query in Access is created using the query wizard, using the instructions of which, you can easily create this view. At its core, a cross-query is similar to Excel pivot tables.
A crosstab is created when row headers, columns, values, and a group operation are specified.
The scheme for constructing this type of table is as follows. Using a DBMS, data is grouped by group fields. This operation is performed on numbers in the value field. In the cell of the table located at the intersection of the column and rows, the total value of this group is placed.
Cross queries in Access are typically used to create reports and charts.
Parameter Queries
This type of query clarifies the condition for the user before fulfilling.
To create a query in Access, you need to place the parameter input expression in square brackets in the query column, in the "Condition" cell .
If you run this query, a dialog box appears that will contain a suggestion for entering parameters.
SQL queries
In Access 2013 and other versions, queries are executed using the SQL structured query language.
To create SQL queries it is necessary to switch to design mode, then select "SQL mode" on the "View" button. As a result, a dialog box appears in which we enter the names of the columns in the Select field and in the From field what appears in the rows, for example, the names of suppliers (if the first column is "Name").
Access does not use pure SQL, but its Jet-SQL dialect. The main instructions for queries in this language are: SELECT, which selects records from certain conditions (the names of the fields in the source tables that are transferred to the resulting table), UPDATE - is used to edit records, DELETE - to delete any specified records, CREATE - to create new database objects. MS Access also uses TRANSFORM to build cross-queries, WITH OWNER-ACCESS OPTION to create special queries by a user who does not have access to the tables that this query must have access to, IN (to provide communication with a remote database), DISTINCTROW (creation query with possible data aggregation). You can also use the resulting SQL functions, built-in Access and VBA functions.
Finally
Access queries are created to create records for other queries, reports, or forms. Using certain actions, data is collected from several tables. Requests allow you to include selected fields in the generated table, 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.