Ways to use a subdatasheet

Access automatically creates a subdatasheet in a table that is in a one-to-one relationship, or is on the "one" side of a one-to-many relationship, when the SubdatasheetName property of the table is set to Auto. A relationship is defined by matching primary key and foreign key fields in the related tables. 

However, you can add a subdatasheet to any table, query, or form. A subdatasheet can have as its source object a table or query (specified in the SubdatasheetName property). 

Table and query subdatasheets

Both a table and a query in Datasheet view can contain either a table or query subdatasheet. If a table has a one-to-many relationship with more than one other table, you can choose which table to add as a subdatasheet by clicking the expand indicator icon (+) and selecting a table in the Insert Subdatasheet dialog box.

By default, Access does not display the foreign key or matching field(s) in the subdatasheet. You can temporarily show the foreign key or matching field(s) by using the Unhide Columns command (Format menu), but the next time you open the datasheet containing the subdatasheet, the column(s) will be hidden again. Hiding or showing these fields changes only the appearance of the subdatasheet; it does not modify any relationship or source object.

Inserting Your Own Sub-Data Sheet

You can add a subdatasheet to a table or query in Datasheet view, or modify a subdatasheet, by using the Subdatasheet command (Insert menu). You specify the matching fields between the datasheet and subdatasheet in the List Child Fields and Link Master Fields boxes in the Insert Subdatasheet dialog box. These boxes correspond to the LinkChildFields and LinkMasterFields properties of the table or query containing the subdatasheet. If you leave these boxes blank, Access displays all rows of the subdatasheet under every row in the datasheet, not just the rows in the subdatasheet that match.

Nesting subdatasheets

You can nest (up to eight levels) a subdatasheet within a subdatasheet. However, each datasheet or subdatasheet can have only one nested subdatasheet. For example, in the Northwind sample database, the Customers table can contain one Orders table subdatasheet, and the Orders table subdatasheet can contain one Orders Details subdatasheet. But the Customers table can't contain both an Orders table subdatasheet and a Salespeople query subdatasheet.

Click on the button for the next tips page.

Other Related pages at vb123 are

Exploring Your Data With Subdatasheets for Advanced Users
Consolidation Queries
Data Mining Using MS Access
Linked Tables
Using Access Filters to Graph and Explore Your Databases

External Links

A question from Garry to Luke Chung at FMS

When you mention sub-datasheets as being bad for performance, does this apply to a form that opens a table that has a subdatasheet or only when you open the table itself. (same for queries)

I guess that question extends to recordsets.

Hi Garry,

Good to hear from you. Glad to know someone's reading my stuff.

What we've found is that there's a hit for opening a table with the sub-datasheet whether it's directly as a table or in a form (datasheet or form view). Apparently, Access not only opens that table but also the subdatasheet table and links the records, so it's more work whether the user wants it or not.

I haven't seen it impact invisible things like recordsets.

Luke