Home > Sample chapters > Microsoft Office > Access

Understanding Access 2013 Databases

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.

  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.

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

  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.

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.

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

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.

  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. 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. Close the Customers datasheet and then answer No when prompted to save the changes to the design.

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.

  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. 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. Close the datasheet without saving any design changes.

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.

  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. Close the datasheet.