MS Access and the Power of Databases: Deciding When to Switch from Spreadsheets

Spreadsheets versus Databases

Anyone who has used a spreadsheet knows the value of the venerable columns and rows of data that can be accessed, manipulated and actually used. As the spreadsheet fills up, one can even use it like a sort of clunky database that sorts, filters, and gives a somewhat two-dimensional use. The problem with a spreadsheet is that it is what it is: a computer application designed mainly for number crunching (to an astonishing degree of sophistication) and preparing graphs and charts that give us nice pictures of those iterations. As a database, however, a large spreadsheet begins to topple over with its own girth as it widens and deepens.

The database power of MS Access, on the other hand, brings data manipulation to a level where most of the power is “under the hood.” The basic engine of the database is still the table. However, our rows become “records,” and our columns become “fields.” So it might seem to the new user that a database table is just a more difficult version of a spreadsheet; so why bother? Here’s where the “engine” of the relational database leaves spreadsheets in the dust.

The Relational Database

If a spreadsheet table could be classified as a database, it would be known as a “flat file” database. It stands alone and is related only to itself. Picture a spreadsheet listing customers, their names and addresses, the products they purchased and the price they paid. Take that same concept to a database, but instead of one table, we have a table for customers names and address, one for products and their costs, one that keeps track of inventory, and a table that brings together information from the latter to record the individual transactions of a company’s daily business.

The idea behind maintaining separate tables is that it is more efficient. Say you have a change in price or supplier for a product. Make that change in the products table, and all future transactions involving that product will automatically reflect the new information. That information is all brought together through table relationships that match one field (row) in each table to a like field in a related table. For example, in a customer’s table, you might have a field called “Product Code.” That field would be joined with your products table which would have the same field. You join the tables in the database, and like magic you can keep everything joined, but manageable.

The Real Power

The experienced database user will quickly see the real advantage of databases over spreadsheets (for data storage, etc.): Through filters, queries and reports, the database will display only the information we need. Like spreadsheets, a database will group, summarize, and make computations. Unlike spreadsheets, you only have to tell the report generator once how you want the report. You can also refine your reports through queries or filters that show you a different or restricted view of the same report. Add the relational power of databases to its reports generator, and you have a powerful ally in managing any type of information.

The Learning Curve

Unlike a spreadsheet, where the new user can grope around to enter data and maybe do a few formulas, MS Access takes a little time to get the feel. Database design is from the ground up and how well the database works depends on the quality of data design. Database users need to carefully plan the structure of their data. For example, after you have entered hundreds of records into you new database is not the time to realize that you have a problem with field format, or maybe that you needed a separate the street address field from the city, state and zipcode.

The good news is that once all the planning is done for the database “shell,” it will run rings around any spreadsheet. Experienced users can develop their own “applications” using the power of the database switchboard to quickly navigate and execute commands that produce pre-positioned reports that are updated by ongoing data entry.

So, if you are considering making the jump to databases, consider enrolling in a course of instruction. There you will meet and learn database “objects” that help your tables work for you in a manageable way.

If you are embarking upon a course of self study, be sure to check out the Microsoft Access tutorials that are available here on Bright Hub’s Windows Channel.