Understanding Access 2013 Databases

  • 5/15/2013
Contents

Working with data in a datasheet

Each table of data in a database has an associated datasheet. The datasheet allows you to present and interact with the data in the database. It has a series of columns across the top and a series of rows containing the data records. You can adjust these presentations by hiding, freezing, and ordering the columns without changing the underlying design of the table.

Datasheets are limited in controlling how a user can interact with the data (when compared to using a form), but they are also a key feature to understand, because you can further exploit the power of a datasheet through a form. A form can be used to present data as a datasheet and make use of a number of more sophisticated features to extend the datasheet with capabilities only available through the form’s Datasheet View.

In this exercise, you’ll work with datasheets.

Set Up

You need the MSOfficeProBlank.accdb database from the previous exercise. Open the database, if it is not already open.

  1. At the lower left of a datasheet are navigation buttons for moving to the first, next, previous, and last records. In the Navigation pane, double-click the Customers table to open the datasheet.

  2. Click New (blank) record in the set of navigation controls located in the lower-left corner of the screen (the icon has a >* symbol). This will take you to a new record.

    Figure 37.20

  3. Move to the Company Name column and enter the words: Adventure Works 2. The Record Selector will change from a star (new record) to a pencil (editing record symbol).

    Figure 37.21

  4. Click the next row, or press Shift+Enter to save your changes to the record. The Record Selector will no longer show the pencil symbol.

  5. Click the Record Selector to highlight the row, and then press the Delete key. Access will prompt you to confirm deleting the record. Click Yes to delete the record.

    6. TIP

    To edit data, move to the appropriate row/column and begin entering text. You can undo changes by pressing the Esc key or using the Undo icons on the Quick Access Toolbar.

Clean Up

Close the Customers table and retain the MSOfficeProBlank database for use in later exercises.

Sorting a datasheet

Sorting data in a datasheet means that you can very quickly locate and display a subset of data without the need to use more sophisticated methods such as queries.

In order to sort by multiple columns, you need to use drag and drop to order the sorted columns from left to right (this is the order in which columns are sorted). Individual or selected groups of columns can be sorted either in an ascending or descending sort.

In this exercise, you’ll sort data in a datasheet.

Set Up

You need the MSOfficeProBlank.accdb database from the previous exercise to complete this exercise. Open the database, if it is not already open.

  1. In the Navigation pane, double-click the Customers table to open the datasheet.

  2. Click the Country heading (scroll right to locate the column), and drag this column to the left of Company Name.

  3. Click the Country heading, and holding down the Shift key, click Company (so that both columns are highlighted).

  4. On the Home tab of the ribbon, in the Sort & Filter group, click Ascending. This will sort by Country. Then, sub-sort those results by Company Name.

    5. Figure 37.22

  5. Click the X in the upper-right corner to close the datasheet. You will be prompted to save the design changes made to the table. Click Yes (the next time you open the table, the Column order and Sorting will be remembered).

Clean Up

Retain the MSOfficeProBlank database for use in later exercises.

Filtering a datasheet

Access supports several techniques for filtering the data in a datasheet. If you click a column heading, then you can filter by specific data values. Alternatively, right-click in a field and then filter the data by a variety of matching options on the shortcut menu. This section will demonstrate these two techniques.

In this exercise, you’ll filter data.

Set Up

You need the MSOfficeProBlank.accdb database from the previous exercise to complete this exercise. Open the database, if it is not already open.

  1. In the Navigation pane, double-click the Customers table to open the datasheet.

  2. On the column heading for Country, click the right drop-down arrow in the Column Heading. Clear the (Select All) option, and select the single country France and click OK.

    3. Figure 37.23

  3. Click in the Contact Title column containing Owner in the row where the CustomerID has the value 9.

  4. Right-click and select Equals “Owner.” This will then further filter the results.

    5. Figure 37.24

  5. Close the Customers datasheet and then answer No when prompted to save the changes to the design.

TIP

You will notice when filtering data that on left side of the navigation buttons is a symbol indicating that the data is filtered. Click this button to unfilter all the results.

Clean Up

Retain the MSOfficeProBlank database for use in later exercises.

Adjusting the presentation of a datasheet

The height of rows, width of columns, and general look and feel of a datasheet can be defined for each individual datasheet. Columns can also be hidden or frozen (allowing you to scroll to the right while keeping some information in view).

In this exercise, you’ll define the look and feel of the data presented in a datasheet.

Set Up

You need the MSOfficeProBlank.accdb database from the previous exercise to complete this exercise. Open the database, if it is not already open.

  1. In the Navigation pane, double-click the Customers table to open the datasheet.

  2. To adjust the row height, point to the Record Selector (the pointer will change to a selection arrow), then move the pointer down to the horizontal grid line (still pointing at the Record Selector; the pointer will change its shape to a horizontal bar). Click, hold, and drag down to adjust the row height.

    3. Figure 37.25

  3. On the Home tab, in the Text Formatting group, at the lower right is a very small diagonal arrow. Click the arrow to display the Datasheet Formatting window.

  4. Select an Alternate Background Color and click OK.

    5. Figure 37.26

  5. Close the datasheet without saving any design changes.

Clean Up

Retain the MSOfficeProBlank database for use in later exercises.

Working with a subdatasheet

By default, every table has an automatic feature where, by using the relationships, it can expand related data in other tables. This enables you to explore the relationship between data in your tables with a minimum of effort.

In this exercise, you’ll work with relationships and subdatasheets.

Set Up

You need the MSOfficeProBlank.accdb database from the previous exercise to complete this exercise. Open the database, if it is not already open.

  1. In the Navigation pane, double-click the Customers table to open the datasheet.

  2. Click the + symbol next to Company. This will expand the relationship, showing the Orders for the Company.

  3. Click the + symbol for any Order. This expands the relationship between each Order and the Order Details.

    4. Figure 37.27

  4. Close the datasheet.

    5. TIP

    Notice that because the Order Date field is not wide enough to show the available information, a series of # symbols are displayed. To avoid this, you can grab the right edge of the column header and drag it to the right, increasing the column width.

    Figure 37.28

Clean Up

Retain the MSOfficeProBlank database for use in later exercises.

Save to your account

This chapter is from the book