Academic journal article Journal of Accountancy

Building a Database from Scratch

Academic journal article Journal of Accountancy

Building a Database from Scratch

Article excerpt

EXECUTIVE SUMMARY

* TODAY'S DATABASE SOFTWARE is much easier to use than earlier versions, plus it's much more powerful. Yet many CPAs still create databases in the software they know best--spreadsheets.

* BY GOING STEP-BY-STEP through this tutorial, you should discover how well database software can work for you.

* DATABASES TODAY CONTAIN many different kinds of pre-designed templates--making the initial design task a snap.

* IF YOU ALREADY HAVE a database in a spreadsheet, a simple way to import data directly into the database is by using a wizard.

* ONCE YOU ENTER THE DATA, the programs make it easy to change and move information around.

* USING FILTER BUTTONS, it's possible to produce reports of specially selected data.

* THE MAIL MERGE FEATURE ALLOWS you to create letters from your data--a marriage of the database and word processing software.

Put information at your fingertips.

Only 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 pre-designed 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, at right. You can view the full palette of templates by selecting the database wizard option displayed on Access's opening screen.

[Exhibit 1 ILLUSTRATION OMITTED]

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, at left). 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).

[Exhibit 2 ILLUSTRATION OMITTED]

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. …

Search by... Author
Show... All Results Primary Sources Peer-reviewed

Oops!

An unknown error has occurred. Please click the button below to reload the page. If the problem persists, please try again in a little while.