Notes on Microsoft Access 97 - Part 1. An Introduction to Access and Tables

Access is a database, a program used to store information about a large number of items. As well as displaying the data kept about each item you can ask the database to select various items that satisfy certain criteria (e.g. selecting all the sales representatives from a certain town) or sort them into order. Getting the database to search the list of items to find ones that match criteria is called applying a query to the database. Access is one of the most powerful databases around as it offers so many features, and, as such, it has become the industry standard.

Tables, Records and Fields

An Access database contains one or more tables. These are lists of items and each table consists of a list of items of the same type. A database of employees in a factory might contain a table listing the employees themselves, one listing the salary levels for the company, one listing the details of the departments that the employees work for etc.

Each item in a table is called a record. Each piece of information stored about each record is called a field. The following diagram shows a table called Friends consisting of three records, each with four fields:

Starting an Access Database

When you activate Access, you are faced with the initial menu. This gives you the choice of starting a new (blank) database, opening an existing database (to edit it or to ask it queries), or to use the Database Wizard.

The Wizard is a program built in to Access which takes you by the hand and helps you build the database. It does the hard work for you - all you have to do is "fill in the gaps". I won't talk about it here, as it is fairly easy to use. Try it for yourself.

A list of the most recently used databases appears under the Open Existing Database option. To use one of these, click on the name. Alternatively, one of the options on that list is "More Files" which lets you choose from all the available database files using the normal Open dialogue box (the sort of thing you see when you choose Open in Microsoft Word).

Whichever of the three options you choose, to proceed to the next step, click on OK at the bottom of the box. I shall proceed on the assumption that you have chosen to start a blank database.

Starting a blank database

Curiously enough, the first thing Access gets you to do when you create a new database is to save it! This seems strange when it is blank, but as the database consists of a variety of tables, queries, forms (explained later), it makes sense to choose the file name "up front" so that Access can save these items automatically as and when they are created. The database file will lump all these things together in the same file.

The name that Access suggests for your database is db1 (or db2, db3 etc. if the file db1 already exists), although you are free to choose any other file name, of course. When you have chosen the file name for your database, you are faced with the listing of the main components of the database:

The diagram on the right shows the component listing for a database that I created on Aphasia patients. When you start a new database, the large white area will be blank.

The six tabs at the top of the listing (Tables, Queries etc.) show the different listings that you can choose. The diagram shows the Tables tag selected, so that the white area shows the tables present in the database.

(Fig. 1)
In this example, there are two of them, Papers and Patients. If you click on the Queries tab, Access will list the queries that the database holds, or forms for the Forms tab etc.

Next to the white area are three icons, Open, Design and New. The Open icon lets you view the contents of the item (whatever it is). The Design icon lets you edit the structure of the item (for example, adding another field to a table or altering a query so that it does something different). The New icon lets you create a new table, query or whatever.

Creating a New Table

The first thing that you should do when you start a blank database is put a table of data in it. Select the Table tag and click on New. The following dialogue box appears:

You are asked what method you would like to use to create the table. As before, there is a Wizard, which is a program that creates the table for you (you still have to enter the raw data in the table, of course).

(Fig. 2)

You can import a table from an existing database. You can also create a table which is a copy of another table (possibly in another database) and linked to it, so that when the table is changed in one database, it also changes automatically in the other as well.

The two options that create a simple table from scratch are Datasheet View and Design View. I shall concentrate on Design View for the time being.

The next stage is to define the list of fields that will make up this table. You are presented with a series of slots for you to fill in the fields - the diagram to the right shows a table in the process of having its fields defined.

Each field has a name (first name, surname, DOB in the example) and a data type. The data type indicates what sort of information can be stored in that field. The "first name" field, for example, is going to hold letters of the alphabet, so its data type is set to Text. The DOB field is going to hold a date. Similarly, if a field had been included to hold a person's age, then its data type would be set to Number.


(Fig. 3)
To set the type of the field, click in the relevant slot in the Data Type column. A small grey down-arrow symbol appears. Clicking on this produces a drop-down list of data types.

