SubdatasheetsA Product of Relationships

Did you notice the plus signs (+) to the left of each record in the Catalogs table as you worked with it? Access displays those signs in the primary key table when a relationship exists between two tables. If you open all three tables, as we've done in Figure 6.17, you'll see that the Types and Catalogs tables both have a column of plus signs, but the Plants table doesn't.

Figure 6.17. The plus signs in a table indicate that Access has related records available.


The plus sign means that the corresponding record has at least one related record in another table. If a relationship exists between two tables but no related record exists for a particular primary key value, Access displays a minus sign () instead of the plus sign.

Click the plus sign to the left of Gurney's in the Catalogs table. The result is shown in Figure 6.18. Specifically, a subdatasheet displays the records for cosmos and German Chamomile. A subdatasheet displays related values via an embedded datasheet (table).

Figure 6.18. Click a record's plus sign to display related records.


You can disable this feature if you like. To do so, follow these steps:

  1. Open the table in Design view (click the View button on the Table Datasheet toolbar).

  2. Click the Properties button on the Table Design toolbar.

  3. In the resulting dialog box, select [None] from the Subdatasheet Name property's drop-down list. The default option is [Auto], which automatically displays one-to-many related records.

If you know the table has related records, but the plus signs aren't visible, you can update the table's properties to display subdatasheets. For instance, the Plants table doesn't display subdatsheets by default because the primary key values have no related records. All the relationships are between that table's foreign key values and the other two tables. To display subdatasheets in the Plants table, do the following:

  1. Open the Plants table in Table Datasheet view.

  2. Select Subdatasheet from the Insert menu to display the Insert Subdatasheet dialog box.

  3. Select Catalogs in the Tables tab.

  4. Select Name from the Link Child Fields control.

  5. Select CatalogName from the Link Master Fields control, and click OK.

The result of this change is shown in Figure 6.19. Click any plus sign to display that record's corresponding catalog information. You don't want to display subdatasheets in the Plants table, so close the table without saving the change you just made. (Click No when Access prompts you to save your changes.)

Figure 6.19. The Plants table now displays related records in subdatasheets.


With all the extra records and fields displayed, navigating can be a bit awkward . Refer to Table 6.2 for shortcut keys for working with and navigating subdatasheets.

Table 6.2. Shortcut Keys



Ctrl+Shift+Down Arrow

Expands a record's subdatasheet

Ctrl+Shift+Up Arrow

Collapses a subdatasheet


Enters the subdatasheet from the last field of the previous record in the datasheet


Enters the subdatasheet from the first field of the following record in the datasheet


Exits the subdatasheet and moves to the first field of the next record in the datasheet


Exits the subdatasheet and moves to the last field of the previous record in the datasheet


Enters the next field in the datasheet from the last field in the subdatasheet

The Absolute Minimum

The relationships between tables are the cornerstones on which all relationship database systems thrive. As you saw in this chapter, relationships can help you find related data from one table to another. Coupled with referential integrity, they can also help protect your data from accidental changes or deletions. In this chapter, you learned how to

  • Create a lookup field and run the Table Analyzer

  • Create a relationship between two tables

  • Enforce referential integrity and why you should

  • Display subdatasheets for browsing related records

Absolute Beginner's Guide to Microsoft Office Access 2003
Absolute Beginners Guide to Microsoft Office Access 2003
ISBN: 0789729407
EAN: 2147483647
Year: 2002
Pages: 124

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: