Creating databases with multiple tables

Whether your database holds 100 records or 100,000 records (or more), if you need to keep separate tables and relate them for maximum use of the information, you need a relational database — and that’s Access. How do you know whether your data needs to be in separate tables? Think about your data — is it very compartmentalized? Does it go off on tangents? Consider the following example and apply the concepts to your data and see if you need multiple tables for your database.

The Big Organization database

A large company has data on their customers and their orders, the products the company sells, its suppliers, and its employees. For a complex database like this one, you need multiple tables, as follows:

• One table houses the customer data — names, addresses, phone numbers, and e-mail addresses.
• A second table contains the customers’ orders, including the name of the customer who placed the order, the salesperson who handled the sale, shipping information, and the date of the order.
• A third table contains information on the products the company sells, including product numbers, supplier names, prices, and the number of items in stock.
• A fourth table contains supplier data — about the companies from which the main organization obtains its inventory of products to resell to customers. The table contains the company names, their contact person, and the address, e-mail, and phone number information to reach them.
• A fifth table contains employee data — from the date they were hired to their contact information to their job title — and also contains notes about them, sort of a summary of their resumes for reference.

Other tables exist, too — to keep a list of shipping companies and their contact information (for shipping customer orders), an expense table (for the expenses incurred in running the business), and other tables that are used with the main four tables. The need for and ways to use the main tables and these additional tables are covered later in this book, as you find out how to set up tools for data entry, look up records, and create reports that provide varying levels of detail on all the data you’ve stored.

Tip:

Because you don’t have to fill in every field for each record — in any table in the database — if you don’t have a phone number or don’t know an e-mail address, for example, it’s okay to leave those fields blank until you’ve obtained that information.

Fail to plan? Plan to fail

If you think carefully about your database, how you use your data, and what you need to know about your employees, customers, volunteers, donors, products, or projects — whatever you’re storing information about — you can plan

• How many tables you’ll need
• Which data will go into which table
• How you’ll use the tables together to get the reports you need

Of course, everyone forgets something, and plans change after a system has already been implemented. But don’t worry — Access isn’t so rigid that chaos will ensue if you begin building your tables and forget something (a field or two, an entire table). You can always add a field that you forgot (or that some bright spark just told you is needed) or add a new table after the fact. But planning ahead as thoroughly as possible is still essential.

Tip:

As part of thorough planning, sketch your planned database on paper, drawing a kind of flow chart with boxes for each table and lists of fields that you’ll have in each one. Draw arrows to show how they might be related — it’s sort of like drawing a simple family tree — and you’re well on your way to a well-planned, useful database.

Here’s a handy procedure to follow if you’re new to the process of planning a database:

1. On paper or in a word-processing document, whichever is more comfortable, type the following:

• A tentative name for your database
• A list of the pieces of information you plan on getting from that database on a daily or regular basis

2. Now, based on that information, create a new list of the actual details you could store:

List every piece of information you can possibly think of about your customers, products, ideas, cases, books, works of art, students — whatever your database pertains to. Don’t be afraid to go overboard — you can always skip some of the items in the list if they don’t turn out to be things you really need to know (or can possibly find out) about each item in your database.

3. Take the list of fields — that’s what all those pieces of information are — and start breaking them up into logical groups.

How? Think about the fields and how they work together:

• For example, if the database keeps track of a library of books, perhaps the title, publication date, publisher, ISBN (International Standard Book Number, which is unique for each book), price, and page count can be stored in one group, whereas author information, reviews, and lists of other titles by the same author or books on the same topic can be stored in another group. These groups become individual tables, creating your relational database of books.
• Figure out what’s unique about each record. As stated in the previous point, you need a field that’s unique for each record. Although Access can create a unique value for you if no unique data exists for each record in your database, it’s often best to have such a field already in place, or to create such a field yourself. Customer numbers, student numbers, Social Security numbers, book ISBNs, catalog numbers, serial numbers — anything that isn’t the same for any two records will do.

With a big list of fields and some tentative groupings of those fields at the ready, and with an idea of which field is unique for each record, you can begin figuring out how to use the data.

4. Make a list of ways you might use the data, including

• Reports you’d like to create, including a list of which fields should be included for each report
• Other ways you can use the data — labels for mailings, product labels, catalogue data, price lists, contact lists, and so on

5. List all the places your data currently resides — on slips of paper in your pocket, on cards in a box, in another program (such as Excel), or maybe through a company that sells data for marketing purposes.

With this planning done, you’re ready to start building your database. The particulars of that process come later in this chapter and in subsequent chapters, so don’t jump in yet. Do pat yourself on the back, though, because if you’ve read this procedure and applied even some of it to your potential database, you’re way ahead of the game, and we’re confident you’ll make good use of all that Access has to offer.