Unit 7. Queries (I)


In this unit we will learn how to create queries and how to use them to edit records in tables created in Access2003.

 

Types of queries.

 

Queries are those objects in a database that allow us to view, change, and arrange data stored in tables.

We can also use them as the source of records for forms and reports.

Various types of queries exist:

Select queries.

These are the queries that extract or show us data. They will show data that complies with specific criteria.

Once we have the result we can consult the data and edit it (this can or cannot be done, depending on the query). A select query generates a logical table (named this way because it is not actually in the hard drive but in the memory of the computer, and every time we open it is recalculated).

This is the most common type of query.

Action queries.

These are the queries that carry out changes to the records. Various types of action queries exist to delete, update, insert data, and to create a new table from one existing table. These queries are named delete queries, update queries, append queries and Make-Table queries. We will study them in unit 10.

Crosstab queries.

We use these queries to calculate and restructure data for easier analysis. Crosstab queries calculate a count, average, sum, or other type of total for data that is grouped by two types of information (two fields), one down the left side of the datasheet and another across the top.

SQL queries.

When we want some action to be made on the data, we must tell Microsoft Jet engine to do it. SQL is the language that Microsoft jet engine understands and permits us to comunicate to it.

When you create a query in Query Design View, Access constructs the equivalent SQL statement behind the scenes for you. If you want, you can view and edit the SQL statement in SQL view.

After you make changes to the query in SQL view, the Query Design view will change and adapt to the new SQL sentence. However, sometimes, query might not be displayed in Query Design view because of the SQL sentence.

There is some statements that can not be defined from the Query Design View but rather directly in SQL, these are SQL-specific queries.

These queries will not be studies in this course as in order to define them knowledge of SQL is necessary, which is not part of the objective of this course.

 

Creating a query.

To create a query, follow the next steps:

Open the database where the query will be created.

Click on the Queries object found on the left lateral of the Database window.

This is the screen that will appear:

Later we have 3 alternatives:

Double click on the Create query by using wizard option.

In this case the wizard window will open in which we are asked from which table we choose to extract the data, the fields we wish to visualize and the title of the query, next it will automatically generate the corresponding query.

Double click on Create query in Design view.

In this case the Query design window will open on which we will elaborate further on, and within which we can define our query in more detail.

Click on the button in the Database window.

The following dialogue box will appear:

The Simple Query Wizard is the same as Create a query using the wizard mentioned above.

The other wizards permit us to generate special types of queries. In Unit 9 we will study the Crosstab Query Wizard.

The Design view option has the same effect as the Create a query in Design view. This is the option we will explain next.

 

On entering the Query design we are firstly asked for which tables the query should extract the data from:


 

 

 

 

Select the table from which we wish to extract the data and click on the Add button (or double-clik on its name).

If we wish to extract data from another query, click on Queries tab an select it.

If we wish to extract data from various tables we should continue in the same manner.

Finally click on the Close button.

The query Design view window will appear.

To continue go to the next page...

 



 

Page. 7.1

Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited. April-2006.