ACCESS-TU-1
Access Database: Tutorial Introduction

3. Creating a New Database

• 3.1 The Database Window
• 3.2 Creating a New Table
• 3.3 Designing a Table
• 3.4 Defining the Table's Fields
• 3.5 Saving a Table
• 3.6 Entering Data into a Table
• 3.7 Creating a Second Table

3. Creating a New Database

To start Microsoft Access, double-click on its icon in the Microsoft Office window on the Windows desktop.

OR

If you have the Microsoft Office Manager button bar on your screen (usually top right), you can simply click on the Access button.

When Access starts, it usually displays a Cue Cards window. Later, you may want to use the options on this to help you find out more about Access; for these exercises, however, you need to go direct to the Access main screen. To close the cue cards window:

Double-click on its control box (at the left of the MS Access Cue Cards title bar).

You are now at the Access opening screen, which contains two menu titles: File and Help.

To create a new database:

	Click on File to pull down the File menu.
	Select New Database from the menu.
	Type in a name for the new database and press [Enter].

3.1 The Database Window

Figure 2 - Database Window

The Database Window (see Figure 2) allows easy access to all objects that are stored in the database.

The tabs down the left-hand side of the window represent the different Access objects. The Table tab is selected by default. The types of object that an Access database can contain are:

Table stores information

Query selects information from the database

Form controls how records are displayed on the screen

Report controls how records are printed

Macro carries out a set of Access actions

Module a collection of Access Basic procedures

To return to the Database Window after working on a different Access object, such as a form or table, either click on the Database Window button (shown right) in the toolbar at the top of the screen or use the [F11] key.

3.2 Creating a New Table

A table contains information that is stored in the database. A database can contain one or more tables of related information. Each table is divided into columns or fields such as tutor no.

To create a table:

	With the Table tab selected, click on the New button.
	Click on the New Table button (shown right).

The Table Wizards button allows the creation of tables using a set of commonly-used fields. This method of creating tables is not covered in this document.

After clicking on the New Table button, the Design View is shown (see Figure 3). This is where you define all the fields that will make up the table: each row on the screen represents one field. You need to give every field at least a name and a data type.

Figure 3 - Table Design View

In design view, the Design View button in the toolbar near the top of the screen is "pushed in". (Compare the normal version, shown right, with the pushed-in version in Figure 3 below.)

Once you have created the fields in Design View, you can use the Datasheet View button (right) to switch to datasheet view, where you can enter records into the table.

3.3 Designing a Table

Below is a table structure suitable for holding the tutor details. It is important to plan out the structure of each table as this avoids the need to restructure the database later.

Structure of the Tutors table.
Field Name	Data Type	Field Size	Example
Tutor No	Counter		1
Tutor Name	Text	20	John Brown
Room No	Text	7	JG261b
Department	Text	30	Computing Services
Extension	Text	4	2324
Primary Key

Note that you can make the field names quite descriptive, as you can use upper- and lower-case letters as well as spaces.

Each table has a primary key which uniquely identifies every record in the table. In this example, the field Tutor No has been chosen.

Each field must have a data type. The possible data types are:

Text alphanumeric characters (ordinary text) including numbers not used in calculations such as telephone numbers.

Memo alphanumeric characters (usually several sentences or paragraphs).

Number numeric values (whole or fractional).

Date/Time dates and times.

Currency monetary values.

Counter a number which automatically increments for each record.

Yes/No true-or-false values.

OLE Object graphics or other binary information.

3.4 Defining the Table's Fields

To set up the Tutors table from the Table Design View:

	Type in a field name and press the [tabkey] (tab) key to move the cursor to the Data Type column.
	To view the different data types, click on the button on the right-hand side of the Data Type cell or press [Alt]+[downarrow]; then select a data type from the pull down list. Alternatively, you can just type in the first character or two of the data-type name.
	In the Description cell, you may add a brief description of the field for reference purposes.

To move around the Table Design sheet, you can use the following:

• To move back one cell - to correct a mistake, for example - use [Shift]+[tabkey] (shift/tab).
• Press [tabkey] in the Description column to move to the Field Name column on the next row.
• To move directly to a cell, position the mouse pointer inside the cell and click the left mouse button.
• You can also use the cursor keys ([uparrow], [downarrow], [leftarrow], [rightarrow]) to move between cells.

To make the Tutor No field the primary key:

	Select the Tutor No field.
	Click on the Set Primary Key button; a small key symbol will appear to the left of the field name (see Figure 4).

Figure 4 - Defining the Fields in Table Design View

The primary key field is used to uniquely identify each record in the table.

To enter the Field Size information, you will need to use the Field Properties pane at the bottom of the screen. This pane can also contain other additional details such as Default Value, Format, etc.

For example, to enter the Field Size for the current field:

	Click in the Field Size box in the Field Properties pane. OR press [F6] then use the cursor keys to move to the box.
	Enter the field size (see Figure 4).
	To return to the main table-design sheet, click on the cell you want OR press [F6] again.

3.5 Saving a Table

If you have not previously saved the table structure, and you attempt to close the Table Design window or change to Datasheet View, Access will prompt you to save the table. Alternatively, you can save explicitly as follows:

	Select the File pull-down menu, then Save.
	Name the table Tutors, then click on OK.
	Select File and then Close to close the table.

3.6 Entering Data into a Table

You enter data into a table in datasheet view. There are two ways to switch to datasheet view; use the appropriate one of these to switch the Tutors table you have just created into datasheet view:

If you are still in Design View, click on the Datasheet View button.

OR

From the Database Window (Figure 2), choose the Table tab, select the Tutors table, then click on the Open button.

Figure 5 - Datasheet View

The Datasheet View window will be displayed (see Figure 5). This is very similar to a spreadsheet, with each column representing a field and the rows representing complete records.

Enter information for a few tutors into the Tutors table. To move between the different cells, use the standard Windows actions and keys - [tabkey], [Shift]+[tabkey], [uparrow], [downarrow], [leftarrow], [rightarrow] and [Enter]. Records are saved automatically as you enter them.

If you want to switch between Design View and Datasheet View whilst the table is open, simply click on the appropriate button.

When you have finished, return to the main menu:

Pull down the File menu and select Close.

3.7 Creating a Second Table

The database now needs a second table to hold course details.

Set up a Courses table using the following information:

Structure of the Courses table.
Field Name	Data Type	Field Size	Example
Course Name	Text	40	Introduction to Word
Author	Text	20	John Brown
Description	Memo		An introduction to word processing using Word 6 for Windows. No previous experience is necessary.
Primary Key

Devise some suitable course details and enter them into the table. Be sure to use the same names in the Author field as you did in the Tutor Name field of the Tutors table.