Building a Database from Scratch

nly a few years ago you had to be unusually skilled in database technology to construct a database from scratch. In fact, many CPAs were so intimidated by the database products available that they turned instead to the more familiar spreadsheet programs, even though those number-crunchers didn’t do the job as well.

Today’s database products are easier to use and more powerful. (For more on database software, see JofA, Jan.99, “What You Better Know About Databases,” page 61.) To convince you of their friendliness and power, and as a way to encourage you to use a database, we will walk you through the steps of setting up a custom database using Microsoft Access 97. The basic steps are similar for other database products.

Although we focus on how to build a database from the ground up, many users may find a predesigned database template will serve their needs just as well. Using a template saves time and effort: You can be up and running in just a few minutes because the basic design function has been done for you. If anything, a template may require a little tweaking to fit your needs exactly. A sampling of database templates available with Access 97 is shown in exhibit 1 . You can view the full palette of templates by selecting the database wizard option displayed on Access’s opening screen.

STARTING FROM SCRATCH

The first, most important step in building a database is a user-needs analysis—that is, figuring out what information the user wants the database to supply. Although databases are quite flexible and can be adjusted as needs change, it helps to have a general goal in mind. This tutorial creates a fictional small clothing distributor seeking to expand the business. Because the distributor needs a better understanding of customers’ spending habits, this exercise will create two tables—one for customers and one for products—to track the two variables. When the data in the two tables interact, you should get a good perspective of what customers buy. For the next step—called data modeling—you must decide how to set up the data tables and establish their relationships with each other.

At this point, open Access on your computer and follow this tutorial step-by-step.

Begin by clicking on Blank Database and name the file Cust_Track. The one-word file name is a database convention. Rather than call the file Customer Track, those two words are shortened and linked by an underline dash—thus Cust_Track.

The next step is to build tables for the data; for convenience, use the wizard to speed the task. Click on the Tables tab, New, Table Wizard and OK (see exhibit 2 ,) From the Sample Tables in the left column, highlight the Customers option (notice how the choices change in the Sample Fields when you change the options).

To build the table components—customer contacts, addresses and phone numbers—move those categories in the Table Wizard in Sample Fields (Customer ID, Company Name, Contact First Name, Contact Last Name, Billing Address, City, State Or Province, Postal Code and Phone Number) to Fields in my new table by highlighting each of them and clicking on the move button.

After moving all the fields, click on Next, which produces a screen that allows you to change the name of the table. Since the prefilled name, Customers, suits our needs, leave it as is.

Now you will be asked about a primary key. As the screen instructions explain, a primary key uniquely identifies each record. Since the Customer ID field will be the primary key, leave the default as Yes, set a primary key for me and click Next.

The wizard now has all the information it needs to create the table and will allow you to enter data after selecting Enter data directly into the table and clicking on Finish. You can adjust the column widths by moving the mouse pointer between the column titles Customer ID and Company Name and double clicking on the left mouse button.

Because you set the Customer ID key as the primary key, the word AutoNumber appears in the first cell of that column. You cannot enter data there: That space is reserved for the software to automatically assign a sequential number for each new entry, thus identifying each entry.

Press the Tab key on the keyboard to move to the Company Name column and type in the name of the first customer, “Fly-By-Night Clothes,” and press the Tab or Enter key. Notice that the number 1 is now in the Customer ID column.

For the Contact First Name, type in the customer’s first name, “Mark,” and press Tab or Enter. For the Contact Last Name, type “Fly”; for Billing Address, type “111 Any Street”; for City, type “Nashville”; for State/Province, type “Tennessee”; for Postal Code, type, “11111 1111”; and for Phone Number, type “111 111 1111.”

For the Postal Code and Phone Number data, the wizard automatically applies what’s called an input mask, which sets the format (five digits, a hyphen and four digits for the Postal Code and open parenthesis, three digits, close parenthesis, three digits, a hyphen and four digits for the Phone Number).

Repeat the steps in the last three paragraphs to enter the information for the second and third records, as shown in exhibit 3 .

When finished, close the table by clicking on the bottom X button in the top right corner and on OK when you’re asked to save changes to the table layout.

