Databases

Creating a Table

When you first load Access, it will ask you if you want to create a new database, or load an existing one. Choose "Blank database" and then call the database "My Timetable", making sure that you store the file in a sensible place. You should now see a screen similar to this one. Move your mouse over the image to see some descriptions of the controls:-

We need to produce a new table, so click on the "New" button, select "Design View", and then click "OK"

Start to enter the fields into your table, making sure that you don't make any mistakes.

Add the field names into the first column, and choose the correct datatype from the list in the second column. You will notice that the "Field properties" box at the bottom of the window shows different values depending on the data type that you have selected.

As you can see, Access has many options, and is capable of very complex database designs. The good news is that you dont need to use every one, but a well designed database would make use of a lot of these tools. Here is a list of some of the more useful ones:-

Field size

When you select a "text" data type, Access reserves a set amount of storage space to store the contents of the field for every record that you have entered. If you are storing data that contains words that are quite short (for example names) then you do not need to store many letters. Change the value of the field size so that it can store the longest string of letters that you can envisage using. In the names example, 15 is a reasonable setting and saves 70% storage space compared to teh size of 50.

When you select a "number" data type, here are the sizes that are available:-

Byte, Integer, and long Integer only hold whole numbers. If you attempt to store 1.2 in a whole number field, it will be shortened to 1

Single and double are used with decimal numbers. The table above has some rather strange looking numbers in it, which are called "exponential format"

The number 3.40282E38 is the same as 3 followed by 38 zeros!

Format

Access can display certain data types in a special way, for example a date may be displayed as:-

20/08/02 or 20th August 2002 or 20/8/2002 or 20-Aug-02 as well as many more.

Use the drop downlist to choose the most appropriate format for each field.

Decimal places

Only applicable for single and double number types. Select a different value to alter the way the number is displayed.

Input Mask

An input mask will make Access check that the data you are entering fits into a set pattern

Click here to find out more about Input Masks.

Caption

When you come to actually enter your data into Access, the caption will appear next to the empty field. It is simply a label.

Default value

Sometimes, nearly every record in your database will have a similar starting value. To save time, Access allows you to set this default value so that the field will automatically have the correct value without you typing anything in.

Validation Rule and Validation Text

Access can check that the data you have entered is meaningful, for example it may check that a month value fits within the range 1 to 12

Click here to find out more about Validation Rules.

Required

Certain data will be essential if the record is to make sense. It would be silly having an antry into a diary if you didnt include a date.

If you set the required property to "yes", Access will not let you move to another record without setting a value.

Indexed

Access can set a value to be an index. this makes it much faster for Access to sort records or produce queries that contain this value. An Index can also prevent you from entering duplicate values, which would prevent you from typing in the same record twice.

You have three choices for the Index:-

Lookups

We often need to select data from a list of values. To do this in Access we use a "Lookup".

Click here to find out more about Lookups.

Completing the table design.

Here is a complete list of the fields that you should have entered for your new database. Check each one carefully.

Once you have entered each one, you will need to save your table design.

Do not save it yet, there is something you must know first!

Click here once you have entered all of the fields

The Day field can have a lookup to a list of the numbers 1 to 10

Click here to return to the top of the page

The LessonNumber can have a lookup to a list of the numbers 1 to 5

It is good practice not to include any spaces in the field names, use a capital letter at the start of every word instead.

Click here to return to the top of the page

It would be possible to produce a lookup for the subject field, but we arent going to.

Click here to return to the top of the page

Click here to return to the top of the page

Click here to return to the top of the page

Click here to return to the top of the page

Click here to return to the top of the page

Click here to find out about Input Masks

Click here to find out about Data Validation

Click here to find out about Lookup values

Click here to find out how to save your table and add a Primary Key