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 query1. Choose one or several tables or queries2. 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. ![]() ![]() 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.![]() ![]() ![]() ![]() ![]() ![]() ![]() 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. ![]() ![]() ![]() 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 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 queryAccess offers you six kinds of queries. Each is specialized for a certain kind of query or operation.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 fieldsAmong 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 ![]() ![]() The selected field will be added at the end of the list of fields already selected. ![]() 2. Choose from the list ![]() ![]() ![]() ![]() ![]() 3. "Drag" a field and insert it in the right place ![]() ![]() 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. ![]() 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. ![]() ![]() ![]() ![]() ![]() 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 outWhen 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. ![]() ![]() ![]() 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:
5. Hide fields from the needNormally, 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 ![]() 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 selectionSelect 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
![]() 7. Execute the query![]() ![]() OR ![]() The records that answer the criteria will be shown. ![]() OR ![]() 8. Specialized options: the functions, the expression generator and the operationsAccess 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.
![]() ![]() 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: ![]() ![]() 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. ![]() ![]() OR ![]() Here is the list of the operations and it that you can carry out by using them.
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 queryAfter 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 columnIt 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.![]() ![]() Delete a columnYou don't need any more a field for a query. It is easy to remove it by following the following instructions.![]() ![]() Move a columnIt 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.![]() The column completely should be selected. ![]() ![]() 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 dataAs 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. ![]() ![]() ![]() When the query is recorded, it is then possible to export the result to another file format. ![]() Access a choice offers you: to save the result in a file or as a query in the data base. ![]() ![]() ![]() ![]() ![]() 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 ! |
No comments:
Post a Comment