Shortcut. If this information originally had been in a spreadsheet (as shown in exhibit 4 ,) you could have imported it directly into the database.

Here’s how to import spreadsheet data into Access: After creating a new Access file, Cust_Track, click on File in the toolbar on the top of the screen. Then click on Get External Data and select Import. The import screen lets you choose the format of the file to be imported. In this case, select Excel (see exhibit 5 ,) and the folder’s location should be changed to the address where the spreadsheet is stored.

The import wizard guides you through the process. Click on Next, check the box displaying first row contains column headings. For the following four screens, click on Next, change the name of the table to Customers and click on Finish.

Now that you’ve set up the database, you can adjust elements within it without changing the actual data. For example, you can rearrange the position of any column by first highlighting it (see exhibit 6 ,): Place the cursor in the field, click and drag the field to the desired location and release the mouse button. Notice a dark line appears between the columns as you drag the column: That line marks where you will move the column.

Editing data is simple, too. Should you want to place hyphens between the words “Fly by the Seat of our Pants,” simply click between “Fly” and “by” and insert a hyphen as needed; ditto with the other words.

You also can manipulate data by using the toolbar. If the toolbar is not already visible, click on View, Toolbars, Table Datasheet (see exhibit 7 .)

Data can be sorted without having to specify a range. All that is required is to click in the column of interest (for example, State Or Province) and then click on the buttons for either ascending or descending order.

Filter keys also are handy. They allow you to filter the data to specified criteria. For example, by placing the cursor in either of the cells containing Nashville and clicking the Filter By Selection button, the database will show only records where the City field is equal to “Nashville.” Similarly, you can use the Filter By Form button for filtering on more than one attribute. For instance, you can filter for both City equal to “Nashville” and Contact First Name equal to “Julie” using drop-down boxes under those headings and then activating the Filter By Form filter by clicking on the button. You also can filter for multiple selections within an attribute. An example of a multiple filter is City equal to “Nashville” and Contact First Name equal to “Julie” or “Lora.” You might use the Or option situated at the bottom of the filter form to accomplish this.

Similar to the filter buttons is the Find button, which allows a search through the database or parts of the database for specific records or parts of records. For example, say you can’t remember whether the Contact Last Name was “Fly” or “Flight.” To initiate the search, put the cursor anywhere in the Contact Last Name column and click on the Find button. Then type the first few letters—“Fl”—and select the Start of Field option of the Match selection (see exhibit 8 .) Clicking on the Find First button places the cursor in the cell of the first record where the contact’s last name begins with “Fl.” Click on Find Next if the first selection isn’t the one you’re looking for.

In addition to manipulating data, the software can enhance both accuracy and completeness of its information. By clicking on the Design button, you can see the properties available for each of the fields in a table (see exhibit 9 .)

Earlier, when the Table Wizard set the primary key (Customer ID), it wouldn’t allow duplicate ID numbers. This was accomplished by setting the Indexed field property selection to Yes (No Duplicates). Also, setting New Values to Increment acts as an automatic counter: Each time you add a new record, Customer ID rises by one. While this ensures that no duplicate Customer ID numbers will be entered, it does not stop you from entering the same company name twice. To prevent that, put the cursor in the Company Name cell (as shown in exhibit 10 ,) and then change the Indexed property from No to Yes (No Duplicates).

Another item to consider changing is the Field Size—the maximum number of characters (numbers or letters) for the field. Reduce it to 40 for Company Name. Similarly, Postal Code should be set to 10 and Phone Number to 14. Reducing the field sizes reduces unused storage space.

Accuracy and completeness of the database also can be improved. To improve completeness, change the Required property for each of the field names from No to Yes; this prevents a record from being created if any values are missing. To illustrate this point later in the article, change the Required fields for Company Name and Phone Number to “Yes.” We’ll get back to this point in a moment.

Another way to improve accuracy and save time is to fill cells with automatic values. For example, assuming you will be doing business only with Arkansas customers, set the Default Value for State Or Province to “Arkansas” (see exhibit 11 .) Should there be an exception to the default value, you can type in the correct value.

