Monday, January 3, 2011

Beyond the Spreadsheet

Lists! Some people love them; others hate them. No matter which category you’re in, lists are a fact of life since we all have stuff we need to keep track of: customers, invoices, things that have happened, things to do. For those of us of a certain age, our first impulse might be a card file (Remember when every desk had a Rolodex?). In today’s computer world, many people may use a word processing program for simple lists but for more complicated records, a spreadsheet such as Microsoft Excel is often the program of choice.

Spreadsheets are great for many purposes, especially those that used to be accomplished on accounting paper and involve lots of mathematical calculations and they have evolved to include many data management features. In addition, the program is often included in the basic software found on many computers (Excel is included in the most basic versions of Microsoft Office). However, the basic structure of spreadsheets make them difficult to manage for more than moderately complex information. This is because a spreadsheet relates data spatially, based on their physical location, not logically, based on the information being stored. Many people have become frustrated as their once managable spreadsheet became more and more complex and they find themselves “impaled” on the grid of columns and rows.

Database programs, such as Microsoft Access, offer a solution to this problem since they are, at their core, designed to store and relate sets of data. Because data are related logically, rather than spatially, a “relational database” can be a very powerful tool for handling very complex information systems. The key to success with a database is to decide at the beginning, what “sets” of information need to be stored.

Let’s say we want to store information on customers and their invoices, using a mental image of multiple card files. We would establish a file with a card for every customer. On that card, we would record the basic information about the customer. We could also record information about each invoice (date, amount, whether it was paid etc.). There would, however, be a physical limit to how many invoices we could record, even if we wrote really, really small and used the back of the card. Instead, we create a separate file with a card for each invoice. In addition to the basic information, we would also record which customer was being invoiced. In this system, there would be no limit to the number of invoices we could record for a customer.

But a database is more than just a place to store information. It is a system for controlling how information is entered (and by whom) that improves accuracy, efficiency and security. It is also a “magic window” into your data. Using our card file example, we could easily retrieve a customer (based on business name, contact name, phone number etc.) and automatically pull information on all related invoices by telling it which customer ID to look for. (We don’t need to tell the system that the invoices are the 13th to 27th cards in the file!)

Although they don’t present the user with a familiar looking, ready made system of columns and rows to store information, a database does provide a more robust system for storing, retrieving and reporting information. Programs such as Microsoft Access include many simple to use wizards that help you get started and basic instruction is available from a variety of sources. The expense of purchasing an additional piece of software and the up-front time spent in setting up the data structure, are more than recouped by gains in efficiency and flexibility for any information processing that is an ongoing part of your business operation.

Joe