Access - Queries

Introduction

One of the most powerful elements of a data base is the capacity to search a massive amount of data stored in a data base. You can make many analyzes and maybe find some trends. For example, you don't need to see the entire list of customers if you only need the phone number of one of them. Apart from search queries, there are also action queries for updating or removing data.

The next section consists in learning the steps to create a query. The next query will give you some information about the employees. But first, you must have created the "Employees" table from tables web page.

If you don't have the time to create the table, you can copy the data base demoacc1.mdb (for Access 97) or demoa2k1.mdb (for Access on 2000) containing
the table "Employees". You can begin at once to create the queries. These
files are on the demonstrations files web page that
contains all the files used during my demonstrations and the exercises of this
site. click here to return you on this page then to
return. Later, there will be more explanations on the options for a query. The
following Web page consists of exercises to look more and more in depth at what a query can do.

Steps to make an Access query

1. Choose one or several tables or queries
2. Choose the type of query
3. Choose one or several required fields
4. Determine if fields need to be sorted out
5. Hide fields from the result view
6. Determine the criteria
7. Execute the query
8. Specialized options: grouping, expression generator
9. Connect tables

The next stage consists in creating a query. It mush show the first name and the last name from all the employees whose first name is Roger.

Click
on the queries tab . Press
the New button.

1. Choose one or several tables or queries.

Access offers you several ways to create a query: by using one of the wizards or by building it from beginning to the end in Design view.

The
Design view is the one that allows you all the options to create a query.
However, there is no assistant to help you. It is partially for that reason
that this page exists; to demonstrate the full potential of the queries. The
simple query assistant asks you for the list of the fields that you want to
see and shows the result. There are no criteria of selection. It shows only
the fields you asked for all the records. The
crosstab query assistant asks you for some questions to create a crossed picture.
You can easily know the number, the sum, the average and the other operations
of a field with regard to another. For example, you can know the number from
employees that works in the company by office location and by occupation. Or,
that types of products buy the various customers of the company. It can become
a tool of analysis powerful. The
find doubles query assistant is also very interesting when you have difficulties
creating relations between tables. One of the reasons so that Access refuses
to create a relation between tables is that there is an illegal doubloon in
one of the tables. This query would allow you to know that record has a contents
of a field in double to allow you to correct the situation.The
assistant query of not correspondence is also useful for the relations between
tables. To what is of use a relation if there is no equivalent datum in the
other table? This query allows to find the records that have no equivalent,
or value in common, in the other connected table. This allows to make sure that
all the records are connected with the information of another table.

For the example, select the Design view option. Press the OK button.

You can create a query from a table or even from another query. It is even
possible to have a query that consists of fields resulting from several tables
or from queries. Access will ask you of the list of tables and queries
to choose who or that one you want to add to your new query. select, one at
the same moment, tables and queries and press the button add. When you will
have selected the table, the query or a combination of both that you
need, press the Close button.

Select
the Employees table. Press
the Add button.

Because
you need no other tables or queries, press the Close button.

You can create a query from several tables or queries or a combination
of both. If, for a reason or another, you forgot a table or a query, you
can add it in Design view by pressing on the button. It will then be required to repeat the operation above.

For training purposes, the next queries will be all consistsd from the Employees table.

You are now in the screen for the creation of query. The part of the height contains tables and queries for that you asked before. The part of bottom is the one that is the most important. It is there that you decide on fields that you need as well as options that you want.

The first row serves to select the fields that you need for your query. The second row is to determine the order of view of the data. The third row is to determine if the field should be shown or not. For certain conditions, it's better don't show a field. The fourth row and the following rows serve for determining the criteria to show a record.

Access offers you six kinds of queries. Each is specialized for a certain kind of query or operation.

Request
query: View the records that answer the wanted criteria. It is the type
that you go to use mostly. It shows only the records that answer the selected
criteria.

Crostab
query : View a picture of synthesis according to the wanted fields.

New query:
Creating a new table with the fields you asked according to the wanted criteria.

Updated query: Update of the records according to the wanted criteria.

Add query:
Add records of another table according to the wanted criteria.

Deletion
query: Eliminate records according to the wanted criteria. It is possible
to erase at the same time a group of records that answer the wanted criteria.

For the moment, it is about a query of type selection. It is the one that you use to carry out searches in a data base. For the exercises number 1 until 21, it is the query of type selection that will be used. The following exercises use another type of queries.

3. Choose one or several required fields

Among tables and the queries that you chose, you should choose the fields that you need for your new query. It means that you are not obliged to use all the fields; only those necessities for the query. So, the character "*" located at the beginning of every table that meets itself in a query indicates that you can add all the fields of this one in the query in the order that they are at present in the table. This applies also to the other queries that you can add to your query.

There are three ways to choose a field:

1. Double-click on the field 2. Choose from the list 3. "Drag" a field and insert it in the right place

