The Power of Databases - From Word to Excel to Access by Sandra G. Zohari, AzoTek Database Solutions May 2008
Recommend this article More Articles by Sandra Zohari

In the last issue of RPCN News, Dave Young asked:  “When do I need a spreadsheet? When will it serve me better than a list?” I’d like to answer that question for you now, and also let you know when you need a database. The tips below are specific to Microsoft programs, but the concepts are universal and will apply to any program.

Word List
Let’s say I’d like to keep track of  the birthdays for my friends, so I make a list in Word and separate the name from the birthday with a tab.

Name                  B-day
Sara Brown            4/6
Mark Green            8/5
Paul Jones            12/21

I can sort this list by birthday (Tip: Highlight all rows, then select Tables, Sort, click on the “My List has a header row,” select B-day, and then select Date for the type – otherwise 12/21 will be listed first.)

I could also put this data into a Table, and add another column for how much money I spent on gifts. (Tip: Table, Convert, Text to Table)

Then I don’t need to worry about tab spacing. But what if I want to know how much I spent overall? And I would also like to know how many days I have left to shop for the gift. Now I need to move the data into Excel. (Tip: Cut and Paste from the Table into Excel, and the program will preserve your cells. Otherwise you need to convert again from within Excel.)

With the data in Excel, you can add a Total row (Tip: click in the cell for the total and then click on the summation symbol ∑. Or type in the formula =SUM(C2:C4)) and also a new column for the number of days left to shop using a formula to calculate the number of days (Tip: =B2-TODAY()).

Note how Excel automatically changes the format of the date. It also assumes that the year is the current year if you do not enter it. (Tip: You can change the format yourself using Format, Cells, Category=Date.)

Now let’s say you’re going to buy everyone T-shirts as presents this year. You want to capture shirt size and cost based on how many shirts you buy, with the cost of the shirt based on the size. You could do this:

But what if you had lots more than three friends? It would be so much easier if you just had to put in the shirt size and quantity, and the cost per shirt was taken from another table with total amount calculated automatically.

Excel can do many things, but for relational tables, your best bet is to go to Access. This will allow you to set up a pick list for the shirt size and to produce a report every month with the list of birthdays for that month. Do you want to know the recipient’s age? Put in the full birth date and you can calculate the age the person is as of the current day. Then all of this can be displayed on a nice form on which you can see all of the data for each person in one glance. But wait, how do you want that birthday list sorted? By last name? Then you’d better put the first and last name in separate fields, or you will only be able to sort on the full name. (You could create an advanced query to parse the full name, but what if some have a middle initial or “Sr.” in their name? Then it gets to be a little more complicated, so plan ahead!)

Here is a suggested table layout for your Birthday database:

Person Table
First Name
Last Name
Birth Date
Shirt Size
Shirt Quantity

Shirt Table
Shirt Size
Shirt Cost

These two tables are related to each other by the “Shirt Size” field that appears in both tables. This is what is meant by a relational database. You can also define new fields that are calculated on the fly, ones that can be included in data forms and reports.

Calculated
Age today
Age at upcoming birthday
Number of days left to shop
Total cost per person (or overall) based on number of shirts and size
Total/Projected Costs of shirts for the year

Tip: Calculate Age with this formula:

DateDiff("yyyy",[B-day],Now())+Int(Format(Now(),"mmdd")<Format([B-day],"mmdd"))

This database is still quite limiting. You probably don’t want to buy only T-shirts as birthday gifts. And you have no capability to show history with this design. You would need to start from scratch every year. You may want to be able to see what gifts you bought last year, plus how much you spent. Here’s a new Birthday database you might want to set up:

Person Table
Person ID
First Name
Last Name
Birth Date
Shirt Size
Pants Size
Dress Size
Favorite Music
Gift Ideas

Gift Table
Person ID
Year (or full date)
Gift Category code
Item Description
Item Cost
Did they like the gift?

Category Table
Gift Category code
Gift Category description

The Person ID is a unique ID that you would assign to each person in your database. It could be a sequential number, or a code, such as the person’s initials. It can then be used to set the relationship between the Person table and the Gift table. There are a number of advantages in using an ID field instead of repeating the person’s name in the Gift table. It saves space, and if the person changes her name, you only have to make the fix in the Person table, not in every record of the Gift table.

This design will allow you to produce these reports (and more):

• For every month, whose birthday is coming up, their age, sizes and favorite music; what you bought in previous years, if they liked the gift, and how much it cost. You can also report on any gift ideas that you captured over the past year.
• For prior years, by year, total gift expenditures per person, and then total per year.
• For the past year, a detailed list of gift expenditures by gift category, with totals costs by category.
• For each person (or category, or overall), chart a graph showing birthday expenditures over time.

These days, the table feature in MS Word is more robust, and does have some formulas with which you can do some functions similar to those I have showed you how to do in Excel. Excel is a powerful program, and you can accomplish many tasks in Excel that you can also perform in Access, especially with graphs and charts.

So which to choose? If most of your document is text, and you just need a little bit of tabular data, then create a table in Word. If you are working a lot with numbers and formulas on single tables, your best bet is Excel or another spreadsheet program.

But if you have more than one table with relationships between them, and/or if you would like to enter data in forms in order to simplify data entry or control what data is entered, and/or you would like to output nice looking reports, you probably want to go with a database program like Access. A full blown database application will replace many of the manual tasks you would be performing in Excel, with programmed command buttons. If you are working with more than one user who needs to enter data at the same time, then you also want a database solution.

As you can see, having the correct database solution can make your life easier. And the time that you will save will more than make up for the time, or cost, you will spend to create the database application.

(This article was originally published in the May 2008 issue of RPCN News, the newsletter of the Rochester Professional Consultants Network, Inc.)

Sandy Zohari, database guru, is the owner of AzoTek Database Solutions (www.azotekdbs.com). She has been playing with databases and custom designing features for clients for over 25 years.

Email: sandy@azotekdbs.com
Company Profile: AzoTek Database Solutions
Company URL: http://www.azotekdbs.com

Back to Columns page