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

Job
DepartmentEmployee IDEmployment Date
Account11/1/2000
Admin21/1/2001
Admin31/1/2002
Admin41/1/2003
Payroll51/1/2004
HR61/1/2005
Finance71/1/2006

 

Compensation
Employee IDSalaryAllowance
110000100
220000200
330000300
440000400
550000500
660000600

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.

access_subdatasheet

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.

access_subdatasheet_02

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.

access_subdatasheet_03

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

https://support.office.com/en-us/article/Add-a-subdatasheet-900d1d30-5ea2-4e47-80a7-ddcb87a53d14?CorrelationId=1b9ef07b-2de6-4dcb-9ab6-57d8e9469965&ui=en-US&rs=en-US&ad=US