1. Double-click on the field

Place the pointer over the field that you need. Double-click by using the left mouse button.

The selected field will be added at the end of the list of fields already selected.

Repeat
the operation until you have all the fields required for the query.

2. Choose from the list

Place the pointer in the white cell next to the word Field:. Click in the cell. Click on then the black arrow pointing downward the right-hand side of the cell.

From the list of fields, select the field that you need. Repeat
the action with the cell to the right-hand side until you chose all the required
fields to end your new query.

3. "Drag" a field and insert it in the right place

From the list of tables and queries that you selected, place the pointer on one of the fields that you want in your new query. Keep your finger on the left mouse button.

A small white box will appear. This is to indicate that you are "holding" the
field of your choice. It remains only to put it in the area of the criteria.

While
pressing the left mouse button, move the pointer on the row where you
want to insert the field.

This last method is very practical to insert a field between two others. Once chosen, these fields will be shown in the same order as you chose them from left to right. It is also possible to move fields in the query.

Place the pointer on the small grey box above the name of the field. Click
on this case. Keep your finger on the left mouse button. Move
the mouse towards the left-hand or the right-hand side until the selected field
is in its new location. Release the mouse button.

For the creation of your first query, use the technique that you prefer to put on the row of fields the First name followed by the field name.

4. Determine if fields need to be sorted out

When you chose the required fields to complete your new query, you should determine if these fields need to be shown in a definite order. Do you want the list of the customers in alphabetical order, in order according to their figure of affair(business) with your company, according to their geographic place or a combination of these orders?

Below every name of the field is the option to activate the sorting on this one. You can decide not to activate it, the sorting or the resources in increasing or lessening order.

Click in the box of the sortings. Press the button with the triangle pointing down. From
the list, you can select the type of sorting that you want (ascending, descending,
none).

The priority of the sortings.

It is however required to pay attention. The field sorted out most to the left in the priority on the others whom is for its right-hand side. So, if fields First name and name are sorted out, it will be first in order of First name followed by the name. Ex:

Alain

Lacasse

Alain

Lemire

Josée

Lacasse

For the creation of your first query, it is useless to sort out the shown fields.

5. Hide fields from the need

Normally, all the fields that you select will be shown. It happens at times however when you need a field for the query but when you don't want to show this one. For example, you want the First name and name of the persons gaining(winning) an income superior to 50 000 $ but you don't want to show this .

To hide a field of the view

Click on the square of view below the option of the sorting.

One "X" in the box indicates that the field will be shown during the presentation of the records.

For the creation of your first query, it is useless to hide fields.

6. Determine the criteria of selection

Select fields and having placed in the order of your choice, having sorted out on certain fields, having decided to show or not certain fields, it is required to determine that are the criteria of selection. If you write no criterion, all the records will appear.

The criteria serve for filtering the records only to show those that you need. For that purpose, Access offers you several operators for various situations. Here is the list so that a short description of each.

The logical operators

= <,>, <=>, =

Unless another operator is chosen, the criterion is always equal to the contents of the criterion.

? *

These operators replace a character (?) or a series of characters (*).

And

You can combine criteria to limit the number of records that answer the criteria.

Or

You can combine criteria to have most possible records.

Between and

View the records that are among such and such criteria. These are including.

Not or <>

View all the records EXCEPT those that answer the criteria.

Is null

View the records of that the contents of a certain field are empty.

Calculationated field

You can create calculated fields that give the result of a formula that uses the contents of the fields of the record. Ex: Total: [Quantity] * [ Prix_Unitaire]

Operations

Allow to have the sum, the average, the number so the other mathematical operations on the records that answer the wanted criteria.

Generator of expression

Allow to use at most the queries. Use the functions integrated into Access.

For the example,
write roger for the criterion under the field "First name".

7. Execute the query

Press
the button.ORFrom
the View menu, select the Worksheet option.

The records that answer the criteria will be shown.

To
return then to the mode creation, press the button. ORFrom
the View menu, select the Creation option.

8. Specialized options: the functions, the expression generator and the operations

Access offers you much more to create complex queries. It offers you a long
series of functions that you can use in the queries, the forms or the reports.
To help you to use these functions, Access offers you the expression generators
that gives you access to all the available resources from the software. That
it is, fields, tables, queries, forms, reports, macro, modules or integrated
functions, they are all available from the expression generators. To end, it
is also possible to group together the records to make operations such as the
sum on a field, to count, to find the average the others.

The functions

Besides the calculated fields, Access possesses functions predetermined to help you to realize queries and more complex analyses. There are 176 functions of available that are grouped together(included) in 16 categories. Here is the list of the categories and the number of functions in each of the categories.

Name of itCategory

Number ofFunctions(offices)

Table

2

Conversion

31

Data base

13

Date/Time

23

DDE

6

Regroup. Domain

10

Management of error

4

Financial

13

General

10

Input-output

