Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

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

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.

shows relationship between two tables

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.

shows datasheet with open subdatasheet

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.

subdatsheets

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

    selecting the primary field for subdatasheet

    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

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×