Microsoft Access: Best Spreadsheet Add-In

By Simon Hurst

Of the main office applications used in professional firms, word processors are the least well used, spreadsheets the most overused, and databases the most underused. Part of the reason for this is that database applications are often seen as being the preserve of experienced developers, who use them to create comprehensive database applications.

This is indeed one of their capabilities, but desktop database applications can also be used to develop very effective solutions to a range of problems with no more technical skill, and often considerably less, than that which would be needed to solve the problem using a spreadsheet. The real benefit for many users of obtaining some understanding of databases comes not from creating standalone databases, but using or working with databases to get more out of their spreadsheets, word processors and accounts applications.

Think Link
A key feature of a database is the ability to link easily to existing sources of data. This means that you can exploit the capabilities of a database without ever having to create your own tables of data or enter a single item of data. However, you do have to have some idea of how relational databases are constructed. Anyone who can manage to use an Excel Lookup function should be able to cope with this without too much difficulty.

This ability to link to existing data means that you can not only use existing tables of data from a single source, but also combine data from different sources, or add additional data of your own without having to interfere with any existing database or application. All you need is some basic understanding and a reliable link between the different tables of data such as a unique client code. By making data accessible in this way you can make your use of the other office applications a great deal more efficient and reliable.

Let's consider spreadsheets first. One of the things that a spreadsheet can do extremely well is allow interactive, ad-hoc analysis of data. But first of all, you have to be able to get your hands on the data you want to analyze.

I've heard the argument that software suppliers ought to do a great deal more to make the data in their applications accessible to users of all technical abilities, and there is, no doubt, an element of truth in that. However, until all of them do, users will either have to do it themselves, or pay someone else to do it for them. Even if suppliers did more, there would still be many advantages in being able to work confidently with databases. However easy suppliers make it for users to get at the data in their own applications, an understanding of databases makes it possible to link data from different applications and to augment the data held by an application with additional user-defined data.

As an example, one client I work with needed to take some information from their purchase order system, categorize the orders in a way that their accounts system didn't allow for and then perform some ad-hoc analysis. The client firm used a simple Microsoft Access database to link to the relevant data in its accounts system, created a small data entry form to enable the input of the additional categorization data, and then created a simple database query to produce the newly categorized table of data. They then linked an Excel pivot table to this query so that they could analyze and report on the data as they required.

Undoubtedly, they could have obtained the same result by exporting the data to Excel and then entering the categories manually in Excel, or maybe used Excel Lookup and Reference functions. However, by investing a similar amount of time in understanding a little about databases they were able to create a more dynamic and reliable solution. Also, the understanding of databases that they gained has enabled them to solve other similar issues more quickly and efficiently than they could have done by using a spreadsheet alone.

Discard the Card Index
In terms of hours of usage, word processing may well be the most used of the office applications, and often the cause of the greatest amount of inefficiency and wasted time. Only recently, with the growing concentration on document management, have many practices started to address this issue. For years, practices have held detailed information about their clients centrally in accounting, tax or practice management systems, yet have used card indexes, Word AutoText, personal contact lists or the Blue Peter method (here's a letter to the same client I did earlier) to get the addresses for those same clients into their letters. Firms with a modest amount of database knowledge have either been able to automate links between their WP applications and their databases, or have at least realized the potential, and used a third party to help them do it.

You can obtain great benefits just from using databases to work with other applications but you might also want to explore the use of databases in their own right. It is possible to set up a simple database with a few tables and queries, without necessarily needing to know how to work with complicated forms and reports – the Microsoft Access wizards can help you create simple queries, forms and reports. If you need to create a spreadsheet that requires the user to enter significant amounts of structured data then a database may well be the better route to take.

A Key Weapon in Your Office Software Armory
There does seem to be a distinct reluctance amongst many accountants to get to grips with databases. Where they are happy to use spreadsheets for almost everything, databases tend to be used far less frequently - even when they should be the natural choice to solve a particular problem.

Databases seem to be seen as the province of developers where spreadsheets are OK for all users. However, in many respects the two applications require similar levels of skill. The different perceptions may result from the lack of inherent structure in a spreadsheet. To create a reliable, efficient and well-constructed spreadsheet is often as difficult and time consuming as creating a database.

However, unreliable, inefficient and badly-structured spreadsheets may still appear to work. In contrast, if you get the basic structure of a database wrong, it soon becomes obvious and you will almost certainly need to sort the structural problems out before proceeding. This shouldn't put you off - the time spent getting the structure of a database right at the beginning is likely to be far less than that required to sort out an over-complex spreadsheet.

You don't necessarily need to become a database expert yourself, but understanding how databases work, and what they are capable of, could help you identify important opportunities for both you and your clients to solve a range of IT problems efficiently and reliably.

You may like these other stories...

K2 Enterprises has announced its 2014 technology awards in 27 categories. The only clear message may have been that there was no clear message in a field marked by many good ideas, but no unanimous winners.The company, which...
We're all about QuickBooks this morning. First, read this late-breaking news from John Stokdyk, editor of AccountingWEB (U.K.), who is attending the QuickBooks Connect conference in San Jose, California. Then, for more...
Technology—specifically internet technology—has a record of disrupting tried-and-true methods of operation in ways that we often don't foresee. Look no further than the recent HBO announcement that they *gasp...

Already a member? log in here.

Upcoming CPE Webinars

Oct 30Many Excel users have a love-hate relationship with workbook links.
Nov 5Join CPA thought leader and peer reviewer Rob Cameron and learn ways to improve the outcome of your peer reviews while maximizing the value of your engagement workflow.
Nov 12This webcast presents basic principles of revenue recognition, including new ASU 2014-09 for the contract method. Also, CPAs in industries who want a refresher on revenue accounting standards will benefit.
Nov 18In this session Excel expert David Ringstrom, CPA tackles what to do when bad things happen to good spreadsheets.