14

Inspection

5

Mathematics

12

Messages

3

Prog progress

3

Grouping SQL

9

Text

28

There is a function for almost all the occasions. It is enough to press the button to show
the expression generator. From this window, you have access to all the resources
available on Access. To see the list of the functions:

Double-click on the Functions folder located in the first column of the window. Click
on the Integrated functions folder.

The contents of the second and the third column will fill up. The second column contains the list of the categories of functions. The third column contains the list of the available functions.

To have more information about one of the functions:

Click
on the function that interests you. Press
the Help button.

A description of the function will appear in a window.

The operations

Besides examining with criteria or functions integrated by Access, it is also
possible to group together fields to make(do) operations such as the sum, the
numeric average of fields. From the other operations, you can also
count the number of records that answer certain criteria.

To
reach the operations, press the button. ORFrom
the View menu, select the Operations option.

Here is the list of the operations and it that you can carry out by using them.

Name of the operations

Description

Group

Group together the values of a field.

Sum

Find the sum a grouping of records. Only for a field of numeric or monetary type.

Average

Find the average of a grouping of records. Only for a field of numeric or monetary type.

Min

Find the smallest value for the field.

Max

Find the biggest value for a field.

Count

Count the number of records that answer the wanted criteria.

EcartType

Find the standard deviation of a grouping of records. Only for a field of numeric or monetary type.

The Var

Find the variance of a grouping of records. Only for a field of numeric or monetary type.

First

Find the first record registered in the table that answers the wanted criteria.

Last

Find the last record registered in the table that answers the wanted criteria.

Expression

Enter the own Formulas or fields calculations for a grouping.

Where

Enter criteria without that they are considered for the grouping.

Not all the operations apply to all type of fields.
For example, it is unthinkable to make(do) an average on a field of type Text.
Here is a list of the operations and on that type of fields they can apply.

Operations

Text

Memo

Number
Date/Time
Currency
AutoNumberYes/No

OLE

Sum

X

Average

X

Min

X

X

Max

X

X

Count

X

X

X

X

Écartype

X

The Var

X

The first one

X

X

X

X

The last one

X

X

X

X

9. Connect tables.

If your query requires fields that you will find on several fields, you should connect them on fields in common. It's as possible as you need the other tables to create links "indirect" between two tables. For example, in the structure of the relations between the tables that compose an invoice that one sees on the page on the relations, one notices that there are no direct relations between the table Customers and that of the inventory of the company.

But there is a relation among Customers and Facture. It there also another relation among Facture and Transition Fact-Inv and a last one between Transition Fact-Inv and Inventory. Although there is no "direct" relation among Customers and Inventory, it is always possible "to connect" them by using the other tables such as Invoice and Transition Fact-Inv.

Modify a query

After your first outline of the result of the query, it is possible that you want to change one or several elements of the query. Some possible reasons are that you had no expected result or that it misses or that there are too many fields in the query. So you maybe want to change the order of presentation of fields in the query. The next part consists shows how to insert a new column between fields, deleting a field of the field list and how to move a field in the view order in the query.

Insert a column

You can insert to insert a column between two fields into the list of the fields of the query. It is advantageous when you want to insert a field calculated between two fields that are in the query.

Click
on the column placed in the right-hand side of the column that you want to add. From
the Insert menu, select the Columns option.

Delete a column

You don't need any more a field for a query. It is easy to remove it by following the following instructions.

Click
on the column to be deleted. From
the Edit menu, select the Delete column option.

Move a column

It is also always possible to change the order of presentations of fields in the query. Before we beginthe operation, take note that it is the point of the arrow that is used to select the column of the field and to move it.

Click on the small grey box over the name of the field to be moved.

The column completely should be selected.

Place the pointer over of the grey box of the field to be moved. While pressing the left mouse button, move the field towards the left or to the right according to your needs.

The field will re-fit into the query in the place where there is a vertical bar between fields as soon as you will release the left mouse button.

Exporting data

As for tables, Access can export the result of a query towards other applications. This option is more advantageous than for tables because you can ask for the records that answer your needs. The export allows you to take advantage of opportunities that offer the other applications.

Before being able to export the data, it is first required to register(record) the query in the data base Access.

From the File menu, select the Save option.

Enter the name of your choice.Press the OK button.

When the query is recorded, it is then possible to export the result to another
file format.

After you are satisfied with results of the query, select of the File menu the Save as option.

Access a choice offers you: to save the result in a file or as a query in the
data base.

Select,
the Save as a file orin the database as option.Press
the OK button.

In
the Name of the file box, enter the name of your choice.From
the list of the Type of file, select the file format that answers
your needs.Press
the Export button.

Access will create a file in the chosen format. You can then use it to answer
your requirements.

The next Web page contains exercises to practise what you saw on this page.
Let's go! Press on the Next Page button on the navigation toolbar on
top of this page. Go ahead !