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 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
Access Table add subdatasheet
沒有留言:
張貼留言