Making Multiple Table Databases

If all you need is a simple, one table database life is simple. All of your customers contact information is neatly arranged and can be sorted however you want. When things get a bit more sophisticated managing the backend gets to be more complicated. Sure, that seemingly innocuous customer contact database is a good start but let's say that your friends in marketing have a brilliant idea. They want to link to their information about what campaigns are successful and which aren't. Client by client. Once you are done cursing their names for throwing on extra work (and they do deserve it) we'll get down to business.

Just one important note before you begin: this piece assumes that the people in your marketing department are slackers and they expect you to input the data. If however marketing has a database with that information, check out my article on linking to another database. Trust me; you'll be glad you saved yourself the typing.

Hurdle one: Making the new table.

Step 1: Open your database and save a new copy. There is no point in messing up your pristine original. To do this click on "File" (the Windows logo) then "Save as." Rename your file and click on "ok".

Hurdle two: Adding a new table to the database.

Step 1: Click on the "create" tab.

Step 2: Click on the "table design" icon (it looks like a table).

Step 3: Fill in the field names for the table.

Step 4: Choose the data type from the pull down list.

Step 5: Click on the "property sheet" icon and fill it in.

Step 6: Set your primary key by clicking on that field and then the icon that looks like a big key.

Step 7: Save your database.

Hurdle three: Linking your tables (In Access 07 they call this "creating relationships." Almost sounds friendly, doesn't it?)

Step 1: Click on the "database tools" tab and look for "show/hide."
Here you will see the word "relationships." Go ahead and click on it.

Step 2: A new Window will open with all of your tables. Select ones you are going to use and then click on "add." Repeat this process with each table you need to add. When you are done click on "close."

Step 3: Choose the fields you want to link. This will vary depending on why you are linking. In this example we would be linking the customer name in the contact table to the name in the marketing table. Once you have chosen, drag the field from one table over to the other. This will make a new Window appear.

Step 4: Click on create.

There. Now you are linking like a pro. Go show marketing and be sure that they know just how indispensable you are!

Note: In this box you will see the words "Enforce Referential Integrity" with a check box next to it. Don't click this box lightly. It is designed to keep your database from getting "orphan" fields so if you check that box you will block the ability to delete fields in the tables you are linking.