Ascending versus descending sorts

Records can be sorted in ascending or descending order:

• Ascending order: Arranges records in alphabetical order from A to Z, numbers from smallest to largest, and dates chronologically from earliest to latest.
• Descending order: Arranges text from Z to A, numbers from largest to smallest, and dates chronologically from latest to earliest.

Sorting records

Follow these steps to sort the records in a database table:

1. In Datasheet view, click anywhere in the field by which you want to sort the records.

2. 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.

You can sort on more than one field by clicking a field and sorting it, and then clicking a second field and sorting it. Just make sure you sort the fields in reverse order. For example, to sort the database by the Employee, Customer, and Order ID fields, click in Order ID and sort it in ascending order; click in Customer and sort it in ascending order; click in Employee and sort it in ascending order. If you mess up and forget how the table is sorted, click the Remove Sort button. This button is located on the Home tab underneath the Ascending and Descending buttons.

Different ways to filter a 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.

• Filter by Selection: Select all or part of a field in the database table, click the Selection button, and choose a filtering option. Access isolates all records with the data you select. This method works best when you can’t quite decide what you’re looking for. It’s the only filtering method that permits you to look for data found in a whole field or part of a field. You can also filter data that doesn't match the selection.
• Filter for Input: Select the field you want to filter with and click the Filter button. A dialog box appears so that you can choose values in the field or enter comparison operators to describe the numeric or date values you are filtering for. With this technique, you can filter for data ranges.
• Filter by Form: Click the Advanced button and choose Filter by Form. You see a form with one drop-down list for each field in your table. From the drop-down lists, make choices to describe the records you’re looking for and then click the Toggle Filter button. This method is more flexible than the others because you can conduct OR as well as AND filtering operations. For example, you can isolate the records of people named Smith who also live in California, or the people who live in California or New York.
• Advanced Filter/Sort: Click the Advanced button and choose Advanced Filter/Sort. The Filter window opens. Drag the name of the field you want to filter onto the grid. Then choose a Sort option and enter a search criterion. This filtering technique has more in common with queries than filters. Truth be told, the Advanced Filter/Sort command is merely a way to query a single table.

After you filter a database table, you can run a second (or third or fourth) filter operation to filter the data even more and further isolate the data you are looking for.

“Unfiltering” a database table

You can tell when you’re looking at a database table that has been filtered rather than a database table with all its records because the word Filtered appears at the bottom of the window next to the Navigation buttons (refer to Figure 4-1). Moreover, the filter icon appears in the field names of columns used in the filter operation and the Toggle Filter button on the Ribbon is highlighted.

When you finish filtering a database table, use one of these techniques to “unfilter” it and see all the records in the table again:

• Click the word Filtered at the bottom of the window. You can click this word again or click the Toggle Filter button to repeat the filter operation.
• On the Home tab, click the Toggle Filter button. You can click this button again to repeat the filter operation.
• On the Home tab, click the Advanced button and choose Clear All Filters on the drop-down list. Choosing this option kills the filter operation; you can’t repeat it except by reconstructing it in the Datasheet window.

Filtering by selection

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:

1. Display the database table that needs filtering in Datasheet view.

2. 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.

3. 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

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:

1. Display the database table that you want to filter in Datasheet view.

2. 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.

3. 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.

4. Tell Access how to filter the database table.

   You can choose values or describe a data range:

   • Choose values in the field: The dialog box lists all values in the field. Deselect the Select All check box and then click to select the values you want to filter with.
   • Describe a numeric or date range: Choose Number Filters or Date Filters and select a comparison operator on the submenu (refer to Figure 4-2). The Custom Filter dialog box appears. Enter a comparison value and click OK.

You can repeat Steps 2 through 4 to filter the database table even further.

Filtering by form

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:

1. 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.

2. 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.

3. 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.

   • Enter values in other fields: Open the drop-down list in a field and enter a comparison value or select a value on the drop-down list.

   • 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.

4. Click the Toggle Filter button.

   The results of the filtering operation appear in the datasheet.

Creating a new query

To create a new query, start on the Create tab and click the Query Design or Query Wizard button.

• Create the query in Design view. Click the Query Design button to see the Query Design window, as shown in Figure 4-4, as well as the Show Table dialog box for telling Access which database tables to query. Construct your query in the Design window (the following pages explain how).
• Create the query with a wizard. Click the Query Wizard button to display the New Query dialog box and then choose a wizard option (four possible Query Wizards are available) and answer the questions that the Query Wizard asks. You’re asked which table or tables to query, which fields to include in the query, and which fields to include in the query results (the following pages explain these issues).

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.

Viewing queries in Datasheet and Design views

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.

