Exploring Tables

Tables are the core database objects. Their purpose is to store information. The purpose of every other database object is to interact in some manner with one or more tables. An Access database can contain thousands of tables, and the number of records each table can contain is limited more by the space available on your hard disk than by anything else.

Tip

For detailed information about Access specifications, such as the maximum size of a database or the maximum number of records in a table, search Access Help for "Access 2007 specifications" (including the quotes).

Every Access object has two or more views. For tables, the two most common views are Datasheet view, in which you can see and modify the table's data, and Design view, in which you can see and modify the table's structure. To open a table in Datasheet view, either double-click its name in the Navigation Pane, or right-click its name and then click Open. To open a table in Design view, right-click its name and then click Design View. After an object is open, you can switch between views by clicking one of the View icons in the lower-right corner of the program window, or by clicking the View arrow in the Views group on the Home tab, and then selecting a view from the list. If you simply click the View button Access switches between views in a manner that at times seems logical. If the current view is not Design view, it switches to Design view. If you click it again, the table switches to Datasheet view. When other database objects are active, clicking the View switches between views in a similar manner.

When you view a table in Datasheet view, you see the table's data in columns (fields) and rows (records).

If two tables have one or more fields in common, you can embed the datasheet from one table in another. By using an embedded datasheet, called a subdatasheet, you can see the information in more than one table at the same time. For example, you might want to embed an Orders datasheet in a Customers table so that you can see the orders each customer has placed.

In this exercise, you will open existing database tables and explore the table structures in different views.

USE the 04_Tables database. This practice file is located in the Chapter01 subfolder under SBS_Access2007.

BE SURE TO start Access and display the Getting Started window before beginning this exercise.

Tip

In this database, the Navigation Pane filter has been set to display all Access objects, but the Queries, Forms, and Reports object groups are collapsed. You can collapse and expand groups to display only the ones you want, or you can filter the database objects by clicking the list header, and then clicking the option you want under Filter By Group.

1.	Click the Microsoft Office Button, and then click Open.
2.	In the Open dialog box, browse to your Documents\MSP\SBS_Access2007\Chapter01 folder, and double-click the 04_Tables database. The database opens.
3.	In the Navigation Pane, double-click Categories. The Categories table opens in Datasheet view. This table contains a list of product categories and fields such as Category ID, Category Name, and Description. Tip You can open any database object by right-clicking it in the Navigation Pane and then clicking the view you want to open it in. Clicking Open opens the object in its default Datasheet. Tip You can resize a table column by dragging the vertical bar in the header that separates it from the column to its right. You can set the width of a column to the width of its widest entry by double-clicking the vertical bar.
4.	Maximize the table window if it isn't already maximized. Then in the datasheet, click the Expand button at the left end of the record for the Bulbs category. Expand The Bulbs category expands to reveal an embedded subdatasheet. Access displays the category records from the Categories table and product records from the Products table simultaneously.
5.	Click the Collapse button to the left of the Bulbs category to hide the subdatasheet. Collapse
6.	Click the Close Window button in the upper-right corner of the table, to the right of its tab (not the Close button in the upper-right corner of the program window) to close the Categories table. If Access prompts you to save changes to the table layout, click Yes.
7.	In the Navigation Pane, double-click the Orders table to open it in Datasheet view. The record navigation bar at the bottom of the window indicates that this table contains 87 records, and that the active record is number 1 of 87.
8.	Move through the table one record at a time by clicking the Next Record button several times. Next Record The selection moves down the OrderID field, because that field contains the insertion point. Tip You can move the selection one record at a time by pressing the Up Arrow or Down Arrow key, one screen at a time by pressing the Page Up or Page Down key, or to the first or last field in the table by pressing Ctrl+Home or Ctrl+End.
9.	Move directly to record 40 by selecting the current record number in the record navigation bar, typing 40, and then pressing .
10.	In the Navigation Pane, double-click the Products table to open it in Datasheet view. Notice that the table contains 189 records.
11.	On the View toolbar, click the Design View button. Design View Datasheet view displayed the data stored in the table, whereas Design view displays the underlying table structure.

CLOSE the Products and Orders tables without saving your changes, and then close the 04_Tables database to return to the Getting Started window.