Printer Friendly

Complex data designs.

In the past few months, we've covered some of the fundamentals of Access, from importing tables and optimizing data types to creating basic queries, reports, and forms. Now we'll take a look at some more complex issues in database design and use.

You may notice as you work in Access that it doesn't take long for a database to take on a life of its own. Soon, one table isn't enough, and you're trying to figure out how to effectively connect your various data tables to get the results you need. When you create new tables or request data extracts from other databases, you need to include the key fields so the data can be connected to existing tables. Each table should have a primary key, or unique identifier, that makes it possible to connect that table to other related tables. In addition, some tables will include a foreign key to connect to the primary key in a related table.

Relationships between Tables

Tables in Access can be related in a number of ways, but we'll look at the two most frequently occurring relationships: one-to-many and many-to-many. Let's consider a common business example in which customers order products. One customer may place many orders, but each order comes from only one customer. This is a one-to-many relationship. On the other hand, each order can contain many products, and each product can be purchased on many orders. We refer to this as a many-to-many relationship. Figure 1 presents a database schema that illustrates how the tables can be connected using primary and foreign keys.


One-to-Many Relationships

The one-to-many relationship is the main type that can be represented in a database, and the ability to establish this kind of relationship is a major reason for why you would use a database instead of a spreadsheet. In Figure 1, the Customer table is connected to the Order table in a one-to-many relationship using a foreign key. In this example, the foreign key in the Order table is the CustomerID field. It's the primary key in the Customer table and is used in the Order table to indicate which customer placed the order. (This also enables the Order table to be connected to all the other data about customers.) Notice that the line connecting the two tables has a 1 on the Customer table side and an [infinity], or infinity symbol, on the Order table side. This indicates that it's a one-to-many relationship. In other words, the CustomerID is unique in the Customer table--there can't be any repeats. But there's no limit to the number of times a customer ID can appear in the Order table because a single customer can place many orders.

Many-to-Many Relationships

A one-to-many relationship wouldn't work in linking the Product and Order tables. You can't simply add the ProductID to the Order table because then there could only be one product in an order. Likewise, you can't include OrderID in the Product table because then there would be only one order for each product. This is an example of a many-to-many relationship.

When tables are related in a many-to-many relationship, a bridge is created to allow for the unique combinations. Note in Figure 1 that the Item table has a primary key that consists of two fields. This type of table is called a composite table, and the primary key is called a composite key because it's made up of two fields that, together, make a primary key (unique identifier) and, separately, make foreign keys to the bridged tables. In essence, this has created three tables that are related using two one-to-many relationships (the Order table is linked to the Item table by a one-to-many relationship using the OrderID field, just as the Product table is linked to the Item table with the ProductID field).

Steps to Implementing your Database Schema

When designing database tables and relationships, it's helpful to follow these steps in order:

1. Design a schema with the names of tables, key fields (primary and foreign), and additional fields you want to store. Make sure that each tables is related to at least one other table. Avoid duplicated fields. Ideally, other than the foreign keys, each field name should exist in only one table.

2. Create each table design, and save it with a simple name.

3. Create the relationships in Access.

4. Enter data into the tables.

I will cover steps 2-4 in upcoming articles.

Technical Assistance

There are many things that can easily be done in a database by a person with an accounting or business background. If you spend a little time planning it out first, you'll find that all relationships in simple database applications can be boiled down to a one-to-many. If you're pressed for time, however, this is one area in which it may well be worth a few hours of technical assistance from a database expert. This may save you many hours in the long run. Once your tables are connected, you can use queries to pull the connected data together for reporting.

Additional Examples

The basic design concepts discussed here play themselves out in countless database examples. Many examples have been added to the website at Look under the Related Links area to see how the one-to-many principle repeats itself time and again in various designs. Microsoft also has several templates that create common tables and relationships complete with data to get you started. They may not be exactly what you want, but you may be able to create modifications using the knowledge you're gaining about Access.

Patricia Cox teaches Excel and Access to management accounting students at Alverno College in Milwaukee, Wisc., and has consulted with local area businesses to create database reporting systems since 1998. To send her a question to address in the Access column, e-mail her at

Access is a topic at IMA's Annual Conference, June 6-10, 2009, in Denver, Colo. For information, visit
COPYRIGHT 2009 Institute of Management Accountants
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2009 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Title Annotation:ACCESS
Author:Cox, Patricia
Publication:Strategic Finance
Geographic Code:1USA
Date:Apr 1, 2009
Previous Article:Creating Custom Views in Excel.
Next Article:Tools of the trade.

Related Articles
CartoPac Field Solutions.
CartoPac Field Solutions.
Data management 101: when data management is an integral part of the design environment, it works with designers rather than against them.
Dataupia adds Oracle 11g support to Satori Server.

Terms of use | Privacy policy | Copyright © 2020 Farlex, Inc. | Feedback | For webmasters