Microsoft Office Tutorials and References
In Depth Information
Understanding the Internal Data Model
Figure 17-7: The internal Data Model now contains the Transactions and Generators tables.
Any changes made to your tables (such as adding records, deleting records, and adding
columns) are automatically captured in the internal Data Model. No need to perform a
Build relationships for the Tables in the internal Data Model
Although your data now exists in the internal Data Model, Excel doesn’t inherently know how your
tables relate to one another. For example, your two tables have a column called Generator_ID (refer
to Figures 17-1 and 17-2). This column is the key that connects the two tables, allowing you to match
transactions with customer location.
You need to explicitly define this relationship before Excel recognizes how to handle the data in the
Data Model. Follow these steps:
1. Go to the Ribbon, click the Data tab, and select Relationships.
The Manage Relationships dialog box opens.
2. Click the New button.
The Create Relationship dialog box opens, as shown in Figure 17-8.
3. Select the tables and fields that define the relationship.
In Figure 17-8, note that the Transactions table has a Generator_ID field; it’s related to the
Generators table via the Generator_ID field.
4. Click OK to confirm the relationship.
You are returned to the Manage Relationships dialog box (see Figure 17-9). Here you can
add any additional relationships you may need. Notice that you can also delete and edit
relationships in this dialog box.