|[ LiB ]|
One of the benefits of using a database is viewing just the data you want. For instance, you don't have to search blindly for your best customer's order records while viewing all customer records. Instead, you can limit the data and show only those records belonging to your best customer. In this section, you'll learn how to retrieve just the data you want:
By creating and running queries
By searching and sorting data in a form
By using filters and criteria expressions
By editing queries when necessary
By sorting data
There are a number of ways to retrieve data. In the last section, you learned how to view and modify data in a form; the form has to retrieve data to do so, but that process is automatic. In this next section, you'll learn a bit more about controlling the data you view.
Use the search command for a specific word, number, date in a field.
The Access Find command works in most objects that display data. The process for executing and using the feature is the same for the most part: only the environment changes. From the Edit menu, choose Find. Enter the value you're searching for, as shown in Figure 6.16, and then click Find Next. Access responds by selecting the first occurrence of the value. You can click Find Next again or you can stop. Just remember that by default, the feature searches only the current field and not the entire table or all the controls in a form. You must select the entire table from the Look in control.
To search for a number or a date in a field, first select the field. Then enter the number and click the Find Next button. If you want to search a field that displays data in a particular format (for example, 12-Jul-1996 for dates) enter the date in the format and check the Search Fields As Formatted check box.
Apply a filter to a table, form.
A filter sets conditions that data must meet to be viewed . It's a great way to reduce the amount of data you see.
You can filter a form or a table in a couple of ways:
Use Filter by Selection to match a selected value.
Use Filter by Form to match to a specific value (something you enter).
These options are available as tools you can quickly click. Or you can choose Filter from the Records menu.
Figure 6.17 shows the filtered results of selecting the value ERNSH in the Customer field (column) and then clicking the Filter by Selection tool. Notice that the Navigation toolbar displays the number of matching records and identifies the results as a filtered set.
Choosing Filter by Form displays a blank form. Choose a value from one of the drop-down lists or enter a value and then click Apply Filter. You can also enter more than one value. Figure 6.18 shows the results of searching for ERNSH.
Be prepared to filter records using Filter by Form and Filter by Selection in both a table and a form.
Remove a filter from a table, form.
Once you've applied the filter and you're done viewing the results, you probably want to view all the data again. To remove a filter and view all the underlying data again, click the Remove Filter button.
In the simplest terms, a query asks a question. An Access query is an object, a graphical interface object specifically , that lets you determine what data it retrieves. For instance, you could use a query to view all the records for a specific customer that haven't been filled and shipped yet.
Create and save a single table query, two-table query using specific search criteria.
The simplest way to create a query is to use the Simple Query Wizard. To launch this wizard, choose Query from the Insert menu. In the resulting New Query dialog box, select Simple Query Wizard, and click OK.
To create a query from scratch, choose Query from the Insert menu, choose Design View in the New Query dialog box, and click OK. The resulting object is known as the query grid, shown in Figure 6.19.
To add a data source (a table) to the grid, click the Show Tables button, choose a table in the Show Tables dialog box, click Add, and then click Close. Once you have a data source, you need to specify the fields you want the query to consider and display in the results.
Add a field to the lower grid by choosing one of the following methods :
Drag it from the field list to the grid.
Choose the field from the field cell 's drop-down list.
Double-click the field in the field list.
Type the field name into a field cell.
Adding fields to the grid is the first way to limit data because you don't have to display each field in the table. Add only those fields that you need to query or display. Also, if you add a field to the query for sorting or querying purposes, you can uncheck the Show option if you don't want that field's data to show up in the query's results. The query shown previously in Figure 6.19 returns only a few fields for all the customers, as shown in Figure 6.20.
Now, suppose you want to view orders for each customer? That data isn't in the Customers table. Fortunately, you're not limited to working with a single table. With the query in design view, add a second tablethe Orders table, as shown in Figure 6.21. Then, drag the OrderDate and ShippedDate fields to the grid. The results, also shown in Figure 6.21, show when every order placed by each customer was placed and when it was shipped.
Add criteria to a query using any of the following operators: < (less than), <= (less than or equals), > (greater than), >= (greater than or equals), = (equals), <> (not equal to), And, Or.
There are other ways to limit the data a query returns. Using what's known as a criteria expression , you can return only that data that meets a specific condition. A criteria expression can be as simple as a lone value, such as a particular customer's name. Or a criteria expression can be complex. For instance, you use a criteria expression to find all the orders filled on or before a specific date or during a specific time period. To express conditions, you use the logical operators in Table 6.3.
Is less than
Is less than or equal to
Is greater than
Is greater than or equal to
Is not equal to
Meets all specified conditions
Meets any one specified condition
Edit a query by adding, removing criteria.
Adding a criterion is a simple process. With the query in design view, determine the field you want to limit and enter the appropriate expression in that field's criteria cell. For instance, to limit the previous query (refer to Figure 6.20) to a specific customer, open the query in design view and enter ERNSH in the CustomerID field's Criteria cell. Next, enter the expression Between #11-01-96# and #11-01-97# in the OrderDate field's Criteria cell, as shown in Figure 6.22. The results display records for only one customer, Ernst Handel, and only those orders placed between November 1, 1996 and November 1, 1997.
You can completely change the query's results by removing any of the criteria expressions. To do so, simply highlight the appropriate expression and press Delete.
Edit a query: add, remove, move, hide, unhide fields.
A query displays the data that meets all the conditions and makes it to the results as a datasheet. In fact, it's difficult to tell the difference between the two, but they are different. The query doesn't actually contain the data as a table does; the query is just showing the data from the underlying tables. However, most queries are dynamic in that any changes you make to the query via the datasheet make their way to the underlying table, so be careful when viewing query results. Similar to a table, the query's datasheet can be modified as follows :
To add a field, return to design view and add the field to the query's lower grid.
To remove a field, return to design view and delete the field from the query's lower grid. To do so, simply select the column and press Delete.
Move a column the same way you would in a table using the drag and drop method. (See the section "Moving a Column" for more specific instructions.)
To hide a column, open the query in design view and uncheck the Show checkbox for the column.
To unhide a hidden column, open the query in design view and check the Show checkbox for the column.
Run a query.
We've shown you the results of two queries, but we haven't told you how we displayed them. To run a query, and subsequently view its results, click the Run button (it's labeled with an exclamation point) on the Query Design toolbar.
Delete a query.
Delete a query the same way you do any other database object. In the Database window, click the Queries shortcut on the Objects bar. Select the query in question, and press the Delete key. When prompted, either confirm or cancel the action.
Save and close a query.
Many queries are used over and over. To save a query, click Save from the File menu, enter a name, and click OK. Or click the query's Close button in the title bar and name the query when prompted.
Viewing data in a meaningful order is just as important as limiting the data that you view. You need to know how to sort records by field data.
Sort data in a table, form, query output, in ascending , descending numeric, alphabetic order.
You can sort records in a table, a form, or even a query. Data can be sorted in ascending or descending order, alphabetically and numerically . Here's how to sort in a table or form:
Click anywhere in the field by which you want to sort and click one of the Quick Sort tools: Sort Ascending or Sort Descending . Access sorts the entire table by the contents of the selected field.
Click anywhere in the field by which you want to sort and choose Sort from the Records menu. Then, choose Sort Ascending or Sort Descending from the resulting submenu.
To sort a query's output, open the query in design view and choose one of the sort options from the appropriate field's Sort cell, as shown in Figure 6.23. You can sort by more than one field, but remember that the sort takes precedent from the left. The results show the contents of the OrderDate field sorted within the context of the CompanyName field.
|[ LiB ]|