Share this page
Access - Queries
One of the most powerful elements of a data base is the capacity to make a search on a mass of data stored in the data base. It is then possible to make analyses and to take out of it trends. For example, you don't need to see all the list of the customers if you need only the phone number of one of them. There are also queries for certain queries of action such as the update and the deletion of data.
The next section consists in learning the procedure on creating a query by creating one that will give information about the employees. It is first required to have created the table "employee" of the page of creation of tables.
If you have no 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" and to begin at once the creation of the queries. These files are on the page of the demonstrations files 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 are explanations for the possible options of a query. The following Web page consists of exercises to look more and more in depth at the possibilities of the queries.
1. Choose one or several tables or queries
The next stage consists in creating a query. It is a question of showing the First name and the name from all the employees whose First name is Roger.
on the queries tab.
1. Choose one or several tables and the required queries.
Access offers you several ways to create a query: by using one of the assistants that Access offers you or by building it from the beginning to the end in creation mode.
Creation mode 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.
For the example, select the Creation mode option.
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.
the Employees table.
Because there are the other tables or the queries to be chosen, press the Close button.
It is possible to create a query from several tables or from queries or a combination of both. If, for a reason or the other one, you forgot a table or a query, you can add it in creation mode 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 recording.
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
1. Double-click on the field
Place the pointer over the field that you need.
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:.
From the list of fields, select the field that you need.
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.
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.
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.
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:
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 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
The records that answer the criteria will be shown.
return then to the mode creation, press the button.
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.
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.
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.
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:
on the function that interests you.
A description of the function will appear in a window.
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, it is also possible to count the number of records that answer certain criteria.
reach the operations, press the button.
Here is the list of the operations and it that you can carry out by using them.
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.
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.
It is possible to 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.
on the column placed in the right-hand side of the column that you want to add.
You don't need any more a field for a query. It is easy to remove it by following the following instructions.
on the column to be deleted.
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.
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.
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.
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.
the Save as a file or in the database as option.
the Name of the file box, enter the name of your choice.
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.