Introduction

If all of your financial records are maintained in Excel spreadsheets then you may be missing out on many of the advantages of using Access databases. Microsoft Access may already be available in your version of Microsoft Office. It is a component of Office Professional (and some other versions) and a non-development version of the Access runtime is downloadable from Microsoft. It is also available separately. The biggest investment would be the time to learn or have someone trained to develop databases with it.

You will not want to convert all of your information to an Access database, of course. Access and Excel have different strengths and the two can be integrated. You can use Access for some needs and Excel for others. You can develop Excel spreadsheets and use them later to develop (upgrade to) an Access database. You can even use Excel to analyze data stored in Access or export Access data to Excel. The reason so many people use Excel for everything is that so few people know how to use Access, and vice-versa. Companies are actually moving away from Excel for some tasks, and using standalone products for those functions. In many cases, developing an Access database could be a more effective, economical, and versatile alternative.

There have been other articles[1] written about the choice between Excel and Access, and the benefits of each. Some good points have been made, but others are a bit more superficial. This may not remedy that, but the additional insight may help you develop a plan that fits your company or organization. One guideline[2] I’ve read has been that Excel is better for numbers than text. Excel does have more advanced statistical functions and it can be a better tool for data analysis. However, Access is an excellent resource for creating complex financial reports, and makes it easier for multiple users to access the data. Both have their strengths and weaknesses, and you are not limited to using just one.

Excel Spreadsheets

Microsoft Excel and other forms of spreadsheets are everywhere, and access to them is often built into other software, such as the Document Management System in Lacerte. Other programs, like Microsoft Word have the ability to embed spreadsheets in their documents. Other companies provide on-line access to their spreadsheet applications at no cost. Workers are so accustomed to working with Excel it would be unthinkable to be without the self-calculating grids in a spreadsheet.

Advantages

As previously mentioned Excel has an extensive collection of built-in formulas and is particularly effective in statistical analysis. It is also useful in what-if scenarios, cost-benefit analysis, and financial calculations. Excel can accommodate any type of information that can be organized in rows and columns. Some reasons to consider Excel over another option may include

Data & Features

• Each cell in a spreadsheet can contain different types of data.
• Cells in a spreadsheet can calculate a value and include references to other cells in the spreadsheet.
• Spreadsheet cells are self calculating.
• Detailed formatting options allow you to format every cell in the spreadsheet separately to meet your needs.

Learning

• Learning Curve. The basics of Excel can be learned easily and quickly, whether self-taught or through formal training.
• Training availability. Classes on Excel are readily available at colleges and universities, as well as on-line.
• Multiple levels of expertise. While users with basic understanding of Excel can use Excel, more advanced experts can find other features of Excel useable.

Usability

• Familiarity. Excel doesn’t require extensive training to gain a basic understanding of spreadsheets. Anyone with some office training will likely have learned something about Excel and can use Excel on their first day on the job.
• Accessible. Any user can create a spreadsheet; they don’t have to wait for someone else to develop it or use special software to create spreadsheets.
• Availability. Excel is standard on most every business computer.
• Standardized. Spreadsheets may be consider the industry standard for financial information reporting and analysis, and Excel is the most common format.
• Portability. Excel spreadsheets are portable. Users can pass around spreadsheets internally, email, or post them for upload or download on an internal or external website. Many applications can open or view Excel spreadsheets. Applications that don’t read/write Excel often can import and/or export in a spreadsheet format. Sometimes that format is in csv, which is not a true spreadsheet, but Excel is usually the default application that opens csv files.

Features

• Formula Library. There is an extensive library of functions that can be used.
• Graphics. A wide variety of graphic features and charts are easy to create.
• Expandability - Advanced programming capabilities are built-in to Excel and include macros and a special version of the Visual Basic programming language.

Excel is considered to be the one software package that office professionals and particularly prospective accounting staff will need to know. A candidate's qualifications for a job will obviously include several areas of accounting, but the prime candidates should have an expert knowledge of Excel.

What constitutes an expert knowledge can be debated, though. From an accountant's point of view, that would include knowing many of the financial and formatting formulas, a few special features of Excel (Pivot Tables, etc.), and proficiency with basic functions and shortcuts so that he will be able to create and modify spreadsheets quickly. For an IT professional those may be just the basic requirements, and real expertise would involve writing macros, creating forms, and writing VBA code to enhance the spreadsheet, simplify use, and integrate with other applications or data sources.

