Page 641 - Excel 2013 All-in-One For Dummies

When importing data from such external sources into your Excel work- sheets, you may well be dealing with data stored in multiple related tables all stored in the database (what is referred to in Excel 2013 as a Data Model). The relationship between different tables in the same database is based on a common field (column) that occurs in each related data table, which is officially known as a key field, but in Excel is generally as known as a lookup column. When relating tables on a common key field, in at least one table, the records for that field must all be unique with no duplicates, such as Clients data table where the Customer ID field is unique and assigned only once (where it’s known as the primary key). In the other related data table, the common field (known as the foreign key) may or may not be unique as in an Orders data table where entries in its Customer ID may not all be unique, as it’s quite permissible (even desirable) to have the same client purchasing multiple products multiple times.
There’s only other thing to keep in mind when working with related data tables and that is the type of relationship that exists between the two tables. There are two types of relationships supported in an Excel Data Model:
✦ One-to-onerelationshipwheretheentriesinboththeprimaryandforeign key fields are totally unique such as a relationship between a Clients data list and Discount data list where the Customer ID field occurs only once in each table (as each client has only one discount percentage assigned)
✦ One-to-many relationship where duplicate entries in the foreign key field are allowed and even expected as in a relationship between a Clients data list and an Orders data list where the Customer ID field may occur multiple times (as the client makes multiple purchases)
Most of the time Excel 2013 is able to figure out the relationship between the data tables you import. However, if Excel should ever get it wrong or your tables contain more than one common field that could possibly serve as the key, you can manually define the proper relationship. Simply select the Relationships button in the Data Tools group on the Ribbon’s Data tab (Alt+AA) to open the Manage Relationships dialog box. There you click New to open the Create Relationship dialog box, where you define the common field in each of the two related data tables. After creating this relationship, you can use any of the fields in either of the two related tables in reports that you prepare or pivot tables that you create. (See Book VII, Chapter 2 for details on creating and using pivot tables.)
To import external data, you select the Get External Data command button on the Data tab (Alt+AZX). When you do this, Excel displays a menu with the following choices:
✦ From Access to import database tables saved in Microsoft Access 2103
✦ From Web to perform a web page query to import data lists from web pages on the Internet
External Data Query 623
Book VI Chapter 2
Filtering and Querying a Data List