Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

IT Casebook: The best spreadsheet Add-In? Microsoft Access. By Simon Hurst

by
27th Feb 2007
Save content
Have you found this content useful? Use the button above to save it to your profile.

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. The £10 Access for Accountants guide from AccountingWEB is designed to help you get to grips with this aspect of databases.

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

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, categorise 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 categorisation data, and then created a simple database query to produce the newly categorised table of data. They then linked an Excel pivot table to this query so that they could analyse 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 realised 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 armoury
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.

Simon Hurst's database tutorials

  • A pain-free introduction to Microsoft Access
  • Build a trial balance in Access
  • Create your own extended TB application (Part 1)
  • Create your own extended TB application (Part 2)
  • Create your own extended TB application (Part 3)
  • Create your own extended TB application (Part 4)
  • Excel clinic: Link drop-down menus to MS Access
  • ProductivITy Tip: Sorting by financial year in Access

    The Knowledge Base - resources on AccountingWEB

  • Access for Accountants - Introductory £10 tutorial

    About the author
    Simon Hurst is a former chairman of the ICAEW IT Faculty and runs The Knowledge Base, a consultancy dedicated to helping practitioners make effective use of technology. He is also the author of AccountingWEB's Office ProductivITy Kit and 100 Best time saving ways to use Microsoft Office. 100 Microsoft Office Time-saving Tips'.

  • Tags:

    Replies (0)

    Please login or register to join the discussion.

    There are currently no replies, be the first to post a reply.