Top of
the Page| Discovering Computers | Windows XP | Word 2002 | PowerPoint 2002 | Excel 2002 | Access 2002 | Site Map |
| Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 |
MS Access 2002: Querying a Database Using the Select Query Window
Queries are comprised of specific requests that are run against database tables to return only information that the user is interested in. This is one of the most powerful features in Access for allowing users to search data. One of the main reasons for queries is that there are many instances when a user doesn't need all of the data, just parts of it. The user has the capability of defining which data is important to them, determining the way the data is viewed and putting restrictions on the type of data they would like to view.
Look at the table structures in Figure 2-1 in your book. The Customer table contains many records and there may be times when you only want to view records for specific fields, such Name or ZipCode. Using the query feature in Access will let you achieve these types of results.
To create a new query, you will use the Select Query window. Before you open the Select Query window, make sure that the table you would like to run the query against is selected in the Database window. To open the Select Query window, click on the down-arrow on the New Object: Autoform button and click on Query as shown in Figure 2-2 in your book. The New Query dialog box will appear as shown in Figure 2-3 in your book.
In the beginning, the easiest way to create a query is in Design View. To create a query this way, you should select the Design View option and then click the OK button. The Select Query window will open and you will be ready to begin designing your new query.
Before you begin your work on the query, you may want to change the layout of the design area to make the design process easier. For example, Figures 2-5 and 2-6 in your book show you how to adjust the size of the upper and lower panes in the design window.
It is to your advantage to increase the size of the upper pane because there will be times when you use field lists that are long and you may need to see as much of the information as possible. There is a lot of empty space at the bottom of the design grid in the lower pane, so decreasing this in size shouldn't affect your design efforts.
Once you have started a new query, there are many things that you can do with it. One of them is to view all of the records and fields in the table that you will run the query against. The easiest way to do this is to scroll to the top of the field list for the database table in the upper pane of the Select Query window and then double-click on the asterisk as shown in Figures 2-16 and 2-17 in your book.
This action will result in Access adding all of the fields to the design grid in the lower pane. Access uses a short-cut to do this. For example, if the table you are working with is called Employees, Access will put the following in the design grid:
This short-cut means that all of the fields in the Employees table will be included in the query. As long as you don't put any type of criteria limitations on the data, Access will return all of the records in this table.
Once you have created your query, you will need to run it to display the results. From the Query Design toolbar, click on the Run button as shown in Figure 2-10 in your book.
Access should quickly return the requested data in a datasheet view (depending upon the amount of data in your table, this process could become a bit time-consuming).
When the results appear, they will look similar to how a table displays its contents. It's important to remember that even though the results of the query appear to be in a table, they really aren't. The actual data is still in the original table. All that Access has done is request the data from the table and displayed it in a tabular format that allows us to view it. It's key to understand that a query is run AGAINST a table; it does not replace a table. If the data in a table changes, then it's very likely that the results of a query will change based upon the different data in the original table.
Once you have viewed the data, you can return to the Query Design by clicking on the View button arrow on the Query Datasheet toolbar and selecting the Design View option as shown in Figure 2-13 in your book.
To print the results of a query, you will need to follow the same steps that you used when printing the contents of a table. For more information on this, please refer to the Print the contents of a table objective in Project 1.
Once you are done working with a query, you should close it. If you have not already done so, Access will ask you if you want to save the query. If you think that you will use it frequently, saving it is a good idea. To close the query without saving it, click on the Select Query window Close Window button as shown in Figure 2-14 in your book.
When Access displays the dialog box asking you to save the query, click on the No button. This will close the Select Query window without saving the query. Once the Select Query window has closed, the query will be destroyed and you will have to recreate it to run it again. To create a new query, see the Create a new query objective of this project.
If you make a mistake or decide that you would like to start the query over, you can either close the query as discussed above or you can use the Clear Grid option. While the Select Query window is open, click on the Edit menu and select the Clear Grid option as shown in Figure 2-19 in your book.
Once you have started a new query, there are many things that you can do with it. One of them is to select specific fields from the table that you will run the query against. The easiest way to do this is to scroll through the field list for the database table in the upper pane of the Select Query window and then double-click on the field name of any fields that you want included in the query as shown in Figure 2-9 in your book.
Other ways to include specific fields in a query include dragging the field from the field list to the design grid and clicking the down-arrow in the Field column of the design grid and selecting the field name. These two actions along with the one described above will result in Access adding the selected fields to the design grid in the lower pane.
As long as you don't put any type of criteria limitations on the data, Access will return all of the records in this table with the fields that you selected instead of all of the fields in the table.
There are many different ways that you can limit or restrict the data that is returned from your query. Setting the criteria equal to a specific value limits the results returned. For example, if you had a field named LastName and wanted only records returned where last name equals Smith, you would type Smith into the Criteria box in the design grid for the field LastName. Since the information in the field is text data, this is one way of using text data in a criteria for a query.
Sometimes using the whole text string, Smith, may be too restrictive. Let's say that you want to return all of the records where the last name begins with S. You can do this by using a wildcard. There are two types of wildcards available in Access:
The best way to get all of the last names that begin with an S is by using the asterisk. You would put the following into the Criteria area of the design grid:
All of the records in the table that have a last name beginning with S should be returned in the Query Datasheet view.
There are many different ways that you can limit or restrict the data that is returned from your query. Setting the criteria equal to a specific value limits the results returned. For example, if you had a field named AmountDue and wanted only records returned where amount due equals $0.00, you would type 0 into the Criteria box in the design grid for the field AmountDue. Since the information in the field is numeric data, this is one way of using numeric data in a criteria for a query.
Sometimes using the equal comparison may be too restrictive. Let's say that you want to return all of the records where the AmountDue is greater than $500.00. You can do this by using comparison operators. There are five different comparison operators available in Access in addition to the equal (=) operator:
The best way to get all of the records where the amount due is greater than $500.00 is by using the greater than operator. You would put the following into the Criteria area of the design grid:
All of the records in the table that qualify should be returned in the Query Datasheet view.
Access allows you to use multiple criteria values to restrict your search even further. This type of criteria is known as a compound criterion. There are two types of compound criteria available in Access:
There are major differences between the AND and the OR. When you use the AND criteria, each criterion MUST be true for the entire compound criteria to be true. When you use the OR criteria, any of the criterion can be true for the entire compound criteria to be true. It's very important to remember this because if these rules are not followed, the results of your query may be incorrect.
When you use the AND criteria, you MUST put the criteria on the same line in the design grid as shown in Figure 2-32 in your book. If you use the OR, the criteria must go on separate lines in the design grid as shown in Figure 2-34 in your book.
Access gives you the ability to display the resulting data returned from the query in a specific order. This is also known as sorting. You may wonder why you would want to know about sorting. This can be a very important feature depending on the type of data that you are viewing.
Suppose you want to see all of the records that are returned in your query in order by last name, with the last names being in alphabetical order from A to Z. This is made possible by using the Sort line in the design grid as shown in Figures 2-36 and 2-37 in your book.
If you click on the down-arrow, you will see three options: Ascending, Descending and (not sorted). Ascending puts the records in order from first to last, descending orders from last to first and (not sorted) does not sort.
Another sorting feature in Access is the ability to sort on multiple fields. You do this by adding the fields that you want in your query to the design grid and then selecting multiple fields to sort on as shown in Figure 2-42 in your book.
It is very important to remember that sorting begins with fields on the left of the design grid and works to the right. What this means is that if you set up a query with ten fields and three of them are sorted, the first sorted field will be whichever one is the farthest left. This first sort field is also known as the major sort key. The sort fields to the right are known as minor sort keys. It is important to remember this because the order of the fields can affect how your sort results appear when the query is run. For example, if you are sorting on State, LastName and FirstName and they appear in this order in the design grid, Access will first sort all of the records by State and then within that sort, it will sort all the LastNames within the States. Once that has been done, Access will then sort all of the FirstNames within the LastNames within the States. This can get very confusing, but as long as you remember to keep the fields in order in the design grid, you shouldn't have any trouble.
The last sorting feature we will discuss is the ability to omit duplicates from the query results. This is a very nice feature when you want to see the data in your table and you only need to see it once. For example, suppose that you would like to see which States are represented in your table. It is possible that any particular state could appear multiple times, but you only need to see it once. The way to achieve this is by changing the Unique Values property for the query. Before you do this, you need to make sure that your cursor is not in any of the occupied fields in the design grid. To avoid this, click in any field of the design grid that is not being used for the query. Once you have done that, click on the View menu and then choose the Properties option. The Query Properties sheet should appear as shown in Figure 2-40 in your book.
Click in the Unique Values property box and select the Yes value. Once you have changed this, click the Close button to close the Query Properties sheet. Only unique values should be shown when you run the query.
Sometimes you will have data that is in different tables that you need to bring together for your final result. This may be necessary for any number of reasons, such as using the data for a custom report. When you match the data in multiple tables, this is known as a join. To be able to join the tables, there MUST be a field in the first table that matches a field in the second table and so on. To better understand this, look at Figure 2-44 in your book.
You can see that the Customer table and Driver table both include the DriverNumber field. When these two tables are joined on the DriverNumber field, all of the records from the Customer table are joined with records from the Driver table. What occurs once the query is run is that the LastName and FirstName from the Driver table are included in each record. This could be very useful, especially if you want to sort the report by the drivers name.
To join tables, you need to make sure that all of the tables are visible in the query design area. To include multiple tables, right-click anywhere in the open area of the upper pane and then select the Show Table menu option. The Show Table dialog box should appear as shown in Figure 2-46 in your book.
If the Tables tab is not selected, click on it to make it active. All of the tables in your database should appear in the list. For each one that you would like to add to the query, select the table name and then click the Add button. Once you have added all the tables that you need, click the Close button.
Now that all of the tables are included, you can join them. In some cases Access recognizes the fields that should be joined and draws the join line automatically between the tables as shown in Figure 2-47 in your book.
If this does not occur, you can manually join the tables by clicking on the necessary field in the first table and then dragging it over the necessary field in the second table. Once the mouse is positioned over the field in the second table, release the mouse button. Access should draw the join line between the two tables.
Once you have joined the tables, you can perform any type of query that you wish. You can include all of the fields from all of the tables or only a few fields from each table. You also have the option of using the Criteria feature to limit the records that are returned from the query. Doing this is known as restricting the records in a join. For more information on using the Criteria features, see the Use text data and wildcards in criteria in a query objective, the Use numeric data and comparison operators in criteria objective and the Use compound criteria objective described in this project.
Calculated fields are a very powerful feature of Access. There will be times that you will need to be able to calculate amounts to use for various objects. Access lets you perform these types of calculations in queries. It is much better to perform these types of calculations in a query instead of storing it in a database. There are many reasons for this, but the main two are that Access makes it easy to use calculations in a query and storing calculations in a table could actually hurt the performance of your database.
There are many helpers that Access provides to make the creation of a calculated field easier. Look at Figure 2-54 in your book to see the Zoom dialog box.
This is a good thing to use if you know what your expression should look like and you don't need any help building it. It gives you much more room to input the calculation and is preferred over typing the calculation in the Field row. Another powerful helper is the Build dialog box. If you are not sure what your expression should look like, this will help you build the calculation by selecting the type of operation you would like to perform and choosing fields.
The layout of a calculated field is very specific. Again, look at Figure 2-54 in your book and study the expression in the box.
To the left of the colon is the name of the calculated field. This will always appear to the left of the colon. To the right are the fields that are being summed. Notice that these are enclosed by square brackets. If you do not enclose them, Access will not recognize them as valid field names and your query will not work as expected.
Once you have input your calculated field, try to run the query. If you think that the query is displaying incorrect information, switch back to design view and change the expression. Keep testing the query until you get the results that you are expecting.
Access has the ability to calculate statistics for a query. There are many types of statistics available for use, including COUNT, SUM and AVG. To use statistics, you need to include them in the Total row of the design grid. This row does not appear by default so you will need to enable it. To do this, perform one of the following methods:
Once the Total row is visible, you can use it to calculate statistics. The first thing you need to do is decide which field you would like to perform the calculation against and then add it to the design grid. Once that is done, click in the Total row and then click the down-arrow to display the list of available statistics. Scroll through them to see what is available. A partial list is shown in Figure 2-59 in your book. Once you decide on a specific statistic, try to run the query to see what happens.
When you are comfortable with how this works, you can try some of the more advanced features. If you would like to perform statistics on the Paid field and you only want to include records where the Paid amount is greater than $500.00, you can use the Total and Criteria rows to restrict the query and limit the records included in the statistic. Figures 2-62 and 2-63 in your book show this by using the Total and Criteria rows to evaluate the average for the Amount Paid field where the Driver Number field is equal to 60.
This is just one example of how you can use Access to narrow down the results of your query and statistics.
You can also calculate statistics for a group of records, which is known as grouping. Look at Figure 2-65 in your book to better understand how grouping works.
The key for grouping is the group by option, which is shown in the Total box for the Driver Number field. Group by means that Access will calculate the statistics for each group that appears in the Driver Number field in the table. Figure 2-66 in your book shows what the results of this query would look like.
There are three different driver numbers in the Driver Number field and the average for the Amount Paid field was calculated for each driver number.
Saving a query is a good idea if you will need to use it in the future. To save a query, you would perform the same steps described in the Print the answer to a query, clear the query grid and close the query objective for closing a query except that you would click the Yes button instead of the No button when asked if you would like to save the query. When you do this, the Save As dialog box will appear. You should type in a name for the query in the Query Name box and then click the OK button as shown in Figure 2-67 in your book.
This will close the Save As dialog box, save the query under the name that you typed into the Query Name box and close the Query window.
After you save the query, it will be available whenever you want to use it. To run the query, make sure that it is selected in the Database window and then right-click on it. From the short-cut menu, select the Open option. This will actually run the query and put the results into the datasheet view for a query. It is again important to mention that the query is running against the original table and will only retrieve data that matches the "questions" asked by the query. If the data in the original table has changed, the results of your query could be different each time that you run it.
Saved queries can be used for more than viewing the results in the datasheet view. You can use them as the basis of a form or report instead of the original table that the query is run against. The form/report will work exactly the same as using a table, except that you will only see the data that the query returns.
| Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 |
| Discovering Computers | Windows XP | Word 2002 | PowerPoint 2002 | Excel 2002 | Access 2002 | Site Map |