Even so, the need for Excel is unquestionable. But from a practice management perspective, Excel’s value may be overstated. Excel has many limitations and risks.

How Spreadsheets Work

Everyone reading this probably knows how Excel works, but for comparison later, this is a short version of how spreadsheets allow us to do the work we do.

• A spreadsheet has many cells arranged in rows and columns.
• Each cell can have formatting, formula, and can refer to other cells in calculations.
• Rows typically represent records with columns representing characteristics (fields) of the record.
• Alternate display of information is possible in a spreadsheet with Pivot tables and charts.
• Workbooks can have multiple spreadsheets with cells that refer to cells in other spreadsheets.

Limitations

Excel has limitations on what it can do easily. While more advanced users can create complex spreadsheets, many users will limit their use to the more basic features of Excel. This is particularly true when it comes to structured or three dimensional data. When users ignore the limitations or stretch the capabilities of Excel or their own abilities, the resulting spreadsheet may be prone to errors. In some cases, it might be better to consider a database, especially if the requirements are complex and the same type of data is frequently used by other clients. Limitations of Excel may include the following.

• Relational data. Excel doesn’t handle relational data very well. The two-dimensional ability of spreadsheets may not be able to consider multiple related variables.
• Data structure modifications. Excel cannot easily grow with changes in data structure. Changes in data structure may require many modifications to many cells in order to adapt to new requirements.
• Spreadsheet maintenance. Reusable spreadsheets also have limitations. When someone develops a spreadsheet and then hands over the spreadsheet to someone else to use as a template, there is no control over what changes may be made, and if they are made properly. Improvements in the original also cannot easily be distributed to successors of the previous version of the spreadsheet.
• Data synchronization. If the same spreadsheet is updated by other users, there is no easy way to merge or synchronize the independently developed spreadsheets.
• Version control. With spreadsheet distributions there is no version control so that there may be many spreadsheets based on an original that are not updated with the latest calculations.
• Backups. Spreadsheets are typically loosely stored on user systems and there may not be specific backup procedures that protect and verify the data.
• Don’t forget to save! Excel must be regularly saved to disk whereas Access databases save entered data after entering each record.

Risk

In addition to the limitations, and often because of the limitations, Excel contains potential risks.

Security

Security is one risk associated with indiscriminate use of Excel. Since spreadsheets are so easily shared, confidentiality of data may be a concern, as with any standalone document. Likewise, as with other documents, loss could be a concern if there isn’t a reliable IT policy in place for backups and data security.

Errors

While security is the concern of many professionals, the biggest risk may be simply the errors that spreadsheets may contain. The actual data may not be reliable, or information produced from the data may be inaccurate. Much has been written about the prevalence of errors in business spreadsheets. Some articles have estimated that almost 90% of spreadsheets have errors,[3] and the risks involve the possibility of material errors in a report. Further, one paper[4] analyzing errors in various companies found that errors were common at every level of the organization and that people are only moderately good at correcting them.

Errors can also be made when creating a spreadsheet, when using it, or when making modifications to update or customize it. The errors can be associated with using the wrong formulas, the wrong fields, entering data outside of a formula’s field range, or improper formatting of numbers. Some errors could occur as spreadsheets age. If the formula uses a common value (hard-coded) in a formula and that value changes, the formula has to be changed. One example may be a tax return analysis that includes 7.5% as the floor for medical expenses. The risk is greater with more people in the department, and the wider the distribution of the spreadsheet.

Types of Errors

Errors can range from simple mistyping to errors in more complicated formulas. One article[5] analyzed spreadsheet error using this list of common error types.

1. Hard-coding in a formula – one or more numbers appear in formulas
2. Reference error – a formula contains one or more incorrect references to other cells
3. Logic error – a formula is used incorrectly, leading to an incorrect result
4. Copy/Paste error – a formula is wrong due to inaccurate use of copy/paste
5. Omission error – a formula is wrong because one or more of its input cells is blank
6. Data input error – an incorrect data input is used

In general, errors can be segregated into those created during development of a spreadsheet, those that are the result of usage, or a combination of the two.

Development Errors

Development Errors include the actual errors made during spreadsheet creation, although many of those can be eliminated with company-wide development and usage standards.

Logic and Formula Errors

