Office 2016 For Dummies (2016)

Part VI

Storing Stuff in Access

Chapter 17

Searching, Sorting, and Querying a Database

In This Chapter

Searching and filtering a database

Sorting databases

Creating and using queries

If you need to find a specific name in your database, searching through the database alphabetically may be tedious but possible. However, if you need to find the names of everyone in California who ordered more than $50,000 worth of supplies in the past three months, trying to find this information yourself would prove tedious and time-consuming. Yet, Access can search for this information at the blink of an eye.

If you search for specific types of data on a regular basis, you probably don’t want to keep telling Access what to search for over and over again. To simplify this, you can create a query. A query lets you define specific ways to search your data and save those parameters so you can retrieve data instantly the next time you need it.

Besides searching through data, Access can also sort data. Sorting can be as simple as organizing names alphabetically, or it can be more complicated, such as sorting names according to zip code, annual salary, and alphabetically by last name. Sorting simply rearranges your data so you can study it from a new point of view.

By searching, sorting, and querying your data, you can extract useful information about your data.

Searching a Database

A paper database is useful for storing information, but not so useful for finding it again. If you have a thousand business cards stored in a Rolodex file, how much time do you want to waste trying to find the phone number of a single person?

Searching a database is crucial to make your data useful, so Access provides two ways to search a database:

· Search for a specific record.

· Use a filter to show one or more records that meet a specific criterion.

Searching for a specific record

The simplest type of search looks for a specific record. To search for a record, you need to know the data stored in at least one of its fields, such as a phone number or e-mail address.

The more information you already know, the more likely Access will find the one record you want. If you search for all records that contain the first name Bill, Access could find dozens of records. If you just search for all records that contain the first name Bill, the last nameJohnson, and a state address of Alaska, Access will likely find just the record you want.

To search for a specific record in a database table, follow these steps:

1. In the All Access Objects pane on the left of the screen, double-click the name of the database table you want to search.

Access displays the Datasheet view of your database.

2. Click the Home tab.

3. Click the Find icon in the Find group.

The Find and Replace dialog box appears, as shown in Figure 17-1.

4. Click in the Find What text box and type in the data you know is stored in the record you want to find.

For example, if you want to find the phone number of a person but you know only that person’s last name, you type that person’s last name in the Find What text box.

5. Click the Look In list box and choose Current field or Current document (searches in all fields).

6. (Optional) Click in the Match list box and choose one of the following:

· Any Part of Field: The Find What text can appear in any part of a field.

· Whole Field: The Find What text is the only text stored in a field.

· Start of Field: The Find What text can be only at the beginning of a field.

7. (Optional) Click in the Search list box and choose one of the following:

· Up: Searches from the record where the cursor appears, up to the beginning of the database table

· Down: Searches from the record where the cursor appears, down to the end of the database table

· All: Searches the entire database table

8. Click Find Next.

Access highlights the field where it finds the text you typed in Step 4.

9. Repeat Step 8 to search for more records that may contain the text you typed in Step 4.

10. Click Cancel or the Close button.

Figure 17-1: Search for a specific record in a database table.

Filtering a database

Searching a database is easy but somewhat limited because you can retrieve only a record that matches any text that you want to find. If you want to find multiple records, you can use a filter.

A filter lets you tell Access to display only those records that meet certain criteria, such all records that contain people who earn more than $200,000 a year, are currently married, live in Las Vegas, Nevada, and own two or more cats.

To filter a database table, you must tell Access which field to use as a filter, and then you must define the criteria for that filter. For example, if you want to filter your database table to see only those records listing the names of people who are at least 65, you filter the Age field and set the criterion to Greater than or equal to 65.

Filtering simply hides all records in a database table that don’t match your criteria. Filtering doesn’t delete or erase any records.

Using an exact match for a filter

The simplest filtering criterion searches for an exact match. When you filter a field by an exact match, you’re telling Access, “I want to see only those records that contain this specific chunk of data in this particular field.” By using an exact match filter, you can display only those records that contain CA in the State field.

To filter a database table, follow these steps:

1. In the All Access Objects pane on the left of the screen, double-click the name of the database table you want to filter.

Access displays the Datasheet view of your database.

2. Click the Home tab.

3. Click in the field (column) that you want to use as a filter.

4. Click the Filter icon in the Sort & Filter group.

A pop-up menu appears. You can either

· Select or clear check boxes from this menu.

· Continue with Steps 5 through 7 for more flexibility.

5. Choose Text Filters.

A submenu appears, as shown in Figure 17-2.

6. Choose a filter option, such as Equals, Begins With, or Contains.