• Opening in Design view: Right-click the query’s name in the Navigation pane and choose Design View on the shortcut menu.
• Opening in Datasheet view: On the Navigation pane, double-click the query’s name or right-click its name and choose Open on the shortcut menu.
• Switching between views with the View button: On the Home tab, open the drop-down list on the View button. Then choose Design View or Datasheet View on the drop-down list.
• Switching between views on the status bar: Click the Datasheet View or Design View button on the right side of the status bar.
• Switching between views by right-clicking: Right-click the query’s title bar and choose Datasheet View or Design View.

Finding your way around the Query Design window

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:

• Table pane: Lists the database tables you’re querying as well as the fields in each table. You can drag the tables to new locations or drag a table border to change its size and view more fields.
• Design grid: Lists which fields to query from the tables, how to sort the query results, which fields to show in the query results, and criteria for locating records in fields.

Choosing which database tables to query

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.

To query more than one table, you need to establish relationships among tables. (Chapter 2 of this minibook has information about establishing relationships among database tables.) So-called join lines in the Query Design window show how the tables are related to one another.

If you haven’t defined relationships between the tables, you can still join them together by dragging a field from one table onto a field in another table. This is the same method used to create relationships between tables. Joining tables in a query doesn’t create an actual relationship; it’s just a temporary join for the sake of the query.

Choosing which fields to query

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:

• Dragging a field name: Drag a field name into a column on the Design grid. The field name appears on the grid, as does the name of the table that you drag the field name from.
• Double-clicking a field name: Double-click a field name to place it in the next available column in the Design grid.
• Choosing a table and field name: Click in the Table row, open the drop-down list, and choose the name of a table. Then, in the Field box directly above, open the drop-down list and choose a field name.
• Selecting all the fields in a table: In the unlikely event that you want all the fields from a table to appear in the query results, either double-click the asterisk (*) at the top of the list of field names or drag the asterisk into the Design grid. Access places the name of the table followed by an asterisk in the Field text box. The asterisk signifies that all the fields from the table are included in the query.

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.

Sorting the query results

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.

Choosing which fields appear in query results

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.

Whether a field’s Show check box is selected in the Query grid determines whether its results are part of the query results. Deselect the Show box if a field is necessary for producing records in a query but not necessary in the query results. By the way, after you save and reopen a query, Access moves deselected Show fields to the right side of the Query grid, where you usually have to scroll to see them.

Entering criteria for a query

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).

As Figure 4-7 shows, Access places double quotation marks (“”) around text criteria and number signs (#) around date criteria. When you enter text or date criteria, don’t enter the double quotation marks or number signs. Access enters them for you.

When you need help writing an expression for a query, try clicking the Builder button to construct your query in the Expression Builder dialog box. This button is located on the (Query Tools) Design tab.

At last — saving and running a query

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:

• Starting from the Query Design window: Click the Run button on the (Query Tools) Design tab.
• Starting from the Navigation pane: Double-click an existing query’s name, or right-click its name and choose Open on the shortcut menu.

Select query

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.

Top-value query

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:

• Highest or lowest by percentage: Enter or choose a percentage to find, for example, the highest or lowest 25 percent of the values. To enter a percentage, type a percent sign (%) after your entry and press the Enter key.
• Highest or lowest by ranking number: Enter or choose a number to find, for example, the top-ten or lowest-ten values. Press the Enter key after you enter a number.

This may seem counterintuitive, but to see the top values, you have to sort the field you’re ranking in descending order. For example, if you sort employees by number of sales in descending order, the employees with the top sales appear at the top. To see the bottom values, sort in ascending order.

Summary query

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.

The Group By, Expression, and Where choices in the Totals drop-down list are for including fields you're not performing a function on:

• Group By: For choosing which fields to show totals for
• Expression: For creating a calculated field
• Where: For setting criteria (you can’t include the field in the query)

Calculation query

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:

1. Create a query as you normally would and be sure to include the fields you want to use for calculation purposes in the Query grid.

2. 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.

3. 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.

4. 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].

Sometimes the results of the query aren’t formatted correctly on the datasheet. To assign a new format to a field that you create for the purposes of making a calculation query, right-click the field on the Query grid and choose Properties. The Property Sheet appears. On the General tab, click the Format drop-down list and choose the correct format for your new, hand-crafted field.

Delete query

Be careful about running delete queries. A delete query deletes records and doesn’t give you the opportunity to get the records back if you change your mind about deleting them. If used skillfully, however, a delete query is a great way to purge records from more than one database table at one time. Back up your database file before running a delete query.

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.)

To preview the records that will be deleted before you run the delete query, switch to Datasheet view (click the View button). Those records you see? The delete query will delete them if you click the Run button.

Update query

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:

1. Starting in Design view, go to the (Query Tools) Design tab and click the Update button.

2. 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.

3. Click the Run button.

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.)