Here I have created a field called Married, and I want to set it to Yes/No (I shall ignores divorces, separation etc.), so I click on the grey arrow and choose Yes/No from the list that presents itself.

Most of the types are fairly self-explanatory - text, number, date/time, currency etc. A memo field is similar to a text field. An AutoNumber field is similar to a simple number except Access automatically gives each record in the database a different (and unique) number. For instance, if you create a table with an AutoNumber field, then the first record in the table will have 1 in this field. The second will have 2 here, the third 3 etc. This is marvellous if you want a field which you can guarantee will be different for each of the records in the table. More on this later.

Beneath the grid for defining the fields themselves is the Field Properties box. This lets you set the exact properties of each field. For instance, if you create a Text field, Access assumes that you will require 50 characters (letters) for the field.
If it is a first name, then 50 characters is more than necessary, so you can click on the field and change it to 20, as shown.

I won't say too much about these field properties. They are different from one data type to another. For instance, a Yes/No field doesn't need a Field Size property, as it is always either "Yes" or "No". You will gradually meet these properties as you go along. Mostly you can ignore them, anyway.

The last thing to mention is the Description slot of the field creation grid (in Fig. 3). This lets you enter a description for the field explaining what it is for. This is purely optional - a field called "surname" needs no description, but one called "DOB" should probably be explained.

When you have finished adding and editing fields in the table, click on the icon in the top right corner of the field selection grid (Fig. 3). First of all, Access asks you whether you want to save the table that you have created (presumably you do), and when you click on Yes, you are asked to give the table a name. The default name is Table1 (or Table2 etc. if that name has already been taken), but I'm sure you can think of a better one.

A Primary Key

Just one slight fly in the ointment before you go back to the component list. An error message has appeared indicating that you have no Primary Key Field. What the blazes is that all about?

A primary key is a field in the table that Access can use to identify each field uniquely. For instance, you may have several John Smiths in your database, so how can you tell them apart? Access suggests that you create a field, perhaps a code number, for each record, with no two records having the same value in this field. If you mark this field as the primary key, then Access will make sure no two entries are ever the same.

A good example of a primary key field is one set up as an AutoNumber type. This will automatically fill in a code number for each record, starting at 1 for the first.

To turn any field (whether it is an AutoNumber or not) into a primary key, move the arrow over the small grey rectangle to the left of the field name and click the right mouse button.

This gives you an option for creating the primary key, and while you're at it, you can use it for adding/deleting fields in the grid (i.e. inserting or deleting rows). Cut and Copy work exactly as you would expect - use them for moving fields between tables.

Now you have created a primary key, you can happily click on the cross to stop
designing the table without Access moaning about a primary key. Of course, primary keys are purely optional, but you have to put up with Access reminding you at every stage!

Clicking on the cross returns you to the components list (shown in Figure 1), where you will see that a new table has appeared in the large white space (or joined those already in it!) The table, is however, empty. The fields have been set up, but there is no data in them. Click on the Open icon to view the (as yet non-existent) contents of the table in Datasheet view:

The table has only one row in it and that is empty! The height (being a number) appears as 0, and the automatically generated code number has yet to be filled in. Now is the time to type the data in - don't worry about running out space; as soon as you start typing in the empty line, another empty line appears below it ready for the next record:

Access always marks the blank line where the next record is to be entered with a star in the grey box on the left - just in case you can't recognise a blank line when you see one!

Exiting the Table

To stop entering data in the table, click on the lower of the two crosses in the top right corner of the screen. Only use the top cross to leave Access altogether!

Designing the Table in Datasheet View

Now that you have seen the Datasheet View (the view used for entering the data itself), I can go back and explain one of the options that you saw in Fig. 2, namely the Datasheet View option on the New Table dialogue box. This lets you specify the field name at the same time that you enter the data itself. The table appears in Datasheet view as above, with the fields called Field1, Field2 etc. Move the mouse pointer over one of these field names and click the right mouse button to rename the field itself.

However you design your tables, you needn't worry about saving them on the disc. You will find that Access does that automatically when you exit the program. It will include all the tables in the one file whose name you specified when you first chose the Blank Database option.