The MS Access database application is a complete assistant for creating and maintaining databases enclosed in tables and arrays. If the base is too large, quickly finding the necessary values is quite difficult.
That is why Access has a feature like queries. Consider what it is, how it works, what features it has.
Creating Queries in Microsoft Access
To understand how to create queries in Access, you need to know the basic principles of working with a DBMS.
There are two ways to perform this procedure:
- Query constructor.
- Query Wizard.
The first method makes it possible to create any of all available queries manually, but with a small caveat, namely that the user has experience with the Access application. Also, he should understand at least his main tasks. As for the second method, it needs to be considered in more detail.
Easy way for beginners
A knowledgeable person selects with a few clicks those components that the user will need to complete the request, and then quickly forms the registry in accordance with the collected key values. If this is the first acquaintance with the DBMS, and the user does not imagine how to create queries in Access, then the Master program is selected.
In this mode, you can familiarize yourself with and understand the following types of queries:
- Plain.
- Cross.
- Records without subordinates.
- Duplicate entries.
This choice is made at the first stage of work with the Master. And in the future, following clear instructions, even a novice user will easily create a request. Get acquainted with its varieties.
Simple request
This table tool collects the necessary data from user-specified fields. Already the name shows that this is the most popular type of request for beginners. Its convenience lies in the fact that such a procedure opens in a new tab. Therefore, the answer to the question of how to create a query in Access 2010 becomes obvious after opening the first menu of the Wizard.
Cross request
This type of sample is more complex. To understand how to create a cross-query in Access using the "Wizard" in this mode, you need to click on this function in the first window.
A table appears on the screen in which you can select up to three columns located in the original.
One of the remaining unselected fields can be used as the headers of the query table. At the third stage of the procedure (intersection), one more value is selected with the variability of the function (average value, sum, first, last).
The photo shows that a cross-query has been created, and that the necessary actions have been taken by the specified parameters.
Duplicate entries
As the name implies, the main purpose of this query is to select all the same rows in the table according to the specified parameters. It looks like this:
In addition, a selection of additional fields is available to select a match in several lines at once.
To select duplicate entries, you need to expand the list of queries and create a new folder there. Next, in the "New request" window, select the line "Search for duplicate records." Next, follow the instructions of the Wizard.
Records without subordinates
This is the last type of query available in the "Master - Records without subordinates" mode.
In this case, only those values are selected that are not involved in any field of tables and queries, but which are already created.
This type is relevant only in cases where there are several databases.
All four of these types of queries are the base point for working with complex elements, but they make it easy to figure out how to create a query in the Access database.
Query Functions in MS Access
Let's figure out why you need to perform the above steps. The task of all simple and complex queries in the Access DBMS is as follows:
- Collection of necessary data in tables, their subsequent viewing, editing, adding new values.
- Excellent source material for the preparation of various forms of reporting.
- Conducting mathematical and statistical counting procedures on entire data arrays with the conclusion of the results on the screen (average value, amount, deviation, results).
Sample Request
This type of work with databases is complex, as it requires the participation of several tables.
All tables must have common key fields. Otherwise, the operation will fail.
Let's repeat how to create a query for access in Access. First you need to create a simple query with the selection of the required fields. Already here you can edit the data to bring them to the desired form. By the way, the changes made will be transferred to the source tables, so this point needs to be taken into account.
In the designer window that opens, the "Add Tables" window is filled. Here you need to add the tables or queries from which you want to get the original values.
After adding, you can start filling out the conditions of the request. To do this, we need the line "Field". In it, you need to select those values from the tables that will be displayed when prompted.
To complete the operation, click on the "Run" button.
Request with parameters
This is another kind of complex procedure that will require the user to have certain database skills. One of the main directions of this action is preparation for creating reports with voluminous data, as well as obtaining summary results. How to create queries in Access 2007 using the constructor will be described below.
To start this procedure for data selection, you need to create a simple query to select the required fields. Further, through the Designer mode, it is necessary to fill in the “Selection condition” field and, based on the entered value, selection will be carried out.
Thus, to the question of how to create a query with a parameter in Access, the answer is simple - enter the initial parameters for the selection. To work with the Designer, you must use the Query Wizard. There, primary data for filtering is created, which serve as the basis for further work.
Expanded Cross Query
We continue to complicate the situation. Even more difficult to understand is the information on how to create queries in Access, if there are several tables with data. Cross-request has already been considered above, as one of the options for working with the Wizard. However, in the "Design" mode, you can create a similar request.
To do this, click "Query Designer" - "Cross".
The menu for adding source tables opens, as well as the ability to fill in custom fields. The only things to pay attention to are the items “group operation” and “crosstab”. They must be filled in correctly, otherwise the procedure will not be performed correctly.
Cross-querying is the easiest way to search and retrieve information from multiple data sources, plus the ability to create charts and graphs.
Moreover, when using this procedure, a search is performed faster, even with several development options.
Of course, there are pitfalls that can interfere with work. For example, when creating a query to sort the database by column value, the system generates an error. That is, only sorting by standard items is available - “increase and decrease”.
Brief recommendations
Summing up, it must be said that it is up to the user to decide how to create requests in Access - using the Wizard or Constructor. Although, for most people who use the MS Access DBMS, the first option is more suitable. After all, the Master himself will do all the work, leaving only a few mouse clicks for the user, when choosing the conditions of the request.
To use advanced settings, professional experience with databases is clearly required. If large databases are involved, it is best to contact specialists in order to avoid disruption of the DBMS and possible data loss.
There is one point that is available only to programmers. Since the main language of the DBMS is SQL, the necessary query can be written in the form of program code. To work in this mode, just click on the line of the already created query, and select "SQL Mode" in the context menu that opens.