Multiple Select Queries

Multiple (relational) select queries are created to similarly simple requests. The single difference is that as a source several tables appear. During creation of multiple select queries it is necessary to pay attention that between tables sources connection shall be established. It can be made in advance, during creation of the database (by means of the "Relationship" command), and can be made in the designer's window. In the latter case connection is established in the same way, as in case of execution of the command of "Relationship". Cross-table relationships are necessary for formation of a condition of connection of tables. It is possible to give the following multiple select query as an example:

Criterion: Like "A*" Or Like "D*"

 

Let's note separately that sometimes it is required to connect the table to itself. Such case is called self-association.

 

Action Queries in MS Access

In MS Access we will understand such queries as Action Queries as: update query, a parameter query, crosstab query, etc. The most part of similar queries is created, as a rule, by means of the masters. We will consider creation of several types of special queries by means of design.

Update Query

Queries of this group allow performing certain operations over the derived data. Generally these queries are created for the decision of such tasks as: deleting or archiving of large volume of certain records; entering of global changes into the database. The following queries enter into this group:

Update Query - by means of this query can be changed all data which meet a certain condition. Let's consider process of creation of this query:

1. Create the query for selection taking those data which need to be updated.

2. On the "Design" tab in the "Query type" menu choose the command of "Update".

3. In the form of a query the new line - "Update To" will appear. It is necessary to enter new data or a formula on which they are calculated into this line. For example, if we want a salary of workers for 5%, we shall enter in this line under the field salary the following:

= [salary]*1,05.

Delete Query - is required for removal of a large number of the records according some criteria. It is possible to execute this query unlike previous only once (actually queries for addition and updating can be carried out more than once, but it doesn't make sense and can lead to mistakes). Let's consider process of creation of Delete Query:

1. Create the request for selection taking those records which are subject to removal.

2. On the "Design" tab in the "Query type" menu Choose the command "Delete".

Make Table Query - as appears from the name, this query allows to create new tables on the basis of the taken data. Most often such queries are applied as sources to any monthly reports. It is necessary for creation of a similar query:

1. Create the make table query taking those records which need to be added to the new table.

2. On the "Design" tab in the "Query type" menu choose the command of "Make Table".

3. In the appeared dialog box enter a name of new table.

4. Remember that only names of fields and data are transferred to the new table. Properties of fields and primary keys need to be set anew.

Parameter Queries

A parameter is a piece of information you supply to a query right as you run it. Parameters can be used by themselves or as part of a larger expression to form a criterion in the query. You can add parameters to any of the following types of queries: select, update, make-table etc. They can be used, for example, in case of creation of daily reports (the necessary date, every time new will be entered) or when using any intervals (which can often change).

For creation of a parameter query it is necessary to create a select query, with that difference that instead of a specific condition in the line "Criteria" the text concluded in square brackets is entered.

For example:

[Enter date]

Or

between [Enter the minimum quantity] and [Enter the maximum quantity]

Creating a parameter is similar to adding a normal criterion to a query:

1. Create a select query, and then open the query in Design view.

2. In the Criteria row of the field you want to apply a parameter to, enter the text that you want to display in the parameter box, enclosed in square brackets. For example, [Enter the start date:]

3. Repeat step 2 for each field you want to add parameters to.

When you run the query, the prompt appears without the square brackets.

Fill in the value you’re looking for, and then click OK.

You can use multiple parameters in a criterion. For example, Between [Enter the start date:] And [Enter the end date:] will generate two prompts when you run the query.

 

For example: