SubDatasheets In Access 2000+

Sub datasheets are one of the good end user improvements of Access 2000.   By understanding them, you can add a lot of functionality to your application.  If you open your tables in read-only mode from a command button, you can add a powerful user exploration tool easily to your application.  As shown below in figure 1, you can find out detail by drilling down on the tables.

In a subdatasheet, you can view and edit related or joined data in a table, query. In the example below,  the suburb table has a one-to-many relationship with the Location table and their are many Tasks for each location.  The diagram shows a drilldown on the tables using sub-datasheets

Figure 1 - Drilling down with subdatasheets

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

Links >>>  Home | Search | Workbench | Orders | Newsletter | Access Security | Access professionals