Happy Table Relationships In Access 2007

A happy table relationship? While it may conjure up images of a couple clasping hands across their entrée in a restaurant, it has a more serious meaning in business - removing data that you don't need.

Surely you're thinking, well, why would I put unwanted data in my Access database anyway? The key to table relationships is to remove data you didn't know was duplicating itself. Think of any typical commercial database and it will be full of this kind of data. Let's say you have a small database of customers for a mail order business. You'll have data relating to names, addresses, products purchased and so on. However, think of other lumps of data - your mailing list (repeating the names and addresses), same with keeping tabs on customers behind on a payment (repeating everything), or even the obvious - a repeat order will create duplicate data if you're entering the person as a new customer every time, (you wouldn't remember everyone's individual order, after a while, so this is entirely possible). The answer to reducing this kind of redundant repetition comes with table relationships.

If you're a regular Access user you may already know that Access can have common fields of data - such as names and addresses - that can be called upon by other parts of the database, which removes the risk of duplicity. However, you can co-ordinate this data further with a table relationship. There are several different kinds.

One-to-many is the simplest. Taking the above example of a repeat order from a customer - you have the one (customer) and their (many) orders. The table relationship would link them up. The next type is many-to-many. Using the same example, let's say that the customer's order had more than one thing in their shopping cart. Therefore the (many) repeat orders contain (many) other items within them. Finally, there's the one-to-one. Let's say that you have a "bad debt" list - it will flag up should the (one) customer try to place an order and they have (one) strike against their name.

Access makes it relatively easy to create table relationships from your existing data. There is a Relationships window available that you can use with your database objects. You can either tell Access to join up different tables of data (e.g. names, addresses) or it has a built-in facility to create a join with one of the help wizards. Table Relationships are then visually represented with a line between the two (ore more) tables you are referring to in your query.

Once created, table relationships are quite malleable, much like the rest of your database. You can view, delete, edit or have Access provide you with a breakdown report of all the different table relationships that you currently have in your database. Some of the more commonly used ones (usually one-to-many) can be created by dragging your data field and consulting the lookup wizard - automatically making the relationship for you.

Although they seem quite complex at first, once you start with some of the automatic relationship features in Access, you will find editing and creating new ones much easier. It prevents your data from getting clogged with unnecessary repetitions, while also reinforcing the integrity of your database. Your working relationship will Office be a lot happier as a result!