Simple Select Queries

Laboratory work №5

Development of database structure, creation of tables and queries.

MS Access. Queries

TASK2:

· Open the database Construction.

· Create the following queries:

§ Simple select query.

§ Several simple select queries with various conditions.

§ Multiple select queries (with one and several conditions).

§ Action Queries (Make Table Query, Update Query, and Delete Query.)

§ Parameter Query

· Save the database.

 

Procedure of laboratory work:

1. Open the database Construction. Press the Options button and choose the "Enable this content" command and click OK.

 

Simple Select Queries

Queries in Access are intended for obtaining necessary information from one or several tables. At the same time the user can select only some of fields of tables and set conditions on which data from tables will be selected. It is accepted to call the queries based on one table Simple Select Query, and queries according to several tables - multiple or relational.

For creation of queries in Access the QBE method is used (Query by Example). The main tool for queries is the Design. Besides, it is possible to use masters who will help to create this or that queries. One more tool for queries is the SQL language. During queries creation you can on the "Design" tab in the "View" menu choose the command of "SQL View" and work with queries, using the SQL language.

2. Let's consider creation of a Simple Select Query. As an example we will select the " Construction " database. The table " Worker " of this basis contains the information about workers, such as name, profession, salary. Let us need to clarify which of workers receives more than 50000. For this purpose we will perform the following operations:

• In a database window on the " Create " tab we will click on the " Query Design " button. There will be a dialog box:

 

· On the screen the window of the designer and the " Show Table " dialog box will appear:

By means of this window we choose those tables from which we want to obtain information. It is necessary to pay attention that not only tables can be a source. Other queries can act as a source. The choice of a source is carried out by click on him and pressing the " Add " button. After all necessary tables and queries are chosen, we finish dialogue with click on the " Close " button. In our example we will choose only the table " Workers ".

• Let's consider more attentively the Design's window:

Conditionally the Design window can be divided into 2 parts. In the top part there are tables and queries which are a source. The lower part represents the form of query on a sample. In the form of 6 names of lines. All lines which are below a line with the name "or" belong to it. Let's consider purpose of each of lines:

§ " Field " - in this line settle down names of fields from which information is necessary for a query. To place the name of the necessary field in this line it is enough to double click according to its name in the source which is in the upper part of the designer. An alternative method - to select a name of the necessary field from the revealing list. If it is necessary to select all attributes of some table, it is possible to click on the character "*" in a source. All information which is under field name will belong only to this field. We will select the Name and Salary fields from our example.

§ " Table " - information in this line appears automatically, in case of a field choice. Here it is specified in what table there is this field.

§ " Sort " - In this line we can specify whether information will be sorted and as. Ascending sort order and to decrease is possible.

§ " Show " - Information from a field can be shown on the screen in case of execution of a query, but maybe is hidden from the user. If you want that information was displayed - check whether the checkbox in this line under the necessary field is set.

§ " Criteria " - the User is given ample opportunities at the choice of the necessary information. By means of selection conditions it can restrict information output, selecting only necessary. In more detail we will consider conditions in the following item.

§ " Or " - allows to use logical action of " OR " in the conditions of a choice. In the following item this operation will be considered in more detail.

In the example reviewed by us the form of a request will take the following form:

After the query is created, it needs to be saved. It is executed or click on the appropriate toolbar button, or when closing a query (the question of need to save a query will be asked).

The created queries stored in the appropriate tab of a window of the database and can be launched by the " Open " command or are modified by the " Design View " command. In the inference we will add that the query represents the dynamic table, i.e. in case of change of data in sources also data in a query change. Actually not data, but structure of a query remain, i.e. in case of each new start of a query data are generated again. It is necessary to pay attention that data in a request "live". It means that all changes made in a query automatically will be transferred to the table source. Therefore it is necessary to be attentive, by operation with queries since accidental deleting data will be irreversible.