MS Access Databases

An Introduction to

MS Access 2000

© The Third Age Trust 2004MS Access Databases

1. The components of an Access database

Tables

A table is the basic unit of a database and is an array of data much as you might see it in a spreadsheet. Each column contains a field and each row contains a single record. A record therefore consists of a number of fields. A name & address table could therefore contain the following fields: First name, Surname, Address1, Address2, (etc.), Telephone no., Xmas. Xmas being a Yes/No or True/False field that would indicate whether the person was on your Christmas card list.

Every field can be formatted to be, for example, text, number, date, logical (yes/no), currency and this may be refined by stating how, say, a date shall be shown, eg 03/03/02 or 3 Mar 2002.

A database may contain more than one table. In a database of U3A study groups there might be one table which lists the groups and another table that lists the dates on which they meet. This gives flexibility to the database making it more efficient, easily amendable as things develop and it should also save space.

Queries

Queries are essentially a view of a table or a combination of tables. You can control which records are shown, a sort order, calculated fields and more. Beware though that queries are “live” data and this means that any change you make to a query is also made to the underlying table(s).

For example using the names and addresses database you might need a query that lists only those records which have Y in the Xmas field (ie your Christmas card list).

Forms

Forms are for displaying on the screen and can be used for viewing, adding or amending data. They can be designed so that they look attractive and easy to use. When designing them there is a toolbox enabling the various objects available to be placed on the screen. The underlying data may be a table or a query, again meaning that any alteration to the data will be permanent.

Reports

Reports are for sending to the printer and could be (for the names & addresses) a complete printout of the data suitable for a name and address book or mailing labels.

Macros

Macros comprise a list of instructions but in a simple and easy to understand way. They can be used to “glue” your custom database together.

Modules

These are written in a programming language (Visual Basic) and might well seem rather frightening without any background in programming.

2. A closer look at the main elements

The tables, queries, forms and reports can be viewed in two ways: the design view, which allows you to change this and the final view which is how you will see it in the working database. You can switch views using a button on a toolbar near the top left corner of the screen:

a button like this can be clicked to take you to the design view.

It then becomes this button: which, when clicked, takes you to the finished view.

Tables

1. Open “U3A members demo 2000.mdb” in MS Access. If you do not have this file you can download it at http://www.u3a.org.uk/membership_databases.htm . There is also an Access 97 version there as well. Close the opening screen by clicking on the X at the top right of the window to view the basic Access window:

Fig 1

1 Click on Tables (if not already shown “down” as in screenshot above).

2 Click on U3A and then the Open button (top left of dialog box)

This is a part of what you see; a data table with the field names are at the top of each column and each row representing a single record. The left hand column is the ID number which Access allocates automatically when a new record is entered. Its Data type (see below) is therefore AutoNumber. This is the Access “Key Field” (which it likes to have) and is indicated in the table below with a little key symbol at the left hand side.

Click on the Design button

This is the definition of the table. Each of the fields whose names are in the left hand column have against them in the next column their data “type” – that is whether they are text or numbers or dates, etc. You can at any stage add or delete fields or amend them; the only worry here is that you may lose data or have to go around amending things elsewhere if the field you are deleting or changing is referred to elsewhere in the same database. The items shown in the window below on the General tab are further definitions. For example at the bottom it says that this field is to be indexed and Access should not allow duplicate values of this field.

Have a look at the definitions of some of the other fields by clicking on their names.

Queries

Close down all the table screens and return to Fig 1 above. Now click on Queries.

Warning: A query is “live” in that any addition or deletion you make to a query is immediately made to the underlying table or tables. There is no undo button in Access!

Let us define a query:

Double click on the Create Query using a Wizard:

This screen decides which fields will be shown by the query. If we wish to show the full names of all members who are currently paid up, we just need to move over the Surname and forename. Click on 1 and the arrow button 2 and this will move the Title field to the selected fields box. Do the same for Surname, Firstname and Expiry Date. Click 3.

4 Type in a name for the query, eg “Current members”. Make sure that Modify the query design is selected by clicking it and then click on 5. You will now see the query in its design view:

Now, as shown in the Criteria cell of Expiry Date, enter >Now() which means: only show those members whose Expiry Date (ie the date their membership expires) is greater than today’s date which in Access is Now(). You can run this immediately but better save it because we have added something. Just Click on the save button. Now click on the button top left on button bar and you will open the query and see a table with the members names who are currently paid-up.

Other query criteria

To search for a surname put: =”Brown” or just “Brown” in the criteria place under Surname. To search for any surname beginning with B, enter Like “B*”. The * meaning any number of other characters (this sometimes called a wild card). If you want all those whose expiry date is 30/09/01 enter under Expiry Date = #30/09/01# .

Sorting

Click in the Sort cell below Surname. Click on the drop-down arrow which appears and choose Ascending by clicking on it. If you also want to sort on a secondary criterion, eg Forname (regrettably misspelled) do the same as above. The secondary sort field must appear to the right of the primary sort field.

Forms

Go to the database window in Fig 1. Click on Forms then New

1 Click on Form Wizard.

2 Click on the drop down arrow, scroll to the bottom, click on U3A – the table on which to base the new form.

Click OK.

Now select the Fields that will be needed in the form. For the present purposes just move over Surname, Firstname and all the address – postcode fields. Click the Next button.

Now choose Columnar and click the Next button. Now sample the different options by clicking on the name and, finally, choose one of them and click the Finish button.

What you get is a series of field boxes together with their field name in a text box. A lot of them will be too big (because the length of the field was not made smaller when defining the original table.

The properties box

The form and all the objects on it all have a bewilderingly large number of “properties”. These properties control the appearance, position on the form, source of the data for each of the field boxes and their labels. The form itself also has properties and this shows when you click the square at the top left of the form.

Of the many things that you can define a few are as follows:

Size and position of an object, ie a text box, whether it has a border, background and foreground colours, font, font size, bold, italic, etc., on the Format tab. The Data tab defines where the data comes from, ie which field of which table while the Event tab shows if an event (ie a mouse click) will trigger an action, ie a macro or code module (both effectively mini programs) which will carry out something ie preview a Report.

Reports

Reports work in much the same way as forms except that they are specifically intended to be printed. If you start with a Report Wizard you can then move things around until you have the layout that you want. You can define a Report Header and/or Footer meaning that you can have these appear at the beginning and end of the Report. You can also have a page Header and/or Footer which will, of course, appear on each page printed.

Reports may have calculated fields and, particular useful for some sorts of reports, a total of the quantities in a numeric field, ie number of members (see Gift Aid report).