Articles on all things data...

Microsoft Access basics – tables, forms, queries and reports

 October 2

by Andrew Richards

This article has been devised to answer the questions that everyone has when they start using Microsoft Access for creating databases, or when they first begin to use existing Microsoft Access databases.

It’s designed to fill in the gaps left by the plethora of great articles on the web which answer the complex questions, or the challenges posed by developing a database, but often assume that the fundamentals are already grasped. In fact, in our experience, it’s all too easy to assume that people already get the basics when that’s not always the case.

So let’s start at the beginning, with a guided tour of the fundamental objects you’ll encounter when working in Microsoft Access.

An overview of the objects in Microsoft Access

When you create a database in Microsoft Access, you have a number of different types of object: tables, forms, reports, queries, macros and modules. You may also have – depending on which version of Microsoft Access you’re using – data access pages. But for the moment, we’ll stick to the basic six types of object and go through each of these in turn.

Tables store data. Nothing else, no formatting, no sorting, no filtering, no calculating, just storing. The most basic, fundamental task of a database is to store data for easy retrieval and management, and it’s tables that do this donkey-work. If you’ve ever worked in Excel, then a table will be familiar to you in appearance – they look rather like Excel spreadsheets:

A table in Microsoft Access

Forms make that data available on the screen. With a form, you can view and edit the data, display it nicely, sort it, add to it, delete it and so on. Forms let you work with your data. They don’t hold any data – they are just a tool for viewing the data in your table. Change something in your form, and you’re actually changing it in your table. Yes, you could – theoretically – just work with the data in your tables directly, like you would work with data in an Excel spreadsheet. But remember that tables don’t sort the data or present it nicely – so you’ll quite likely end up looking at very old data that’s no longer relevant (because the new data is a long way down the table), or data displayed in ways which aren’t very intuitive. And if you open a load of data in a table, there’s a chance (a tiny one, but a chance nonetheless) that you’ll then delete a load of data… That’s a bad thing. And it’s much less likely to happen with a form.

Here’s an example of a form in an Access database – this one comes from a database that we built for a university.

The course marks analysis screen shows a visual representation of marks for any course, and allows comparison with previous years to check for consistency in standards and marking

Reports also display your data, but on paper. Unlike Forms, Reports don't allow you to edit the data - they are designed to be static. After all, once you've printed your data on paper (or as a PDF) it's going to be pretty static, so Reports reflect that.

Whether it's a product catalogue (as in the screenshot below) or a staff directory, an invoice or a manufacturing docket, if it's data on paper, it's a report.

A report in Microsoft Access

Queries extract and link the data. Think about Amazon's database. It has (maybe) a million products, a hundred million customers and a billion orders. The odds are that you're not going to want to move this data across the internet very often - it would just take too long, and most of it would be irrelevant to what you're actually wanting to find out. So the job of a query is to extract just the information you need for you to work with. So when you log in to Amazon (other online - and even real! - stores are available) and ask to see your order history, a query will have the job of extracting relevant information about the customer (you), the orders (but only those for the past 60 days, or whatever) and the products (but only those you've ordered in that time frame). It passes that information to the form (to display on screen) or the report (to print out). And queries are dynamic - by which we mean that if you make a change to the data - perhaps correcting a typo in your address - they pass that changed data back to the correct table.

Macros are one way of extending what you can do in Microsoft Access. For example, when you create a form, you can add a button to the form - perhaps to open another, related form or report. When you add a button, Access, by default, will launch a wizard which guides you towards getting the button to do what you wanted. But there's a limit to what you can achieve like this. For example, what if you wanted the button to open both another form and also a report? The wizard won't let you do this - but a Macro will.

You may have used macros elsewhere - perhaps in Word or Excel. But macros in Access are slightly different. When you create a macro in Microsoft Excel, for example, you either record it (essentially telling Excel "Watch what I'm doing and write it down so that you can repeat these actions later") - which is what most people do, at least to begin with - or you write the instructions yourself, using a programming language called Visual Basic for Applications.

But in Microsoft Access, you can't record a macro. So rather than forcing you to learn the programming language, Microsoft have invented a new way of creating such instructions. Instead of having to write the code yourself, macros provide a drop-down list of instructions, and you can select from that list. So, for example, you might select the instruction "OpenForm". This then enables another drop-down list, showing all the available forms. Another instruction would be "OpenReport" which provides a list of all available reports to choose from.

In this way, you could select "OpenForm" twice to open two forms, or "OpenForm" and then "OpenReport" to open both a form and a report, thus achieving what you wanted your button to do, but what you couldn't do by simply using the button's wizard.

Modules are much more akin to the world of Excel macros designed above - they are places where you store VBA code. But as we noted, you can't record a macro in Microsoft Access, so modules are where you'll find all the VBA code you've written yourself, plus, potentially, code written by various wizards within Microsoft Access.

Why would you write your own code from scratch when you have the macro option? Well, remember that macros provide drop-down lists of actions such as "OpenForm" and "OpenReport". This list, while very useful, is limited. Writing your own code is like speaking a language - the limit to what you can say in that language is really the limit of your imagination (and skill!). But a drop-down list can only ever provide a subset of those choices. So, many people start with macros, but eventually progress to writing their own code - in which case, they'll be storing that code in modules.

In conclusion

So that's it for the introduction to Microsoft Access objects.

Do note that it really is just an introduction - there are other types of object that you'll come across, and in one or two places I've simplified the picture a little for ease of explanation and understanding. But this set of descriptions should at least get you started.

In future articles, we're going to take a look at the difference between "normal" tables and "linked" tables, and at what relationships are and why you need them!

Get in touch!

If you've got questions, feel free to get in touch, either through the comments box below, or via our contact page - we'd love to hear from you. Or, if you want to know more, why not talk to us about organising a Microsoft Access course? And in the meantime, enjoy getting to grips with Microsoft Access!

share this

Related Posts

Microsoft Access basics: How to manage Linked tables

Microsoft Access basics: How to create Linked tables

Microsoft Access basics: Why create Linked tables?

Sign up now to be the first to hear about new articles!