Accessing and Analyzing Data with Microsoft Excel (Bpg-Other)

Once you have entered data in Access or have imported or linked to external data from your Access database file, you can use Access features and tools to analyze that data. Like Excel, Access provides tools for basic sorting and filtering so that you can quickly find one or more data records and data values.

Depending on how you view your data in Access, the sorting and filtering tools may not be available. Sorting tools are available in all views except Design view; filtering tools are available only in Datasheet view and Form view. Access data forms allow all view types, while Access data tables allow all view types except Form view. If you aren’t sure which view you are using for a table or form, look at the View button on the main toolbar or click the View menu and see which view is active. To change the view, click the arrow next to the View button or select a different view from the View menu.

Simple Sorting

There are two types of Access data-sorting tasks: simple and complex. Simple sorts are similar to the operations you perform with the Excel sorting feature, but you can sort by only one data field at a time in an Access table or form. To perform a simple sort in Datasheet view or Form view, click a field name. On the Records menu, point to Sort, and then click Sort Ascending or Sort Descending. Complex sorts are accomplished by using a special Access user interface, which I’ll describe later in this section.

Your Turn

In this exercise, you will locate the sales order with the highest freight charge. Once you find the highest freight charge, you will drill into information about the sales order’s related line items using a subdatasheet.

1. If the Northwind.mdb file is not already open, start Access and open the Northwind.mdb file in the Chap05 folder.

2. In the list of tables in the Database window, double-click the Orders table to open it.

3. Click the Freight field to select the entire Freight column.

4. On the Records menu, point to Sort and then click Sort Descending. Compare your results to Figure 5-5. Order 10540 had the highest freight charge, $1,007.64.

   Figure 5-5: Orders sorted by highest freight charge.

5. On the Format menu, point to Subdatasheet and then click Expand All. The data expands, displaying related records from the Order Details table.

Although the Order Details table data is not actually part of the Orders table, displaying its data in a subdatasheet can be helpful for seeing data details in context. For more information about subdatasheets, see the Access online help or read the books about Access listed earlier in this chapter.

Filtering Data

If you need to find multiple Access data records or data values, you should apply a filter. Access has various filtering features that enable you to select records that match a value in a single field or in multiple fields, as well as custom filter conditions that you can specify.

• Use Filter By Selection to quickly find records that have a matching value in a single field, such as all records that have the value Northwest in the Region field. You can also exclude records that have a certain value. After selecting a value, point to Filter on the Records menu and then click Filter Excluding Selection.

• Use Filter By Form to find records that have multiple matching field values.

• Use Advanced Filter/Sort to apply a wider range of conditions to filter and sort records. This feature also allows you to filter and sort records at the same time.

To filter data by selection in Datasheet view or Form view, click an instance of the value that you want data records to match to be included in the results. Select all or part of the value. On the Records menu, point to Filter and then click Filter By Selection. After the matching records are displayed, you can repeat this process until you have the group of records you want.

Here are some tips for how to filter records by selection:

• To find records that match the entire value in a field, select the entire contents of the value or click inside a cell containing the value. For example, select Sales Representative in the Title field to display only records that contain Sales Representative in the Title field.

• To find records in which the value in a field starts with a specific set of characters, select the characters in an instance of that field, starting with the first character. For example, select only Sales in the Title field to display records that have a Title field containing the values Sales Representative, Sales Manager, or any other title starting with the word Sales.

• To find records in which all or any part of the value in a field contains a specific set of characters, select the characters, starting after the first character in a field. For example, select only Manager in the Title field to display records that have a Title field containing the value Sales Manager, Regional Manager, of any other title containing the word Manager.

Your Turn

In this exercise, you will use Filter By Selection to display sales orders that were made by the QUICK-Stop company through employee Janet Leverling and shipped by Federal Shipping.

1. If the Northwind.mdb file is not already open, start Access and open the Northwind.mdb file in the Chap05 folder.

2. In the Database window, double-click the Orders table to open it.

3. Look for the word QUICK-Stop anywhere in the Customer column and click it.

4. On the Records menu, point to Filter, click Filter By Selection, and compare your results to Figure 5-6. Only orders made by the QUICK-Stop company are displayed.

   Figure 5-6: Orders made by the QUICK-Stop company.

5. In the Employee column, look for a cell containing the words Leverling, Janet and click it.

6. On the Records menu, point to Filter and then click Filter By Selection.

