When two tables have one or more fields in common, you can embed the datasheet from one table in another. An embedded datasheet, which is called a subdatasheet, is useful when you want to view and edit related or joined (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) data in a table or query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.).

---

• Introduction to subdatasheets

• Open and close a subdatasheet

• Add a subdatasheet

---

Introduction to subdatasheets

A subdatasheet is useful when you want to see the information from several data sources in a single datasheet view. For example, in the Northwind sample database, the Orders table has a one-to-many relationship (one-to-many relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of many records in the related table.) with the Order Details table.

If the Order Details table is added as a subdatasheet in the Orders table, you can view and edit data such as the products included in a specific order (each row) by opening the subdatasheet for that Order.

If you add subdatasheets to your tables, limit the use of these subdatasheets to viewing, and not editing, important business data. If you want to edit data in a table, we recommend that you use forms to edit the data instead of subdatasheets, as data entry errors are more likely to occur in datasheet view if users are not careful to scroll to the correct cell. Also note that adding a subdatasheet to a large table can adversely affect the performance of the table.

Microsoft Office Access automatically creates a subdatasheet when you create a table that matches one of the following criteria:

• The table is in a one-to-one relationship (one-to-one relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of one, and only one, record in the related table.).
• The table is the "one" side of a one-to-many relationship, where the table's SubdatasheetName property is set to Auto.

A relationship is defined by matching the primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) and the foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) fields in the related tables.

You can also add a subdatasheet to any table, query, or form (form: An Access database object on which you place controls for taking actions or for entering, displaying, and editing data in fields.) in a database. In Datasheet view, a form can display only one subdatasheet at a time.

 Top of Page

Open and close a subdatasheet

To determine whether a table, query, or form already has a subdatasheet, open the object in Datasheet view. If an expand indicator (+ sign) is present, then the table, query, or form has a subdatasheet. When the subdatasheet is open, the indicator changes to a - sign. Subdatasheets can be nested in a main subdatasheet for up to eight levels.

• To open the subdatasheet, click the plus sign next to the value for which you want to see the records.
• To close the subdatasheet, click the minus sign.

 Tip   To expand or collapse all the subdatasheets in an datasheet at the same time, on the Home tab, in the Records group, click More. Then select Subdatasheet, and click the option that you want.

You can also remove or hide a subdatasheet. For information about how to remove a subdatasheet, see the article Remove a subdatasheet.

 Tip   To hide a subdatasheet, open the table in Design View, and then on the Design tab, in the Show/Hide group, click Property Sheet. Find the Subdatasheet Name line, change it to [None], and then save the change.

 Top of Page

Add a subdatasheet

1. In Datasheet view, open the table or query where you want to add the subdatasheet.
2. On the Home tab, in the Records group, click More, point to Subdatasheet, and then click Subdatasheet.
3. In the Insert Subdatasheet dialog box, select the tab (Tables, Queries, or Both) that corresponds to the type of object that you want to insert as a subdatasheet.
4. In the Link Child Fields box, click the field that you want to use as the foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) or matching field that will provide data for the subdatasheet.
5. In the Link Master Fields box, click the field that you want to use as the primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) or matching field for the main table or query, and then click OK.

   For example, if you wanted to create a subdatasheet to view related orders for each Customer record in the Customer table, select the Orders table, in the Link Child Fields box, select Customer ID, and in the Link Master Fields box, select ID.

   

    Note    The values in the Link Child Fields and Link Master Fields boxes create a one-to-many join (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) on the specified fields.

6. To display the subdatasheet that you added to your table, query, or form, click the + sign.

    Note   The child (foreign key) field does not appear as a column of the subdatasheet. When you add a new record in the subdatasheet, Access automatically inserts the primary key value of the selected base-table.

 Top of Page