Yet another way to improve accuracy is to use the input mask feature. For example, put the pointer in the Postal Code field name cell and notice the format of items in the cell “00000\-9999.” The “0s” mean a numeric value is required and the “9s” indicate that a numeric value is optional. Since the last four digits of a ZIP code are not always known, leave the input mask as is.

PUT IT TO THE TEST

It’s a good idea to test the changes you’ve made. First, close out of the design view by clicking on the bottom X and then OK to save changes to values. Then click on Yes at the next two warnings. Open the Customers table and note that the new records will be prefilled with Arkansas in the State Or Province cells. To test the No Duplicates feature, enter “Fly-By-Night Clothes” with different contact names and addresses. For the Postal Code, enter “7777A”; the Postal Code should not take the “A” because it isn’t numeric. Now try to enter another company name. The message in exhibit 12,   should appear, prompting you for a phone number: “777 555 5555” will do. But when you attempt to enter this record, the final accuracy check will not allow it because you have ordered it not to accept a duplicate in the Company Name column, and a warning will appear.

Now build the products table using a wizard. While still in the Tables tab, click on New, highlight the Table Wizard and click OK (see exhibit 13 ,) When you select the Products in the Sample Tables column, note that the Sample Fields change to items related to products. For example, move the Product ID, Product Name, Product Description and Unit Price to the Fields in my new table by highlighting them and clicking on the move button.

If the wizard’s default field names in Fields in my new table aren’t exactly what you want, it’s easy enough to change them. For example, if you want to change Unit Price, highlight it, click on Rename Field, type in “Cost” and click OK. To finish the table, click on Next three times and then Finish.

To see the properties selected for each of the product fields, click on the Design view button on the toolbar. Product ID was set as the primary key and indexed with No Duplicates allowed. Place the cursor in the Cost cell and click. Note that the data type is “Currency.” To examine different data types, click in the data type cell and then click on the triangle that appears. For this example, leave the Cost data type as “Currency.”

Another accuracy feature is the Validation Rule. While still on the Cost field name, click on the Validation Rule cell. If you want the database to reflect the fact that no clothes that cost more than $100 per item are to be purchased, type “<101” in the Validation Rule cell. Validation Text is the message that will be displayed if the validation rule is broken. In the Validation Text, type the text you want displayed, such as “There must be an error, we do not buy anything that costs more than $100.” Save the changes and close the form by clicking on the bottom X.

You can now enter products data into the table. Begin by highlighting the products table and clicking on Open. Using the Tab key to move from cell to cell, enter all the data that are shown in exhibit 14 .

After you have entered the information, a warning should have popped up because the “Dress Shoes” item exceeds the $100 limit. Change the price to “$35.00” and close the form by clicking on the bottom X.

MAIL MERGE

The final feature to demonstrate is a mail merge using Word with the data in Access. Start with a blank Word document and type the following letter to your customers, leaving the space as indicated for Access to fill in the blanks:

 Save the Word document as Mail_ Merge and close out of Word. In the database, highlight the Customers table and click on Tools, Office Links and Merge It with MS Word (see exhibit 15 .)

Next, click on OK to link to the Word document. Change folders until you locate the Mail_Merge.doc file, highlight it and click on Open. This opens the letter and a toolbar line that has the Insert Merge Field button.

If the toolbar doesn’t appear, click on View, Toolbars and Mail Merge. Place the cursor in the line between the date and “Dear :“ and click on the button. Then highlight Company Name and click; this will indicate the desired placement of database items in the letter. Press Enter to go to the next line and add the Contact First Name merge field, leave a blank space and then add the Contact Last Name merge field. Add the rest of the fields so your letter matches the one shown below.

Click on Tools and Mail Merge. Since you already have evoked the Main document and Source data, select Merge on option 3 (see exhibit 16 ,) and then Merge again to a new document. You can generate envelopes and mailing labels in a similar way.

As you can see, databases are not difficult to use and can be valuable tools for running a business. They have many features not touched on in this article due to space limitations, but future Technology Workshop articles will demonstrate setting relationships, creating forms, designing queries and producing custom reports.