Logic and formula errors may be the most common types of errors made during development. While formula errors are inherently logical errors, other errors could be the result of business logic errors, or incorrect interpretation or legal requirements.

Formula errors are particularly onerous because they can cascade across a spreadsheet and incrementally distort the reporting for long periods of time. This is particularly true when formulas reference formulas in other cells. Dependencies must continually be evaluated when spreadsheets are updated or re-purposed. A lack of understanding of differences between similar formulas can produce accurate records initially and later change as more data or more types of data are entered. A formula that gets an average by taking a total and dividing by a count of cells would usually be an inappropriate use of Excel formulas.

Operational Errors

Operational errors refer primarily to the use of spreadsheets. Some errors may be made by using the wrong spreadsheet for a particular task, or for the wrong client. Spreadsheets should always clearly document their purpose and any assumptions that are not defined in formulas.

Reproduction is another risk of Excel spreadsheets. In one of my jobs we used an initially standardized worksheet for each client. However, sometimes it was duplicated from a previous year, and sometimes developed from the template. Often the inherited spreadsheet had issues, either with enhancements or overridden formulas.

In many cases these spreadsheets are re-used from year to year or by different employees and for different clients. Unfortunately, when an error is introduced into a formula and not discovering it until later, there may not be a trail for finding errors in previously shared worksheets. The more complex and the more community oriented the worksheet the less likely it will be discovered early. Other types of errors are also discussed in a paper presented by Panko.[6]

Usage Errors

Not only are errors possible in the development of a spreadsheet and improper operational standards, spreadsheet usage could be a source of inaccurate spreadsheet calculations. There are many possible usage errors, but the most destructive ones are those involving unprotected cells.

Unprotected Cells

If a spreadsheet is used by others, there is a risk that a user deletes or overrides a formula. When formula cells are unprotected, at every use of the spreadsheet there is a potential of corruption somewhere on the spreadsheet. Formula cells can be protected in Excel but the process is seldom done for in-house spreadsheets because it is so time-consuming.

Blended Errors

Combining development errors with usage errors may be another source of corruption. The coding may be correct if the user uses it as designed, but there may not be any error-checking. For example, a macro that relies on a hard-coded value to insert rows could be rendered worthless or destructive if the user manually inserts or deletes rows.

The Hidden Costs

There have been estimates of the cost of errors but the actual costs associated with spreadsheets are often not known. The financial impact of most errors may be minimal, but the risk is still there if there aren’t policies in place to monitor spreadsheet development and maintenance. Even when the actual errors are small, when they are part of a formula, the effects can be cumulative.

The biggest risk may be that errors can go undetected for years. Excel can help you find errors in a formula’s syntax and it does provide warning tips about cells, but it doesn’t know if the formula accurately summarizes the information the user needs. Errors are possible in the simplest spreadsheets. The more a user strays from the primary features of Excel, and tries to use a spreadsheet like a database (Risks of Using Excel as a Database[7]), the greater the risk.

“If the spreadsheet is ridiculously complex and impossible to read then nobody has a chance of actually reading it – and determining if there are any errors of it in the first place. That just becomes impossible."[8]

Error Detection

Detecting errors can be as simple as regularly checking formulas being used in a spreadsheet, but there are other methods for detecting errors.

Formula Auditing

