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

 

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

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

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *