MS-Access / Getting Started

---

Splitting Data into Separate Tables

Data must be consistent if analysis is to have any true value in the decisionmaking process. Duplicate data is the bane of consistent data. If an entity is changed in one place, it must be changed in every place. Would it not be more logical and efficient if you could create the name and information of a customer only once? Would it not be great to simply have some form of customer reference number instead of creating the same customer information repeatedly? Then that customer reference could send you to another list where the information is unique and written once.

This is the idea behind the relational database concept. You have separate, carefully designed, unique lists of data, and you relate them to each other by using their unique identifiers (primary keys).

Excel users may not realize it, but they have made great efforts to make the data on their spreadsheets relational. They use (or overuse) VLOOKUP or HLOOKUP to match data from separate lists that have some data field or key in common. Although much is possible with these functions, they do have their limitations. Besides, the functions are not very intuitive and are trying to solve a problem that Access was designed from the ground up to address. When Excel users use these functions to bring data from separate lists onto a single row, they are emulating a relationship of that data. The problem is that the data has not really been related; it has simply been shown how it could relate to each other in the confines of a particular spreadsheet tab. A different tab may choose to relate the data completely differently.

The problem for the analyst is that if there are relationships between the data that are consistent or even permanent, it is easier to somehow reflect this in a behind-the-scenes representation of the data. Some of the data relationships can be quite complex, and forcing the analyst to remember and manually enforce all of them detracts from analysis and increases the possibility of mistakes.

Foreign Keys

To set relationships between tables, you take a primary key field from one table and use it to relate that entity to records in another table. When the primary key is used in a different table to establish relationships, it is called a foreign key. In the TransactionMaster table, there is a Customer_Number field. This relates the records from the primary key field of the same name in the CustomerMaster table, thus making it a foreign key in the TransactionMaster table.