How to create queries in Access: step by step instructions and recommendations

Application, MS Access database – is a helper for creating and maintaining databases of inmates in tables and arrays. If the base has too much, quickly find the required values is quite difficult.

That's why in Access there is such function as requests. Consider what it is, how it works, what has features.

Creating queries in Microsoft Access

To understand how to create queries in Access, you need to know the basic principles of work with DBMS.

There are two ways to perform this procedure:

• The query Builder.
• The query Wizard.

The First method allows you to create any of all available queries in the manual mode, but with a small caveat, namely that the user has experience with the Access app. He also needs to understand at least in its main objectives. As for the second method, it is necessary to consider in more detail.

The Easy way for beginners

A Knowledgeable person a few mouse clicks selects those items that will require the user to execute the query, and then quickly forms the registry, in accordance with the collected key values. If is your first introduction to DBMS, and the user has no idea how to create queries in Access, selected a Master.

In this mode, you can see and deal with the following types of queries:

• Simple.
• Cross.
• Records without subordinates.
• Duplicate entries.

This selection is carried out already at the first stage of the Wizard. And further, following the clear instructions, even a novice user can easily create a query. Get acquainted with its variants.

Simple query

This tool work with tables collects necessary data from user-specified fields. The title already shows that it is the most popular type of queries for newbies. Its convenience lies in the fact that such a procedure is opened in a new tab. So the answer to the question of how to create a query in Access 2010, it becomes apparent after opening the first menu of the Wizard.

Cross reference

This type of sampling is more complex. To understand how to create a crosstab query in Access using the Wizard in this mode, you need to click on this function in the first window.

The table appears where you can select up to three columns arranged in the original.

One of the remaining selected fields can be used as table headers of the request. At the third stage of the procedure (intersection) selects another value with the variation of the function (average, sum, first, last).

The photo shows that a crosstab query is created, and that the given parameters are absolutely necessary steps.

Duplicate entry

As the name implies, the main purpose of this request – the sample is the same for all rows in the table according to these parameters. It looks like this:

In addition, the available choice of additional fields to match multiple lines.

To select duplicate records, you need to open the query list and create a new folder. Then in the window "New query" to select "duplicates". Next you need to follow the instructions in the Wizard.

Records without subordinates

This is the last type of query-mode "Master – the Records without subordinates".

In this case is fetch only those values which is not involved in any field of tables and queries, but which is already created.

This type is relevant only in cases where several databases.

All of these four types of queries are the starting point for work with complex elements but make it easy to figure out how to create a query in an Access database.

Queries in MS Access

We will Understand why you need to follow the steps outlined above. The task of all simple and complex queries in the database Access is as follows:

• Gathering necessary data in the tables, then view, edit, add new values.
• Excellent source material for the preparation of various reporting forms.
• Mathematical and statistical counting procedures over all data sets with the output results on the screen (average, sum, variance, totals).

Query

This type of working with databases is complicated, as it requires the participation of multiple tables.

It is Necessary that all tables have a common key field. Otherwise, the commit operation will fail.

Repeat how to create a select query in Access. First you need to create a simple query by selecting the desired fields. Already here you can edit the data to bring them into a desired form. By the way, the changes will be transferred in the source table, so this point must be considered.

In the window constructor fills the window "Add table". Here you need to add those tables or queries from which you want to get the original value.

After you add the you can start filling in the query. For this we need the string “Field”. It is necessary to pick up values from tables, which will be displayed for the query.

To complete the operation, click on the "Execute"button.

Request parameters

This is another kind of complex procedure that requires certain skills of work with databases. One of the main directions of such steps is to prepare for creating reports from stored data and obtaining summary results. How to create queries in Access 2007 using the designer, will be discussed below.

To Start this procedure from sample data need with create a simple query to select the desired field. Then through the design mode need to fill in the “select” and, on the basis of the entered values will be selected.

Thus, the question of how to create a query with parameter in Access, the answer is simple: to make initial settings for the sample. To work with a Designer, you must use the query Wizard. There is created a raw data filter that serve as the basis for further work.

Advanced crosstab query

We Continue to complicate the situation. Even harder to understand is information about how to create queries in Access if you have multiple tables with data. A crosstab query has already been addressed above, as one of the options Wizard. However, in design view you can create such a query.

To do this, click “query Designer” - “Cross”.

Opens a menu to add the source tables, and the ability to populate custom fields. The only thing you should pay attention, – points "group operation,” and “cross-table”. They should be populated correctly, otherwise the procedure will not be done correctly.

Crosstab queries – this is the easiest way to search and retrieve information from multiple data sources, plus with the ability to generate charts and graphs.

Moreover, when using this procedure faster searches, even with multiple development options.

Of Course, there are also “pitfalls”, which can interfere in the work. For example, when you create a request to sort database by column value the system generates an error. That is available only sort by the standard points – “the increase and decrease”.

Brief summary

Summing up, I must say that to decide how to create queries in Access – with the help of the Wizard or the Designer, the user must. Although, for most people, which...