Access - Queries

Introduction

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. 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 need 6. Determine the criteria of selection 7. Execute the query 8. Specialized options: grouping, expression generator 9. Connect tables 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. Click on the queries tab. Press the New button. 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. The 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. 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 recording 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 Creation mode 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 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. 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 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 Being 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 by moments 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 = <,>, <=>, = 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 It is possible to combine criteria to limit the number of records that answer the criteria. Or It is possible to combine criteria to have most possible records. Among 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 It is possible to create calculated fields that give the result of a formula that uses the contents of the fields of the recording. 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 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 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 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, it is also possible to 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 recording registered in the table that answers the wanted criteria. Last Find the last recording 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 Numeric Date/Time Monetary NuméroAuto 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 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 in seeing how inserting a new column between fields, deleting a field of the list and how to move a field in the order of fields shown in the query. Insert a column 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. 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 or in 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 !