A Custom Filter dialog box appears, as shown in Figure 17-3.

7. Type the data you want to find and click OK.

Access displays your filtered data; there’s a filter icon in the column heading. Access remembers your filter settings. If you want to clear the filter, click on the filter icon in the column heading; when a popup menu appears, choose Clear filter, as shown in Figure 17-4.

Figure 17-2: The Filter pop-up menu lets you specify the criteria for a specific field.

Figure 17-3: The Custom Filter dialog box lets you specify the criteria for a specific field.

Figure 17-4: You can clear a filter from a column heading.

You can click the Toggle Filter on the Home tab in the Sort & Filter group to view all the data in your database table.

Filtering by form

One problem with defining filters in Datasheet view is that you have all your database table records cluttering the screen. To avoid this problem, Access lets you define filters by using a form, which basically displays an empty record so you can click the fields that you want to use to filter your database table.

To define a filter by form, follow these steps:

1. In the All Access Objects pane on the left of the screen, double-click the name of the database table that you want to filter.

Access displays the Datasheet view of your database.

2. Click the Home tab.

3. Click the Advanced icon in the Sort & Filter group.

A pull-down menu appears.

4. Choose Filter By Form, as shown in Figure 17-5.

Access displays a blank record.

5. Click in any field, then type the data you want to filter such as a last name.

6. Click the Advanced icon in the Sort & Filter group.

A pull-down menu appears.

7. Click Apply Filter/Sort.

Access displays a filtered view of your database table.

Figure 17-5: The Advanced pop-up menu lets you specify the criteria for a specific field.

You can click the Toggle Filter icon again to view all the data in your database table.

Using a filter criteria

Searching for an exact match in a field can be handy, but sometimes you may want to see records that meet certain criteria, such as finding the names of everyone whose salary is greater than $50,000 a year. Instead of filtering by an exact match, you have to define the filter criteria.

The type of data stored in each field determines the type of criteria you can create. Three common types of data stored in fields include Text, Numbers, and Dates, which you can filter in different ways, as shown in Tables 17-1, 17-2, and 17-3.

Table 17-1 Common Criteria for Filtering Text Data

Table 17-2 Common Criteria for Filtering Numeric Data

Table 17-3 Common Criteria for Filtering Dates

To create the filter criteria, follow these steps:

1. In the All Access Objects pane on the left of the screen, double-click the name of the database table you want to filter.

Access displays the Datasheet view of your database.

2. Click the Home tab.

3. Click in the field (column) that you want to use as a filter.

4. Click the Filter icon in the Sort & Filter group.

A pop-up menu appears (refer to Figure 17-2).

5. Select the Filters option, such as Text Filters or Number Filters.

A submenu of filter options appears, as shown in Figure 17-6.

6. Click a filter option, such as Between or Less Than.

The Custom Filter dialog box appears, as shown in Figure 17-7. The Custom Filter dialog box contains the name of your filter option, such as Between Numbers or Less Than.

7. Type in one or more values in each text box displayed in the Custom Filter dialog box and then click OK.

Access filters your database table according to your criteria.

8. Repeat Steps 5 through 7 for each additional filter you want to add.

Figure 17-6: The Filter pop-up menu lets you specify the criteria for a specific field.

Figure 17-7: Type in a value for your filter criteria.

You can click the Toggle Filter icon again to view all the data in your database table.

Clearing a filter

When you apply a filter to a database table, you see only those records that match that filter. Access displays a Filtered message at the bottom of the screen to let you know when you’re looking at a filtered database table.

To remove a filter so you can see all the records, choose one of the following:

· Click the Toggle Filter icon in the Sort & Filter group.

· Click the Filtered or Unfiltered button on the status bar near the bottom of the screen.

Access temporarily turns off any filters so you can see all the information stored in your database table.

When you choose the Save command (Ctrl+S) to save a database table, Access also saves your last filter. The next time you open that database table, you’ll be able to use the last filter you created. If you want to save multiple filters, you’ll have to save them as a query (see the section “Querying a Database” later in this chapter).

Sorting a Database

Sorting simply rearranges how Access displays your information. Sorting can be especially handy for rearranging your records alphabetically by last name, by state, or by country. You can also sort data numerically so that customers who buy the most from you appear at the top of your database table, while customers who don’t buy as much appear near the bottom.

To sort a database table, follow these steps:

1. In the All Access Objects pane on the left of the screen, double-click the name of the database table you want to sort.

Access displays the Datasheet view of your database.

2. Click the Home tab.

3. Click in a field (column) that you want to use for sorting.

4. Click the Ascending or Descending icon in the Sort & Filter group.

