The Access database application in Microsoft Office is a divisive issue. Lots of “techies” claim that it has no place in serious business application development (they are wrong) and lots of people claim that it is an easy route to business software for pretty much any user (they are also wrong).
As with anything, there is a happy medium, and this series of posts will hopefully enable you to determine whether Access is the right option for your requirement, and also how to get the best from it.
First, some basic database principles:
What is a database?
A simple definition of the word “database” is “a structured set of data stored on a computer”. That’s good enough to go with, but you will notice that a definition like that could apply to an Excel spreadsheet, or indeed to a list of names and addresses held in a Word document. So what makes a real database different?
Essentially, it is the ability to break your data down, and store it in separate tables, and then reassemble it in whatever manner you need for a specific purpose. Think of it like building blocks that can be dismantled and reused constantly – something that is not so easy to do in a spreadsheet. Also you can ensure that there is no wastage and no repetition of data, something known as “redundancy”.
Good database design
The first task when thinking about a database is to establish what your different tables are going to contain – this involves not only analysing the data you have already but also considering what data you want out of the system in terms of reports.
Think of separate entities, or categories of information – customers, suppliers, products, orders etc. Each of those entities might become a table, or even a set of tables. Orders, for example, will usually comprise a “header” table, with the date, order number, delivery address, and so on stored in it, and also a “detail” table, with the items on the order along with their quantities and prices.
Orders are a very good example of the principle of database design – you have a customer, who may place many orders with you, so having a single customer record linked to the order avoids having to constantly repeat those details. If their address changes, you update their record and that change will be picked up on all future orders.
You also have multiple products on the order, again with a single product record for each one. Any changes in the description of the product can be entered in one place and picked up on all future orders, and the same applies to default prices and so on.
How about we just build something?
OK – I think that the easiest way to learn Access, and the principles of basic database design is just to go ahead and do it, so this series will walk you through that process. Together we will build a simple order processing system.
One caveat – in order to make the thing work, we will need to use some code. I’m not going to get diverted into how that code works, as that is something that you can practise for yourself and there are many books and online resources that you can use. The code we will be using is very simple, and it should be fairly intuitive. We’ll soon find out?
Ready? Then go back to the top of this post and move to the next article in this series Access Database Basics …