Add a subdatasheet

When two tables in Access 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 data in a table or query.

In this article

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

• 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 and the foreign key fields in the related tables.

You can also add a subdatasheet to any table, query, or form 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 in the property Sheet window, 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 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 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 on the specified fields.

6. Click OK to save your changes.

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