Access sorts your records and displays an arrow pointing up (Ascending) or down (Descending) in the field name so you know you’re looking at a sorted list, as shown in Figure 17-8.

5. Click the Remove Sort icon in the Sort & Filter group when you don’t want to view your sorted database table any more.

Figure 17-8: The Ascending and Descending icons let you sort a database table by a specific field.

Querying a Database

One problem with sorting or filtering a database table is that you must constantly define what you want to sort or filter. In case you sort or filter your data a certain way on a regular basis, you can use a query instead.

A query is nothing more than a saved version of your sort or filter criteria. By saving the particular sort or filter criteria as a query, you can select that query by name later.

Creating a simple query

If your database table contains dozens of different fields, you may find it confusing to make sense of all your information. As an aid, a simple query strips away fields so you see only the fields containing data you want to see, such as a person’s name and phone number but not her hire date or employee number.

To create a query, follow these steps:

1. Click the Create tab.

2. Click the Query Wizard icon in the Queries group.

The New Query dialog box appears, as shown in Figure 17-9.

3. Click Simple Query Wizard and then click OK.

The Simple Query Wizard dialog box appears, as shown in Figure 17-10.

4. Click a field name listed in the Available Fields box and then click the > button.

Access displays your chosen field in the Selected Fields box.

5. Repeat Step 4 for each field you want to use in your query.

6. Click Next.

If any of your chosen fields contains numeric data, another dialog box appears, as shown in Figure 17-11. This dialog box asks whether you want to display a Detail (shows every record) or Summary (shows numerical information such as the total number of records found, the average value, and the minimum/maximum value) view of your data.

7. Select the Detail or Summary radio button and then click Next.

Another dialog box appears, asking you to type a descriptive name for your query.

8. Click in the text box, type a descriptive name for your query, and then click Finish.

Access displays the results of your query as a separate tab. If you add or delete information from your database, you can click this query tab to get a quick look at the results of your query without having to define everything all over again.

9. Click the File tab and then choose Save to save your query.

Access saves your query in the All Access Objects pane under the Queries category. Any time you want to view that query, just double-click it.

Figure 17-9: The New Query dialog box lets you choose a Query Wizard.

Figure 17-10: The Simple Query Wizard dialog box lets you pick the fields to use for your query.

Figure 17-11: Choose between Detail or Summary view.

Creating a crosstab query

A crosstab query lets you combine two or more fields to calculate and display a calculation based on a third field. For example, if your database contains the names of salespeople and the products they sold, you can use those two fields to create a crosstab that tells you how much each salesperson sold of each product, as shown in Figure 17-12.

Figure 17-12: A crosstab query extracts information by cross-referencing two or more fields.

To create a crosstab query, you need to identify three types of fields:

· One to three fields to identify each record (such as the First Name and Last Name fields)

· A single field to display specific data from each record (such as the Product field, which displays the actual product names like Purses, Unicorns, or Missiles)

· A crosstab field that displays a calculated result (such as Sales)

To create a crosstab query, follow these steps:

1. Click the Create tab.

2. Click the Query Wizard icon in the Queries group.

The New Query dialog box appears (refer to Figure 17-9).

3. Click the Crosstab Query Wizard and then click OK.

The Crosstab Query Wizard dialog box appears, as shown in Figure 17-13.

4. Click a database table and then click Next.

Another Crosstab Query Wizard dialog box appears that asks for between one and three fields to identify each row (record), as shown in Figure 17-14.

5. Click a field in the Available Fields box and then click the > button to move your chosen field to the Selected Fields box.

6. Repeat Step 5 for each additional field you want to include.

7. Click Next.

Another dialog box appears, asking for a single field to use to cross-tabulate data with the fields you chose in Steps 5 and 6, as shown in Figure 17-15.

8. Click a field name and then click Next.

Ideally, this field should consist of text information that lists different data, such as sales regions (East, West, North, or South) or products (Missiles, Unicorn, and so on). If you choose a field that contains numerical data, your crosstab query displays only those numbers in the column headings, which will seem meaningless. Another dialog box appears, as shown in Figure 17-16.

9. Click a field from the Fields box and then click a mathematical function that you want Access to calculate, such as Sum, Avg, or Count.

10. Click Next.

Another dialog box appears, asking for a name for your query.

11. Type a descriptive name for your query in the text box at the top of the dialog box and then click Finish.

Access displays your crosstab query, as shown in Figure 17-17.

12. Click the File tab and choose Save to save your query.

Figure 17-13: The Crosstab Query Wizard dialog box asks you to choose which database table to use.