Show formulas. When developing a spreadsheet, Ctrl-` will toggle between formula and values. This will enable you to see which values have formulas. A cursory view of each formula may help verify its accuracy. This toggle can also enable you to spot when a column of formulas has been interrupted with a value typed into one of the formula cells. When editing a formula, selecting a cell reference will highlight the range being used, further verifying the proper reference. Newer versions of Excel feature additional operations that will evaluate formulas checking for obvious errors, trace errors, or trace references from or to a given cell.

Peer Review or Collaboration

One of the most effective means of error detection is peer review, or having someone else that is knowledgeable about the project at hand to closely look at the spreadsheet. If the project is one that can be useful to others, collaboration may need to be considered. A team project subjects each member to the scrutiny of other members.

IT Auditing

While most CPA firms spend some time performing audits for their clients during the tax off-season I wonder how many of them audit the IT processes of those clients. Even more critical how many of those firms who are involved in peer reviews of their operations have audits done on their own IT operations. An IT audit will not only look at potential spreadsheet issues, but uncover security and other IT risks. The CPA profession has high standards to maintain in providing their services.

Error Prevention

Identifying errors and the ways errors are creating will be the first step to preventing errors. That goal can be achieved at two levels. Create a system to follow in developing and controlling spreadsheet development. A company-wide standard will be the most effective in developing accurate and functional spreadsheets. The following guidelines are given in “How do You Know Your Spreadsheet is Right?”[9]:

• Draft effectively before creating a spreadsheet.
• Know who sponsors, develops, reads and audits each document.
• Create a prototype that allows developers to refine definitions and user-friendliness in the real version.
• Create a spreadsheet with change in mind, as most sheets develop over time.
• Keep reports, input and logistics in separate areas of the spread.
• Opt for one formula per row or column.
• Use links to other sheets and external sources wisely.

Secondly, each individual should develop good skills and practices when creating spreadsheets. Consider these few, more specific, guidelines for developing spreadsheets.

Field Naming

Naming fields and variables will improve the clarity of many functions that refer to other cells or ranges. Even better, Excel will keep a list of all names used and you can go to them from the Name Box just left of the formula bar. You can also use the Name Manager to review names and their references.

The field naming advantages are also effective when using tables in Excel 2007 and later. Tables (and their named reference) can adjust for insertions, deletions, and still maintain boundaries.

Avoid hard-coded values in formulas. Never assume a value will never change. If a value represents a set amount or percentage, create a separate cell for that value, and improve on that by naming the variable. A lookup section can be useful for collections of values that are easy to change, and lookup when needed. State’s sales tax rates would be one example.

Protection

After completing development of a spreadsheet, protect it and any formulas. Even if you use a universal company password, protection will help prevent accidental overwriting of formulas used in the spreadsheet. More advanced restrictions could also define what information is relevant in a spreadsheet.

Questions to Ask

There are many ways to evaluate spreadsheet development. This is a simplified series of questions you might ask about new spreadsheets.

Do you need a spreadsheet?

Create spreadsheets to get new information instead of just transferring your existing information to Excel.

Are you using the right information?

Know what information you have to work with, and if it contains what you need to get useful information.

Are you using the right formulas?

A formula that uses Sum/Count may not get an average if values are missing from the range being used in the calculation. Using average may (or may not) be the function you want. Statistical formulas in particular require values that meet a certain range of values.

Are you using the formulas right?

While Excel will accept the entry of a formula, it can’t tell if the arguments in the formula have been swapped. Incorrect cell references will also make even the best thought out formula generate meaningless information.

Are you getting the right information?

If the information you are getting doesn’t make sense, you may need to verify formulas or re-examine the logic behind the calculations.

Development and Maintenance

Development of spreadsheets is typically task-oriented. When a new task presents itself a new spreadsheet is developed and put to use for that person or project. For that reason, the skill set required to create spreadsheets is not much different from simply using spreadsheets and the line between use and development of spreadsheets is blurred. This is particularly true if the spreadsheet simply totals multiple columns of data.

Often complex tasks require more complex worksheets. While someone with basic spreadsheet skills may devote considerable time with such projects, there is a significant difference between the skill set required to develop complex spreadsheets and a user with basic knowledge. Not only will such projects be more time-consuming (cost-benefit ratio), they are prone to errors. The user/developer mentality is so common that the concept of developing and maintaining spreadsheets may seem foreign to many managers. The error level of average spreadsheets, however, accents the need to rethink the development process of Excel and spreadsheets and consider demarcation.

Improving quality control of Excel spreadsheets is mentioned here only briefly. For more information review the articles included in the footnotes. “Are Your Excel Spreadsheets Accurate?” mentions some basic ways to guard against errors and detect errors in a spreadsheet.[10] “Is your Love for Excel Misplaced?” looks at a few things that may help to identify reasons for inaccuracies including the need to define the purpose of a spreadsheet before creating it.[11]

Standards have also been developed for spreadsheet development and are available on the Spreadsheet Standards Review Board website.[12] Related to this organization is a structured site devoted to Excel maintenance called “Best Practice Modeling.”[13] Site pages discuss Excel development, including a description of auditing tools available, Modular Development, and a Knowledge Base of industry specific applications. While a system can help to organize the development process, it is the individual developer, whether secretary or IT professional that creates spreadsheets and a guide on “How to Make Spreadsheets Error-Proof”[14] could be a good initial step. “How do you know your spreadsheet is right?” presents some best practices to use in developing spreadsheets. [15]

Much can be done to improve quality of Excel-generated information but there are cases where a database might be more appropriate.  

Access Databases

Databases are similar to spreadsheets in that data is often represented in rows and columns. The biggest difference is that database information is relational. Each table in a database is typically related to one or more other tables in the database. Relationships are usually one-to-many. Thus, for each client, there may be one or more invoices. For each invoice there may be one or more items. For each employee there may be one or more clients he is responsible for. Unlike a spreadsheet, the data entered is separate from the way it is gathered or presented, so the cells do not have display formatting associated with them. Forms and reports, however, can display the data in many different ways.

Databases are the primary sources for Enterprise Resource Planning (ERP) packages, in part because they are highly efficient at data management. On a local level, databases can also be created on an individual PC and shared on the local network using Microsoft Access. Microsoft Access provides users with all the tools for developing functional databases. Whether or not you have a network database or other ERP, learning how to develop using Access may fill the need for many tasks.

History

In the earlier times, data processing was separate from business functions of a company. Computer programmers developed programs that users accessed to generate reports. Most of those programs were database-based. Business users didn’t concern themselves with formatting and output. Their job was to enter data, and request suitable reports from that data. When the first business PC showed up, the first major spreadsheet (Visicalc) helped users to get many of their financials done without an IT department. Lotus 1-2-3 soon followed and then Excel.

As focus moved to spreadsheets, companies found a way to eliminate, downsize, or refocus their IT department. As a result IT-developed databases were replaced by desktop produced spreadsheets. Today, many of the larger companies have ERP systems which are replacing some of the spreadsheet applications. The software industry has also developed specialized database software to handle specific tasks. In accounting today, database software handles tax services, audit functions, and many bookkeeping and financial accounting functions. Progressive companies may do well to bring database-driven information back into other areas.

Database Advantages

Excel and spreadsheets have a useful purpose in an organization, but there are times when a database is more appropriate use of resources. When appropriate, the advantages of using an Access database are multi-faceted. The following categories illustrate the benefits of an Access database.

Data Management

• Data efficiency. A single database can handle the tasks of many duplicate spreadsheets.
• Data growth. Unlike a spreadsheet that becomes unwieldy as it grows, databases operations are generally not affected by growth in the size of the database.
• Integration. A database can handle many different tables of different types of information and integrate all of them. General information about a client can be stored with financial transactions of the client. Everything is (should be) linked to everything else.
• Pictures. Pictures, web links, and other objects can be stored in Access as part of a record. Links to separate supporting documents can also be added to a record.
• Joint databases. A database can reference tables in a separate database. A user may or may not be able to add records in a supporting database. This is useful if some of the data is more confidential or security is important.
• Multi-level access. It’s possible to limit access to some data in a database on a user-by-user basis. Limits could be inability to change, or inability to view confidential data by some users.
• Consistent calculations. Calculations in Excel are cell by cell. If a cell is missing a formula in a column the totals will be incorrect. In a database, the total can be generated by multiplying two fields for each record, without having to assign a formula to each cell in the table.

Usability (ease of use)

• Easy to use. Access databases are easy for users to enter relevant data, since the development is done prior to deployment.
• Navigation. Access databases are typically designed to display forms for data entry with set navigation controls. This is better than trying to enter data in a long string of cells in a grid.
• Data entry forms. Although it is possible to create a data entry form in Excel, forms are the primary tools in Access for accepting data from users. If source documents vary, separate forms can be used to enter data from source documents without having to change the database table structures.
• Formula protection. Database calculations are separate from data entry, so there is no concern about writing over a cell formula with a constant value.

Structure

• Access can represent multiple dimensions, while Excel is typically limited to two. In Excel, for each account you would have rows and columns. For additional accounts you would add another spreadsheet or section.
• All cells in a column are the same. In Excel, cells in a column of table data (fields) are formatted individually. In an Access database, the formatting of a field is the same for all records.
• Data types must be defined in Access, whereas Excel may (incorrectly) redefine data types, format, and precision based on data entered.
• Validation. In addition to data type definitions, data entry can be validated in Access for consistency.
• Primary keys. Every record in an Access database has a primary key that distinguishes that record from any other.
• Normalization. The development process of normalization in an Access database insures that tables are properly related to others and that data is stored efficiently.
• Referential integrity. Access can internally monitor the integrity of data in tables. If a value in a table references another table, the reference must exist.
• Cascading additions and deletions. Referential integrity also controls stray, orphan data. When a table adds or deletes a value with references to a related table, the related tables are added or deleted. For example, you can avoid having invoices for customers that no longer exist if you choose.

Reporting

• Data and presentation are distinct layers. In IT, the separation of data and how it is presented are considered separate layers.
• Flexible reporting. Not only do separate data and presentation layers protect the data from accidental corruption (cell overwriting), reporting from an Access database is more flexible. When properly developed, reports can integrate data from many areas to provide more meaningful information. Using queries, you can get reports on select data or generate full reports of larger sets of data with multiple levels of subtotals, for example. Slice and dice.
• Same data, separate reporting. Because reports are separate from the data in a database, you can create a new report without having to make a copy of the data. In Excel, a different reporting format often means making a copy of the first spreadsheet and modifying the copy. At that point, you have two separate copies of the data that would need to be modified if the data changes.
• Reporting formats. You can export information from a database in a number of other formats, including Excel. Access does have the resources to prepare complex queries, but it doesn’t have all the statistical features of Excel, so you can store the data in Access and export to Excel to analyze if necessary.

Development and Maintenance

• Longer development cycle. Often considered a disadvantage of databases, the longer development cycle reflects the nature of development. Spreadsheets are often not validated to insure they are accurate. Spreadsheet testing may be limited to formula syntax. Databases are generally tested to insure that they are functioning properly.
• The calculations and the data entry are separate. Users cannot alter calculations created in a report using data entry.
• Although a different type of expertise is required, Access databases can be more easily managed once developed. Similarly, automation through macros and VBA modules is available in Excel, but is more of a part of Access, with each form or report having its own code.
• Access databases can be centralized, with users sharing a single data source accessed over the internal network.

How does a database work?

• Each table represents an entity and has multiple records.
• Each record must have a unique value (keys) to identify itself.
• Tables are linked with other tables through those keys.
• Forms and reports query the tables to get the information to be presented.
• Multiple reports can be designed using the same data, without having to alter that data.

Information Presentation

One characteristic of a database is in its layered process. In a spreadsheet, data and presentation information are integrated into each cell. In a database project there are at least two layers; the data layer and the presentation layer. By segregating the tasks of entering information and creating reports users can focus on just one area at a time. Furthermore, once the data is in, the information can be presented in any number of ways, with selected levels of detail.

Database Limitations

Extensive use of Access databases is not for everyone.  Databases may be a new process for many cases, and staff will have to adjust to the operations and limits. Databases separate the two processes, development and use, so access to the database will depend on whether the person has development skills. Other limitations include the following.

• Steeper learning curve. While use of a well-interfaced database can be a breeze, most any type of development or maintenance comes with a steep learning curve. Extensive training is required to teach someone to develop databases.
• Different expertise. Expertise Database development requires a different level of expertise and developers are not readily available. Developers must understand database concepts, not just the steps to navigate the environment and create formulas.
• Long development cycle. It is more time consuming to create a database than to create a spreadsheet. The upside is that changes are easier to make and the database may have many purposes.
• Commitment required. Not only does a company need to develop the database application, they should be able to maintain it, add features when needed, adjust for changes in policy or law, and make corrections when needed. Some databases can exist for years after development without maintenance, but that may not be the norm.
• Not error proof. Poorly designed databases, like poorly designed spreadsheets can appear to generate useful data. Test and retest.
• Reporting. Access reports on all data the same way. Without some specific coding, it’s not possible to highlight or add special formatting for specific value(s).

Database Risks and Control

There are risks associated with database development, and executives will not have as much control. While a senior partner can review spreadsheet formulas, he will likely not have the knowledge to check a database table, formula, or report for errors, other than possibly verifying the information with spreadsheet calculations.

Errors in a database are not easy to detect by users other than double-checking the data entry. A comprehensive development process is needed to insure errors are detected and prevented prior to deployment of the database system. When centralized, loss of data can be catastrophic. Because a database contains large amounts of data, the potential loss is large. Incremental backups are crucial.

Database Development

Following the introduction of the PC in the mid-1980’s, with the limitation that not much software was available, one of the main reasons a company would hire a computer guy was to set up a database. Today, given the universal use of SQL and normalization, and the availability of affordable database software, the value and versatility of a database is greater than ever.  Most any size firm can begin the process if they have a resident database guru, either hired for that or trained in-house. While a company may not need a full-time IT department to begin using more Access databases, there must be someone available who has a good understanding of how to develop a database. A very simple database can be developed with minimal training but the real benefits of a database are based on a well-developed database program.

Database Development training is not unlike training requirements of an Excel. Although formal training is not required extensive training is a requirement. It will require more than a single introduction to Access. A possible misconception is that a person can take a single class in database development and be able to handle all of a company’s database needs. Just as Excel experts gain their expertise through progressive experience and often several classes in the subject, Access training requires more than a rudimentary understanding of Access basics. Given the time and opportunity, however, a staff member can grow into being a database guru.

You don’t have to invest in a dedicated IT department, but you do have to make an investment. Many managers may decide it’s not worth it, or that other projects are more in line with their business. You can begin a database team. Start with a few projects, evaluate, improve and refine them. Then integrate one or more of them. As you grow you can consider more complex applications, network databases, or web based (cloud) databases.

Identifying potential database projects

It’s not always apparent what project to consider for database development. One clue may be the level of frustration you have with a complex spreadsheet. The prevalence of task-oriented software is one indication of the power and flexibility of Access databases. A lot of such software is database driven and provides safeguards to many common risks. A good guide is to look at what things are done with separate software.

Tax Software

I wouldn’t recommend creating your own tax software in an Access database because it is so complex, but a look at what tax software includes may help you picture what the inside of an Access might look like. Tax database doesn’t just contain all the data for a single client. At end of tax-season, your tax software may contain 1000’s of completed forms with hundreds of fields for 100’s of clients in a single database. The fields of one form may be dependent upon the values other forms. Underlying the forms is the collection of related tables. Tax software selects only the information required from those tables for a particular client for a particular year and groups it into a single tax return.

Similarly, any requirement that has multiple layers of the same type of data may be better represented in a database. Spreadsheets that consists of multiple levels of spreadsheet data is a prime candidate for an Access database.

Sectional Spreadsheets

Excel files may contain several (many) sections in the same spreadsheet, with each section using the same formulas. In one case I looked at, a survey project, modifying the spreadsheet was time-consuming. Formatting was fixed and analytical tools did not fit the fragmented format of the spreadsheet. It was essential many spreadsheets on one spreadsheet.

Workbooks

Another Excel strategy was to actually create multiple spreadsheets in the same workbook, with one spreadsheet acting as a summary of the others. I’ve seen over 50 spreadsheets tabs in a workbook and a full sheet of cross-spreadsheet formulas that summarized the other worksheets. As such the data is fragmented and producing the spreadsheets for distribution was very time-consuming. Just the pure volume of information and number of formula cells made it challenging, if not impossible, to insure the calculations were accurate. Not all workbooks are like this, but when repeating spreadsheets are common then a database may be a better choice.

These types of worksheets are actually useable, although they require a very organized developer to use or manage it without introducing the potential for error and associated risks.

Management Tools

Access is not only an alternative to spreadsheets and other digital resources. In many cases, a database can be a new thing that improves management of a crucial element of important business processes. That has been the process that led me to develop databases for inventory control, order processing, and resource management, rather than use Excel as an intermediate step. As companies become more comfortable with databases and improve their internal development potential they will find new uses for this technology to save time and provide improved reporting for clients and within the company.

Conclusion

The first lesson to be learned from this is that companies need to establish a system of spreadsheet development, particularly when spreadsheets are distributed throughout the company. Occasional audits of spreadsheets will help to identify problems and risks associated with spreadsheets. Depending on the structure of the organization, consideration may need to be given to protecting some spreadsheets. In conjunction with implementing a quality control system for spreadsheet development, continuous training will help prevent users from getting stale on features of Excel that they do not use often.

Secondly, sometimes a spreadsheet is not the right tool. If all you have is a hammer, everything looks like a nail. Sometimes you really need a screwdriver. Sometimes you need to have a database. If your firm or department is large enough or has the staffing, consider investing the time to train a couple of people in database development.

This is a simplified coverage of Access databases. Without more exposure to databases you may not fully see the nature of development and daily use of databases. As time permits, more information may be posted to illustrate the effectiveness of internally developed Access database, the process of developing a database, and a program to train staff to develop and use databases.