This month’s Hidden Gem is like a set of car keys- usually hidden in plain sight. Microsoft Access has been part of the main set of programs shipped with Office for well over a decade now, but it’s one of the least-used programs in the suite – even though you may well have paid for it and installed it. There are two reasons why:
- 1) Many small businesses muddle through by using Excel for all their data needs
- 2) And there is a perception – now largely false – that Access requires lots of complicated programming to get to use it properly, even for simple tasks.
It’s time we had another look at Access, and put it to work in your business.
The Target: Access 2010
Whereabouts: Included in Microsoft Office Professional
Modus Operandi: Powerful data management and excellent reporting for unlimited applications across your business.
Access is a database program. Just like Excel, it allows you to enter data and then do lots of useful things with it. However, Excel is fundamentally a single grid of data and is designed for handling figures. Extending its use to the complete management of your business data is rather like using a kitchen knife to undo a screw: we’ve all done it, but it’s the wrong tool for the job, and sooner or later it won’t work properly.
When to use Access
Here are some typical cases when you will have to forsake Excel for Access:
- Lots of data: Excel is limited to around one million lines – which sounds like a lot; but can soon be used up by every client, order and line-item in your business.
- Decision or rules-based validation: Excel will find it hard – although not impossible – to produce complex data handling rules. It can take multiple rows or columns to install logic such as “If box A contains B and box C contains D but E is more than 8, then add F to box G” (which, incidentally, is the sort of logic required to create a purchase rewards scheme).
- Custom interfaces and user-dependent actions: Excel does now have powerful form-filling capabilities, but Access allows for complete interface designs, so that everyone in your business can have dashboards which present them with just the information they need, in just the way which will be useful to them. Furthermore, these interfaces are ‘active’ and can change according to input. For example, something as common and easy to explain as “If a user can’t enter a postcode, open more fields to enter a full address” is impossible in Excel.
- Big updates: Want to change lots of data at once? Excel will need some time for slow chugging away, whereas Access will gobble through your data with ease.
- Attaching files: And what if you want to embed files, links or other assets in individual data fields? Excel will allow you to link to external web pages, but that’s about it. Access data structures mean you can embed and retrieve all sorts of business documents right there in your database.
If you’re not convinced, here are just a few of the problems which everyone in business faces, which Access can solve, but Excel can’t:
- Similar, but not identical data: “The White Horse Pub” and “White Horse Pub, The” are identical to you and me, and can be to Access too. Excel would see them as different records, and never show reports combining the two.
- Illegal data: If someone enters a client number incorrectly in a form, Access can look it up in real-time and spot that it’s the number for another client. Excel would let it go – and that often means wrongly printed invoices and late payments!
- Yesterday’s data: With people sharing and swapping Excel spreadsheets, there’s never just one version of the truth. In an Access database, changes you make are available to everyone, immediately. Nobody’s stuck using outdated information.
Get Started Fast
But what about all the programming? It’s true that a fully bespoke Access database, customised to every requirement of your business will demand a bit of work – but that’s true of Excel, too.
More importantly, there is a wealth of templates available for a range of common business applications – this screenshot is from the Sales Pipeline template which will track and value your client opportunities.
Other instant-install templates include expenses, a contact manager and billed-time lists.
Under the magnifying glass…
There are plenty more reasons to try Access – here are just three:
1) Get data from anywhere, and view it anywhere, too: Access comes fully equipped to connect to other data sources – even non-Microsoft and web-based data sources, so you can mix third-party sources (a supplier database, or local maps, for example) with your own in-house information. Equally, your database will connect to the web, allowing you to view raw datasets or full reports from anywhere, and even take input from colleagues, suppliers and clients who don’t have Access themselves.
2) Endlessly customisable functionality: As well as instant visual designs for your forms, interfaces and dashboards, you can also layer on menus, buttons and functions to better match the natural processes by which you work. Put reports in the order you would use them; move from customer details straight to their order history, or put common tasks right upfront in a top-level menu: Access is effectively a build-your-own-software tool.
3) Lots of building blocks: Even beyond pre-designed templates, you can construct elaborate logic systems which match your business processes without getting your hands dirty with raw code. A raft of tools called IntelliSense give you the building blocks of logical database construction, along with helpful suggestions based on thousands of previous implementations, to help you build reliable, scalable and powerful queries and reports.