Access - Queries

Introduction
Steps to make a query
Modify a query

Insert a field
Remove a field
Move a field

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's 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's 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's 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're 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's 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.

2. Choose the type of query

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's 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's possible to erase at the same time a group of records that answer the wanted criteria.

For the moment, it's about a query of type selection. It's the one that you use to carry out searches in a data base. For the exercises number 1 until 21, it's 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're 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're "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's 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's 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's 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 everyone 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's 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's 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.

The operators, the operations as well as the expression generators are explained besides details in the exercises of the next Web page.

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

7. Execute the query

Press the button.
OR
From 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.
OR
From 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's, 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 it Category	Number of Functions(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's 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's 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.
OR
From 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's 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 AutoNumber Yes/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's 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's 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's advantageous when you want to insert a calculated field 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's 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's also always possible to change the order of presentations of fields in the query. Before we beginthe operation, take note that it's 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.

Selected column

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's first required to register(record) the query in the data base Access.

From the File menu, select the Save option.

Window to register(record) under

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

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

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

Options of protection

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 or in the database as option.
Press the OK button.

Window to register(record) under with the list of the accessible(approachable) sizes(formats).

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 !

You like what you read? Share it with your friends.

This site is hosted by 1&1.com