7. In the Ship Via column, look for a cell with the words Federal Shipping and click it.

8. On the Records menu, point to Filter and then click Filter By Selection.

Through the repeated use of a simple filter, you can find quite detailed information.

To filter data by form in Datasheet view or Form view, point to Filter on the Records menu and then click Filter By Form. The records disappear, and a blank record is displayed. Enter the filter conditions in one or more fields. To add multiple sets of filter conditions, you can click the Or tab and add other filter conditions. After you have added your filter conditions, click Apply Filter/ Sort on the Filter menu. To remove the filter, click Remove Filter/Sort.

Your Turn

In this exercise, you will find all the sales orders booked by employee Margaret Peacock and shipped via Federal Shipping or Speedy Express.

1. If the Northwind.mdb file is not already open, start Access and open the Northwind.mdb file in the Chap05 folder.

2. In the Database window, double-click the Orders table to open it.

3. On the Records menu, point to Filter and then click Filter By Form.

4. Click the empty cell underneath the Employee field and type “Peacock, Margaret”.

5. Click the empty cell underneath the Ship Via field and type “Federal Shipping”. Compare your results to Figure 5-7.

   Figure 5-7: Completing the Filter By Form screen.

6. On the Filter menu, click Apply Filter/Sort and compare your results to Figure 5-8. Margaret Peacock booked 40 orders that were shipped via Federal Shipping.

   Figure 5-8: Orders placed by Margaret Peacock and shipped by Federal Shipping.

7. To add the orders that Margaret Peacock booked and that were shipped via Speedy Express, point to Filter on the Records menu and then click Filter By Form.

8. Click the Or tab.

9. Click the empty cell underneath the Employee field and type “Peacock, Margaret”.

10. Click the empty cell underneath the Ship Via field and type “Speedy Express”.

11. On the Filter menu, click Apply Filter/Sort. You now see that Margaret Peacock booked 86 orders that were shipped via Federal Shipping or Speedy Express.

Putting It Together

Frequently, you use a combination of filtering and sorting to gather the data you need to review and make decisions about. After you complete the preceding Your Turn exercise, sort the filtered data by clicking any cell in the Shipped Date column, pointing to Sort on the Records menu, and then clicking Sort Descending. The 86 records are sorted by shipped date in descending order. (The most recent shipped date is displayed at the top.)

Advanced Filter/Sort

To perform a complex filter or complex sort by using the Advanced Filter/Sort feature, point to Filter on the Records menu and then click Advanced Filter/ Sort. The records disappear, and the Advanced Filter/Sort window appears. Enter the filter conditions in the grid as appropriate. After you have added all of your filter conditions, click Apply Filter/Sort on the Filter menu. To remove the filter, click Remove Filter/Sort. The following table lists some of the filter operators and expressions you can use as filter conditions:

Your Turn

In this exercise, you will find all of the sales orders booked by employee Janet Leverling during 1997. You will then save the filter as a query for use again later.

1. If the Northwind.mdb file is not already open, start Access and open the Northwind.mdb file in the Chap05 folder.

2. In the Database window, double-click the Orders table to open it.

3. On the Records menu, point to Filter and then click Advanced Filter/Sort. Delete any existing information in the grid.

4. In the first grid column, click the Field cell, click the arrow, and then select OrderID from the list.

5. Click the Sort cell, click the arrow, and select Ascending from the list.

6. In the second grid column, click the Field cell, click the arrow, and select EmployeeID from the list.

7. Click the Criteria cell, type 3 (Janet Leverling’s employee ID), and then press Enter.

8. In the third grid column, click the Field cell, click the arrow, and select OrderDate from the list.

9. Click the Criteria cell, type Between #01/01/1997# And #12/31/ 1997#, and then press Enter. Compare your results to Figure 5-9.

10. On the Filter menu, click Apply Filter/Sort. Compare your results to Figure 5-10. Janet Leverling booked 71 orders during 1997.

    Figure 5-9: Completing the advanced filter/sort.

    Figure 5-10: Orders booked by Janet Leverling during the month of July, 1997.

11. To save the filter as a query for later reuse, on the Records menu, point to Filter and then click Advanced Filter/Sort. On the File menu, click Save As Query, type a name for the query, click OK, and then click Close.

12. To apply the filter again later, point to Objects on the View menu and then click Queries. Double-click the icon matching the name of the filter to apply it.