Figure 17-14: The first step to creating a crosstab query is to choose up to three fields to identify each record.

Figure 17-15: To cross-tabulate your data, you need to choose another field.

Figure 17-16: The Crosstab Query Wizard dialog box displays a list of mathematical functions you can choose from.

Figure 17-17: The Crosstab Query can display calculations on your database information.

Creating a query that finds duplicate field data

Suppose you sell a hundred different products. How can you tell which products customers are buying the most? To find the answer to this type of question, you can search your database manually to find a Products Sold field and then count how many times each product appears.

As a simpler solution, you can create a query that finds and counts how many times duplicate data appears. To create a query to find duplicate field data, follow these steps:

1. Click the Create tab.

2. Click the Query Wizard icon in the Queries group.

The New Query dialog box appears (refer to Figure 17-9).

3. Click Find Duplicates Query Wizard and then click OK.

The Find Duplicates Query Wizard dialog box appears, asking you to choose the database table to search.

4. Click a database table and then click Next.

Another dialog box appears, asking you to choose the fields to examine for duplicate data.

5. Click a field name and then click the > button. Repeat this step for each additional field you want to search.

6. Click Next.

Another dialog box appears, asking whether you want to display any additional fields. If you choose to look for duplicate data in a Product field (Step 5) to see which products are most popular, you can display additional fields such as each salesperson’s name so you can also see who is responsible for selling the most products.

7. Click a field and click the > button. Repeat this step for each additional field you want to display.

8. Click Next.

A dialog box appears, asking whether you want to give your query a descriptive name.

9. Type a descriptive name in the top text box and then click Finish.

Access displays your query as a separate tab.

10. Click the File tab and then choose Save to save your query.

Creating an unmatched query

Access can store huge amounts of data, but the more data you store, the harder it can be to view your data. To help you organize your data, you can divide data into separate tables. One table may contain a list of customers, and a second table may contain a list of salespeople.

When you store data in separate tables, each table may share one or more common fields. For example, a table containing customers may contain a SalesPerson field that shows which salesperson deals exclusively with which customer. A second table listing salespeople can contain the Customer field (along with additional information such as each salesperson’s phone number, address, sales region, and so on).

An unmatched query examines two (or more) database tables to look for missing information. For example, you can use an unmatched query to find customers who haven’t ordered anything in the past six months, sales regions that haven’t ordered certain products, or salespeople who have not been assigned to a sales region. Basically, an unmatched query can help you find missing pieces or holes in your entire database file.

To create an unmatched query, follow these steps:

1. Click the Create tab.

2. Click the Query Wizard icon in the Queries group.

The New Query dialog box appears (refer to Figure 17-9).

3. Click Find Unmatched Query Wizard and then click OK.

The Find Unmatched Query Wizard dialog box appears, asking you to choose a database table that contains the unmatched records you want to find.

4. Click a database table and then click Next.

Another dialog box appears, asking you to choose a database table that contains at least one field that also appears in the table you chose in Step 3.

5. Click a second database table and then click Next.

Another dialog box appears, asking you to identify the field that both database tables have in common.

6. Click the common field that both database tables share.

7. Click the gray <=> button that appears between the two fields and then click Next.

A dialog box appears, asking you to identify the fields you want to display from the database table you chose in Step 4.

8. Click a field and then click the > button. Repeat this step for each additional field you want to display.

9. Click Next.

A dialog box appears, asking you to give your query a descriptive name.

10. Type a descriptive name in the text box and then click Finish.

Access displays your query results, which show you only the data in fields you selected in Step 8.

11. Click the File tab and choose Save to save your query.

Viewing, renaming, closing, and deleting queries

Each time you create and save a query, Access stores it for future use. After you create and save a query, you can add or delete data from your tables and then apply your queries on the newly modified data.

To view a query, just double-click the query name in the left pane. In case you need to rename your query to give it a better descriptive name, follow these steps:

1. Right-click the query name in the left pane.

A pull-down menu appears.

2. Choose Rename.

Access highlights the query name.

3. Type a new name and then press Enter.

Each time you view a query, it displays a tab. Eventually, you’ll probably want to get rid of a query, so to close a query, follow these steps:

1. Right-click the query name that appears in the tab.

A pull-down menu appears.

2. Choose Close.

Access closes your chosen query and removes its tab from view.

Queries can be handy, but eventually, you may no longer need a query. To delete it, follow these steps:

· Right-click a query name; when a pop-up menu appears, choose Delete.

You won’t be able to delete a query unless you close it first.

A dialog box appears, asking whether you really want to delete your query.

· Click Yes (or No).