Chapter 4
IN THIS CHAPTER
Sorting, or rearranging, records in a database table
Filtering records in a table to see only the records you need
Querying to collect and examine information stored in a database
Looking at different kinds of queries
Now that you’ve laid the groundwork, you can put your database through its paces and make it do what databases are meant to do: provide information of one kind or another. This chapter explains how to pester an Access database for names, addresses, dates, statistical averages, and whatnot. It shows how to sort records and filter a database table to see records of a certain kind. You also find out how to query a database to get it to yield its dark secrets and invaluable information.
Sorting rearranges records in a database table so that the records appear in alphabetical, numerical, or date order in one field. By sorting the records in a database, you can locate records faster. What’s more, being able to sort data means that you don’t have to bother about the order in which you enter records because you can always sort them later to put them in a particular order.
Records can be sorted in ascending or descending order:
Follow these steps to sort the records in a database table:
On the Home tab, click the Ascending or Descending button.
You can also right-click a field name at the top of a column and choose Sort A to Z or Sort Z to A on the shortcut menu. The menu choices change based on the type of data. For Number fields, you can sort smallest to largest and vice versa; for Date fields, choose to sort oldest to newest, or vice versa.
Filtering isolates all the records in a database table that have the same field values or nearly the same field values. Instead of all the records in the table appearing on the datasheet, only records that meet the filtering criteria appear, as shown in Figure 4-1.
The basic idea behind filtering is to choose a field value in the database table and use it as the standard for finding or excluding records. For example, you can find all the orders for a particular customer, all orders taken in the month of April, or all the orders that a particular customer placed in April. For that matter, you can filter by exclusion and see the records of all the orders in a database table not taken in April and not for a particular customer. Filtering is useful when you need to find records with specific information in a single database table.
For comparison purposes, here are shorthand descriptions of the four ways to filter a database table. All filtering operations begin in Datasheet view on the Home tab. These techniques are described in detail in the upcoming pages.
When you finish filtering a database table, use one of these techniques to “unfilter” it and see all the records in the table again:
Filtering by selection is the fastest way to filter a database table. It’s also the best way when you’re not sure what you’re looking for because you can search for partial words and phrases. Follow these steps to filter by selection:
Tell Access how to filter the records.
To find all records with the same value or text in a particular field, simply click in a field with the value or text. If you aren’t quite sure what to look for, select part of a field. For example, to find all names that start with the letters St, select St in one of the name fields.
On the Home tab, click the Selection button and choose a filtering option.
The options you see are specific to the cell you clicked or the data you selected in Step 2. For example, if you click a Last Name field that contains the name Smith, your options include Equals “Smith” and Does Not Equal “Smith.” Select an option to include or exclude records in the filter operation.
Filtering for input gives you the advantage of being able to filter for data ranges. Use this technique to isolate records that fall within a numerical or date range. Follow these steps to filter for input:
Select the field with the data you want to use for the filter operation.
To select a field, click its name along the top of the datasheet.
On the Home tab, click the Filter button.
As shown in Figure 4-2, a dialog box appears so that you can describe records that you want to filter for. You can also open this dialog box by clicking the button to the right of a field name.
Tell Access how to filter the database table.
You can choose values or describe a data range:
You can repeat Steps 2 through 4 to filter the database table even further.
Filtering by form is a sophisticated filtering method. It permits you to filter in more than one field using an OR search. For example, you can tell Access to look in the Last Name field for people named Martinez, as well as look in the City field for Martinezes who live in Los Angeles or San Francisco. Moreover, you can use comparison operators in the filter operation. Follow these steps to filter by form:
In Datasheet view, go to the Home tab, click the Advanced button, and choose Filter by Form on the drop-down list.
Only field names appear on the datasheet, as shown in Figure 4-3.
Click in a field, open its drop-down list, and enter a comparison value or select a value on the drop-down list.
You can choose a value on the drop-down list or, in Number and Currency fields, enter a comparison operator and a value. Table 4-1 explains the comparison operators.
If you want, enter more criteria for the filtering operation.
You can enter values in other fields as well as filter more than once in the same field.
Filter more than once in the same field: Select a field in which you already entered a search criterion. Then click the Or tab and either enter a comparison value or choose a value from the drop-down list.
When you click the Or tab, the search choices you made previously disappear from the screen. Don’t worry — Access remembers them on the Look For tab. You can click the Or tab again if you want to enter more criteria for Or searching.
Click the Toggle Filter button.
The results of the filtering operation appear in the datasheet.
TABLE 4-1 Comparison Operators for Filtering and Querying
Operator |
Name |
Example |
< |
Less than |
<10, any number smaller than ten |
<= |
Less than or equal to |
<=10, ten as well as any number smaller than ten |
> |
Greater than |
>10, any number larger than ten |
>= |
Greater than or equal to |
>=10, ten as well as any number equal to or larger than ten |
= |
Equal to |
=10, ten — not any other number |
<> |
Not equal to |
<>10; all numbers except ten (instead of <>, you can enter the word not) |
Between … And … |
Between |
Between 10 And 15, a number between 10 and 15 or equal to 10 or 15 |
Querying means to ask a question of a database and get an answer in the form of records that meet the query criteria. Query when you want to ask a detailed question of a database. “Who lives in Los Angeles and donated more than $500 last year?” is an example of a query. So is, “Which orders were purchased by people who live in California and therefore have to pay sales tax, and how much sales tax was charged with these orders?” A query can search for information in more than one database table. For that matter, you can query other queries for information. A query can be as sophisticated or as simple as you need it to be. In the results of the query, you can show all the fields in a database table or only a few necessary fields.
Access offers several different ways to query a database (the different techniques are described later in this chapter, in “Six Kinds of Queries”). Still, no matter which kind of query you’re dealing with, the basics of creating and running a query are the same. You start on the Create tab to build new queries. To open a query you already created, double-click its name on the Navigation pane. The following pages introduce you to queries, how to create them, and how to modify them.
To create a new query, start on the Create tab and click the Query Design or Query Wizard button.
To run a query, open the query in the Query window, go to the (Query Tools) Design tab, and click the Run button. The results of the query appear in Datasheet view.
Select a query on the Navigation pane and use these techniques to view it in Datasheet or Design view. Datasheet view shows the results of running a query. Create and modify queries in Design view.
The Query Design window (refer to Figure 4-4) is where you construct a query or retool a query you constructed already. Switch to Design view to see the Query Design window. You see this window straightaway after you click the Query Design button to construct a new query. The Query Design window is divided into halves:
To choose which database tables (as well as queries) to get information from, go to the (Query Tools) Design tab and click the Show Table button. You see the Show Table dialog box (refer to Figure 4-4). The Tables tab lists all the database tables you created for your database. Ctrl+click to select the tables you want to query and then click the Add button. To query a query, go to the Queries tab and select the query. Query a query to refine the query and squeeze yet more detailed information out of a database.
The tables and queries you choose appear in the Table pane of the Query Design window (refer to Figure 4-4). To remove a table from a query, right-click it in the Table pane and choose Remove Table on the shortcut menu.
After you choose which tables to query, the next step is to choose which fields to query from the tables you selected. The object is to list fields from the Table pane in the first row of the Design grid. Fields whose names you enter in the first row of the Design grid are the fields that produce query results, as demonstrated by Figure 4-5.
Access offers these techniques for listing field names in the first row of the Design grid:
To remove a field name from the Design grid, select it and press the Delete key or go to the (Query Tools) Design tab and click the Delete Columns button.
At the start of this chapter, “Sorting Records in a Database Table” explains what sorting is. The Sort row of the Design grid — directly underneath the Table name — contains a drop-down list. To sort the query, click the drop-down list in a field and choose Ascending or Descending to sort the results of a query on a particular field. To sort the results on more than one field, make sure that the first field to be sorted appears to the left of the other fields. Access reads the sort order from left to right.
Although a field is part of a query and is listed in the Query grid, displaying information from the field in the query results isn't always necessary. Consider the Query grid shown in Figure 4-6. The object of this query is to get a list of customers by ZIP code that ordered products in the year 2018. To that end, the query criteria cell in the Order Date field is Between #1/1/2018# And #12/31/2018#
. However, when the query results are generated, listing the precise dates when the orders shipped isn’t necessary because the object of the query is to get a list of customers by ZIP code who ordered products in 2018.
What separates a run-of-the-mill query from a supercharged query is a criterion, an expression or value you enter on the Criteria line under a field. Enter criteria on the Criteria line of the Query grid. By entering criteria, you can pinpoint records in the database with great accuracy. In Figure 4-7, the Query grid instructs Access to retrieve orders with invoices due before January 1, 2016 that charged more than $2,000 and were shipped to Massachusetts (MA), Connecticut (CT), or New York (NY).
Enter numeric criteria in Number and Currency fields when you want to isolate records with specific values. Earlier in this chapter, Table 4-1 describes comparison operators that you can use for querying and filtering. These operators are invaluable when it comes to mining a database for information. Use the greater than (>) and less than (<) operators to find values higher or lower than a target value. Use the Between operator to find values between two numbers. For example, Between 62 And 55 in a Currency field isolates records with all items that sell for between $62.00 and $55.00.
To enter a text criterion, type it in the Criteria text box. For example, to find students who attended the Ohio State University, enter Ohio State in the Criteria text box of the University field. Access places double quotation marks (“”) around the text you enter when you move the pointer out of the Criteria text box.
Wildcards and the Not operator can come in very handy when entering text criteria:
All the operators that work for numeric data (see Table 4-1, earlier in this chapter) also work for data entered in a Date field. For example, you would enter >7/31/1958 in a Birth Date field to find all people born after (greater than) July 31, 1958. You would enter Between 1/1/1920 And 12/31/1929 to retrieve data about people born in the Roaring Twenties.
Access places number signs (#) around date criteria after you enter it. You can enter dates in the following formats:
To save a query and inscribe its name forever in the Navigation pane, click the Save button on the Quick Access toolbar and enter a descriptive name in the Save As dialog box. The name you enter appears in the Queries group in the Navigation pane.
After you laboriously construct your query, take it for a test drive. To run a query:
For your pleasure and entertainment, the rest of this chapter describes six useful types of queries. Access offers a handful of other queries, but I won’t go there. Those queries are pretty complicated. If you become adept at querying, however, you’re invited to look into the Help system for advice about running the query types that aren’t explained here.
A select query is the standard kind of query, which I explain earlier in this chapter. A select query gathers information from one or more database tables and displays the information in a datasheet. A select query is the most common query, the primal query, the starting point for most other queries.
A top-value query is an easy way to find out, in a Number or Currency field, the highest or lowest values. On the Query grid, enter the name of the Number or Currency field you want to know more about; then choose Ascending in the Sort drop-down list to rank values from lowest to highest or Descending in the Sort drop-down list to rank values from highest to lowest. Finally, on the (Query Tools) Design tab, enter a value in the Return text box or choose a value on the Return drop-down list:
Similar to a top-value query, a summary query is a way of getting cumulative information about all the data in a field. In a field that stores data about sales in Kentucky, for example, you can find the average amount of each sale, the total amount of all the sales, the total number of all the sales, and other data.
To run a summary query, go to the (Query Tools) Design tab and click the Totals button. A new row called Total appears on the Query grid. Open the Total drop-down list in the field whose contents you want to summarize and choose a function. Table 4-2 describes the functions.
TABLE 4-2 Summary Query Functions
Function |
Returns |
Sum |
The total of all values in the field |
Avg |
The average of all values |
Min |
The lowest value |
Max |
The highest value |
Count |
The number of values |
StDev |
The standard deviation of the values |
Var |
The variance of the values |
First |
The first value |
Last |
The last value |
The Group By, Expression, and Where choices in the Totals drop-down list are for including fields you're not performing a function on:
A calculation query is one in which calculations are performed as part of the query. For example, you can calculate the sales tax on items sold or total the numbers in two fields in the same record. The beauty of a calculation query is that the data is recomputed each time you run the query. If the data used to make a calculation changes, so does the result of the calculation. If you were to include the calculation in a database table, you would have to recalculate the data yourself each time one of the values changed. With a calculation query, Access does the math for you.
To construct a calculation query, you create a new field in the Query grid for storing the results of the calculation; then enter a name for the field and a formula for the calculation. Follow these steps to create a calculation query:
In the Field box of a blank field, enter a name for the Calculation field and follow it with a colon.
In Figure 4-8, I entered Subtotal:. The purpose of the new Subtotal field is to multiply the Unit Price by the Quantity.
After the colon, in square brackets ([]), enter the name of a field whose data you use for the calculation.
In Figure 4-8, data from the Unit Price and Quantity fields are used in the calculation, so their names appear in square brackets: [Unit Price] and [Quantity]. Be sure to spell field names correctly so that Access can recognize them.
Complete the calculation.
How you do this depends on what kind of calculation you’re making. In Figure 4-8, I entered an asterisk (*) to multiply one field by another. The equation multiplies the values in the Unit Price and Quantity fields. You can add the data from two different fields — including calculated fields — by putting their names in brackets and joining them with a plus sign, like so: [SubTotal]+[Shipping Cost].
To run a delete query, start a new query, and on the (Query Tools) Design tab, click the Delete button. Then make as though you were running a select query but target the records you want to delete. Finally, click the Run button to run the query.
You can delete records from more than one table as long as the tables are related and you chose the Cascade Delete Related Records option in the Edit Relationships dialog box when you linked the tables. (See Chapter 2 of this minibook for advice about forging relationships between tables.)
An update query is a way to reach into a database and update records in several different tables all at one time. Update queries can be invaluable, but as with delete queries, they can have untoward consequences. Back up your database before you run an update query; then follow these steps to run it:
In the field with the data that needs updating, enter text or a value in the Update To line. You can even enter another field name in square brackets ([]).
What you enter in the Update To line replaces what’s in the field of the records you collect.
To update records in more than one table, you must have chosen the Cascade Update Related Fields option in the Edit Relationships dialog box when you linked the tables. (See the section in Chapter 2 of this minibook about forging relationships between tables.)