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...

Event Date: May 29, 2014 In this presentation Excel expert David Ringstrom, CPA brings you up to speed on the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both...
No field likes its buzzwords more than technology, and one of today's leading terms is "the cloud." But it's not just a matter of knowing what's fashionable. Accounting professionals who know how to use...
There is a growing trend of accountants moving away from traditional compliance work to more advisory work. Client demand is there, but it is up to the accountants to capitalize on that. What should accountants' roles be...

Upcoming CPE Webinars

Apr 22
Is everyone at your organization meeting your client service expectations? Let client service expert, Kristen Rampe, CPA help you establish a reputation of top-tier service in every facet of your firm during this one hour webinar.
Apr 24
In this session Excel expert David Ringstrom, CPA introduces you to a powerful but underutilized macro feature in Excel.
Apr 25
This material focuses on the principles of accounting for non-profit organizations' revenues. It will include discussions of revenue recognition for cash and non-cash contributions as well as other revenues commonly received by non-profit organizations.
Apr 30
During the second session of a four-part series on Individual Leadership, the focus will be on time management- a critical success factor for effective leadership. Each person has 24 hours of time to spend each day; the key is making wise investments and knowing what investments yield the greatest return.