Share this content
Tags:

Don't be frightened: Learn to live with databases

27th May 2005
Share this content
Kashflow logo

During May, AccountingWEB and IT Zone members confronted their fear of databases, following promptings from Simon Hurst to stop using spreadsheets for inappropriate tasks.

Along with long-time AccountingWEB member Neil Eglintine, Hurst has been spearheading the database crusade since 2001. But the profession has generally stuck its fingers in its ears, started humming to itself very loudly and buried its head in Excel.

In an Excel seminar at the Digita user conference at the end of April 2005, Hurst returned to the topic: "One of my pet hates is when people use spreadsheets when they should be using databases. If you're storing lots of data or doing data input with lots of transactions, use Microsoft Access instead," he said.

Accountants tend to overuse Excel because they're familiar with it, and aren't familiar with Access, he explained. "But the person who creates an elaborate spreadsheet database could probably become a world expert in Access in the time it took them to do it in Excel," added Hurst, who has written several tutorials on the subject.

The author of AccountingWEB's FD's Diary wouldn't reveal his name, but later that day he spoke out as a member of Spreadsheets Anonymous: "I admit it, I'm an Excel junkie. I always have been' since about 1990."

After 20 years of spreadsheeting, the FD said he was willing to try and make his acquaintance with Microsoft Access. Eglintine was quick to offer his support, and predicted the FD's would experience some initial pain. "Anyone who has a high degree of competence in Excel will need to relearn their approach to problems."

A month later, the FD reported back, "The first thing I learned is why I don't use it. It requires prior aforethought, and by and large my spreadsheets just grow. I know they shouldn't, but they do. I might be very tidy about how they look and I might do all the auditing of them I can, but sometimes I don't know what I'm trying to achieve when I set out and I'm not sure Access is very good at handling that.

"I need to know how to think Access, not just do Access. Because that looks like the hard bit."

As an introductory concept, people will often explain that database tables are not much different to what you see on a spreadsheet. But Eglintine explained: "In Excel, you tend to store manipulate and display your data all in the same place. In Access these are separate functions. Excel junkies tend to try and use an Access table like a spreadsheet and that doesn't work."

During the month, other members of AccountingWEB came forward to add their thoughts. Steve Taylor was a member of the Hurst/Eglintine tendency and offered the FD some tough love: "How can Accountants have problems with relational databases?" he asked. "Don't you use an accounting package? What is an accounting package? It is a relational database."

Joyce Beck advised the FD to hold his nose and take a deep breath and import some customer details or transactions into Access. "Just PLAY with the resulting database, rummage around the help file and you'll probably soon realise why people tell you that Access is brill' You can do an awful lot of useful stuff without going bonkers."

Simon Murphy suggested a similar course, and like Beck suggested that instead of signing up for a generic training course, the FD should find a local Microsoft-trained developer to show him how to progress with what he wanted to do.

"Part of the reason why Access seems so hard for Excel users is that you can be highly productive in Excel quickly with limited knowledge of any software development fundamentals (this comes back to haunt later)," said Murphy.

"To understand databases you need to understand data, relationships, attributes etc. None of it is at all difficult, but you just need to know about it. If the person teaching you can relate it all back to Excel then it's much more simple to take on board."

D Carmichael took the plunge, and once someone from his IT department took him through the concepts with a pencil and paper (and a lot of lines and arrows), it clicked. "I've never looked back since," he wrote.

In a parallel thread on Any Answers, Nicholas Robinson popped up with a query about what he should do with a client database he maintained in Excel. Could there be a better practical illustration of the database debate?

Alastair Harris suggested Robinson list those things his spreadsheet can't do which he would like it to - or is it inefficient/cumbersome/fiddly/badly maintained, he asked. "Client records" sounds like a lot more than a basic client contact system, Harris added and suggested starting by defining some basic requirements and then talking to mainstream practice developers such as IRIS, Sage, Digita and Star. " If nothing else, they will help you to understand what sorts of things are possible."

Several sales representatives took Alastair at his word and put forward their credentials. However Trevor Green took a different approach: "I have solved your problem of relationships and linking regular tasks with client records by importing my previous data into various Access tables." Because Green's firm only has once Access licence, it extended the application by creating web pages with Dreamweaver to display the results for other users via a web browser.

Gary McHale took a similar route. While Green said it cost quite a lot to have the code written, McHale felt his bespoke solution was quite cheap.

No such shortcuts for Kevin Ringer. He took on the task himself: "I built our database using Access. I'm not at all competent with Access and so all I built was the equivalent of a basic card index database. It could be made much more powerful ' but it will take time to learn how to do that, and there's always something more pressing to get on with. Having said that, the database does what we need."

So, it may take time and intellectual effort to get to grips with Access, but it can cure a simple functional need that is currently not met by a spreadsheet.

Since he is an accountant, Nicholas ought also to consider Bradley Hardacre's response: "Why re-invent the wheel?" Setting aside the educational challenge, it's a delicate cost justification exercise to work out whether it's better for Nicholas to build his own client database, commission one from a database expert, or go for an off-the-shelf commercial solution.

But that's an argument for another day.

John Stokdyk

Related material and websites

AccountingWEB and ExcelZone will continue to explore this territory and will publish Expert Guides on database applications and integration with Excel in the coming months. If you would like to keep up to date with the database debate, click the button on the right to subscribe to our fortnightly ExcelZone newswire. The subscription routine will take you to the AccountingWEB home page when it's finished.

Subscribe to the ExcelZone Newswire

Tags:

Replies (3)

Please login or register to join the discussion.

avatar
By Anonymous
31st May 2005 13:26

Places to start with Access ...
I'm primirily an IT person but with an interest in Finance. I can recommend Access as a great way of learning about databases but like most Office applications there are probably more features than you need which can be a bit daunting when you first use the program.

There are a lot of books on the market which explain relational database theory - I'd recommend at least doing a little Googling to find out about primary keys, fields and normalisation it will really pay dividends if you are seriously contemplating spending the time to learn Access.

The only thing I would say against Access is that I would not use it for an enterprise scale application and it does not have the robustness of its big cousins SQL Server and Oracle (scalability, performance, recovery etc). But it's great if you want a small to medium-sized database for your workgroup.

Thanks (0)
avatar
By Richard Willis
31st May 2005 18:06

What about the workers?!
I am pretty good at Excel and can get by in Access. The reason why most of our in-house ad-hoc work is done in Excel is that I am one of only two people in the place that wouldn't run up the road screaming at the mention of Access.

The problem with Access is that unless one has time to do the full front end bit, people unfamiliar with it will either be unable to use it or will mess it up. As most people in a commercial environment are at least comfortable with Excel, one can knock up spreadsheets which they can and will use intuitively without too much additional coaching required. Protecting the bits that they may feel inclined to mess up is simple, and if they want or need to extract bits for their own use, they can.

Thanks (0)
avatar
By neileg
01st Jun 2005 15:05

Not the whole story, though!
I'm hardly going to disagree with the editorial above, am I?

Bu I have to say that the dedication to Ecxcel goes further than that. I am horrified at the number of reports that I have seen written in Excel by accountants. What about Word, for goodness sake!

Thanks (0)