Access Table add subdatasheet

This Access tutorial explains how to add subdatasheet in Access Table.

You may also want to read:

Access Table Data Validation Property to validate data

Access create Relationships

Access Table add subdatasheet

When you have more than one Access Table, you can build a relationship between them so that when you view one Table, you can also view fields of another Table.

This tutorial explains how to build the relationship in Table Property Sheet.

Example – Access Table add subdatasheet

Assume that you have two Tables, they have a common Field [Employee ID]

Job – records employees’ job related data

Compensation – records employees’ compensation

Department Employee ID Employment Date
Account 1 1/1/2000
Admin 2 1/1/2001
Admin 3 1/1/2002
Admin 4 1/1/2003
Payroll 5 1/1/2004
HR 6 1/1/2005
Finance 7 1/1/2006


Employee ID Salary Allowance
1 10000 100
2 20000 200
3 30000 300
4 40000 400
5 50000 500
6 60000 600

Go to Design View of Job Table.

As you join two tables, you need a key for each table. Link Master Fields is the key in current table, while Link Child Fields is the key in linked table.

In the Table Properties Sheet (on the right hand side), select the Compensation Table as the Subdatasheet Name, and then type [Emplyee ID] for both Link Child Fields and Link Master Fields.


Open Job Table in DataSheet View. Now you can see a plus sign on each employee. Click on the sign to expand the Compensation Fields from Compensation Table.


In the Property Sheet of Design View of Job Table, there is a Property called Subdatasheet Expanded. Set to Yes to automatically expand linked table fields.


There is an Employee ID 7 in Job Table, but there is no Employee ID 7  in Compensation Table. As you can see in the above picture, when they key fails to join, the Compensation Fields show 0.

You should be very careful about this, because when you Left Join two Tables in Query, the non-matched data shows Null (blank) not 0.

Outbound References


Leave a Reply

Your email address will not be published.