The database dilemma: using access for success: should you be using a spreadsheet or database for your reports? How to tell whether you need Access or Excel

Concrete Producer, The, May, 2003 by Charles L. Peters

Every morning in the ready-mix business starts with the ritual of compiling operations reports that indicate the success or failure of the previous day's business. How did each ready-mix plant perform compared to the others? Are volume and productivity up or down? Preparation of at least some of this data happens with common office software such as Microsoft Office.

Ironically, the best program for this type of task is not always the program used to complete operations reports. The use of a spreadsheet instead of a relational database is a common mistake. This article addresses the benefits of using Microsoft Access instead of Microsoft Excel for reports common to the ready-mix business.

Perhaps the biggest advantage of using a database instead of a spreadsheet is the ability to search and sort information contained in the database. For example, suppose that there are data for several ready-mix plants, each with a fleet of 10 or more trucks. The goal might be to retrieve the truck with a certain license plate number from the data, or perhaps to retrieve all of the trucks for a certain plant. Accomplishing goals of this nature is easy with a database. Microsoft Access provides a means to perform not only the simple search needed in this example but also complicated searches based on an unlimited number of search criteria.

Another major benefit of using a database rather than a spreadsheet is ease of reporting. Spreadsheets are very good at performing calculations of a repetitive nature; the output format remains unchanged as new numbers replace the old numbers. A database provides the means to report data and calculations in an unlimited number of reporting formats without incurring the burden of reentry of the data and formulas.

Database take the lead

Additionally, databases facilitate reporting and calculations on an unknown number of data items. This scenario is awkward in a spreadsheet because line modifications adversely affects the final hard copy as well as the formulas that perform various calculations.

An example of this type of problem is production numbers. For each day of production, there are important quantities to track. Some of the numbers typically reported are total number of yards per plant, total number of man-hours per plant, total cubic yards of wasted concrete produced per plant, total mileage driven by each driver, and attendance. One might be interested in a total number of yards per plant for a week, a month, or a year. These totals, commonly called running totals, are much easier to implement in Access than in an Excel spreadsheet. Often one must create many spreadsheet files using a file naming convention in order to maintain historical records that deal with running totals. This is not a problem with the use of a single database file. A single database file performs this type of task especially well.

In a database, reports are possible from a variety of sources--all of the data, data sorted by certain criterion, subsets of data based on user-defined criteria, or even a sorted subset of the data. The great flexibility found in relational database reporting can prove to be highly productive in the hands of a manager that knows how to use this powerful office tool. Generating custom reports to fit the exact circumstances is easy with a database. Custom reports generated with a spreadsheet often require reentry of data--a process prone to typographical errors.

Easy access to Access

The disadvantage to using Microsoft Access might be lack of familiarity. Many people have excellent Microsoft Excel skills; therefore, they simply will not take the time to learn something new. Of the two software programs, Microsoft Excel is much easier to master. However, after achieving a thorough understanding of Microsoft Access, the use of Access in place of Excel will provide a measurable gain in productivity. The gain in productivity will be worth much more than the time spent to learn a new skill.

If you are self-motivated, there are many books on Microsoft Access. Be sure to purchase the correct book for your version of the software. A book written for Microsoft Access 2002 would not be a good selection for a user of Microsoft Access '97. Another excellent way to learn new database skills is through a local community college. Classes at a community college may be less expensive than an American Concrete Institute seminar, yet provide skills that greatly increase office productivity. Try to find a class developed to teach both Microsoft Access and database design to the beginner. Start by creating a small database that features only basic skills. As confidence increases, expand the database adding functionality that includes newly acquired skills. Once a Microsoft Access master, using Microsoft Excel only when appropriate for the task will happen naturally.

--CHARLES L. PETERS is a graduate of International College in Naples, Fla. The author is CEO of Space Age Computer Solutions Inc. Contact the author by e-mail at concretetech@comcast.net.