Creating the table (design view):

In the design view you will create the fields for each category of the kind of information you will include in your database. Once you name your field, you should also select the Data Type in the drop down menu so the database knows how to treat the information in that field such as a number, date, or text. See below for more information on data types. Lastly, you can also enter in a description of the field though this is optional. To create a new field, simply click in the next row of the table while in table design view (shown in the image below). Note that whenever you want to alter the structure of the table, you must use design view.

1. Data Types
Some Data Type entries will require you to give additional information. The data type can also dictate the kind of information you can enter and its format.

Text: Enter any text, number or symbol. Character field up to 255 characters.

Number: Enter only numbers.

Date: Enter the day, month and year at MM/DD/YY format or other date format. Using the field properties Format (see below) drop down menu you can set the format of the date entered.

.

Other Data Types:

• Yes/No - allows only values for true or false
• Memo - allows more text in the field entry than the text data type, up to 65,535 characters
• Currency - formats as a specific type of currency
• AutoNumber - automatically increments the field or assigns a random number
• Hyperlink - formats the field entry as a hyperlink

Note that OLE Object and Lookup Wizard data types are beyond the scope of this tutorial. See Microsoft Access Help for more information on these data types.

Note:
There are different criteria under the General tab for setting up characteristic of each Data Type.

2. Field Properties
At the bottom of the page appears the field properties. To set the field's properties make sure the field is active by clicking on that field. A small black triangle appears pointing to the active field:

In our example on "student id", on the General tab several of the field properties are completed for you based on the data type you selected. In our studentID example with number defined as the field type I defined the field properties as follows:

• Field Size as a Long Integer
• Zero Decimal places
• Default value as 0
• Marked that the field must contain data
• The field is indexed for easier searching and allows no duplicates

3. Adding, modifying, rearranging order, and deleting fields

To add a new field, simply click in the next empty row beneath the last completed field, name the field, define its type, type a description, and set the properties as desired.

To rearrange the order of the fields, click on the grey square to the left of a field to highlight the row. A small black triangle appears in the grey box. Click and drag the black triangle up or down to rearrange the placement of that field.

To modify a field click in the desired area you want to modify, and modify accordingly.

To delete a field, click on the grey square to the left of the field to highlight the row and press delete on the keyboard.

4. Primary keys

At this point you should save your work. Choose File->Save. When you do the program asks you to define a primary key. Primary keys are essential for relational databases but relational databases are beyond the scope of this tutorial. Go ahead and click yes to define a primary key. This will add an extra field with a field name "ID." This field will simply begin with the number "1" and increment with each subsequent additional record. This way if you decide to create related table set a later time, you can do so.

Top of Page

5. Table Data Sheet View: Adding and deleting records

There are two ways to add records to your database, through the table in the data sheet view or through forms. To add records in the table data sheet view click on the data sheet view button while in table design view, located in the upper left hand corner: . This changes the view to look much like a spreadsheet so you can see the individual records in your table. To add a new record, click on the empty lower most row. If you defined a primary key, the data in that field will say "(Auto Number) do not enter anything in this field, Access will do this for you. Click in the box where you want to enter data. You can tab from one field to the next or use your cursor to activate another field in order to enter data.

To delete a record, click on the small grey square to the left of the row you wish to delete, this will highlight the entire row:

Button to Select the Row

Selected Row

Press delete. Access will ask you if you are sure you wish to delete the record. Click yes.

Top of Page

6. Sorting records:

You can sort records alphabetically or numerically based on the field you specify. While in data sheet view, simply click on the field name to select the field.

Click on the Field Header to select the field

The field is now selected

Click on one of these buttons to sort the field ascending or descending.

Top of Page

Back to Index

On to Queries