About this lesson
In this lesson, we will learn about tables in Access, and then setup our first table and add fields.
Download this lesson’s related exercise files.Creating a Table and Adding Fields - Exercise
43.6 KB Creating a Table and Adding Fields - Solution
99.5 KB TechGurusTicketing.accdb
552 KB TechGurusTicketing - Complete.accdb
412 KB Ticket_Example.txt
309 B Trip_Example.txt
Creating a Table and Adding Fields
Tables are the backbone of Access and understanding how they work is extremely important.
A table is where we store all our tabular information in columns and rows. We can have multiple tables in one database and create relationships between tables.
The column headings in a table are referred to as fields in Access. We can add new fields, delete fields and modify existing fields.
When to use
We use tables whenever we are creating a database. Tables are where all our information is stored.
Creating a Table
The first table we are going to create is a table that holds all of the information related to tickets that the IT Helpdesk 'TechGurus' logs. We have the ticket information for the first ticket stored in a text file. Let's take a look at it.
- Download the file 'Ticket_Example.txt'.
The text file shows information related to the customer 'Claire Smith'.
- Review the ticket information and the fields.
- Some fields such as priority, have multiple options.
We will use these fields as the column headings in our table.
- From the Create tab, click on Table.
The new table is given a default name of 'Table1'. Let's rename the table.
- Right-click on the table name.
- Select Rename from the list.
If we choose to do anything else at this stage, such as switch to Design view, we will be asked to give the table a name.
- Name the table 'tbTicket'.
- Click OK.
It's recommended that you use a naming convention to name different elements in the database to make them easier to identify. For example, if it's a table use the prefix 'tbl'. If it's a form, use the prefix 'frm', etc. Or you can use your own naming conventions.
It is also recommended that you do not include any spaces in the table name to prevent issues further down the line.
- From the Home tab, click Views and select Design View.
Working with table fields
Currently, the only field in the table is 'ID' which has a data type of 'Autonumber'. Data types are determined by the type of data contained in the column.
- Click in the Description field and type 'Unique code for the ticket'.
The ID field in this table is a very important field. This field will automatically generate a number that is unique to this trip every time we add a record. The ID field purely exists in the database and isn't visible to users.
- Click on the ID field.
The Field Properties pane is displayed underneath. This pane shows the different settings for the field and can be customized.
Adding additional fields
Let's add another field for the Ticket number.
- Click in the box underneath ID in the Field Name column.
- Type 'TicketNumber'.
- In the Data Type column, click the drop-down and select Number.
- Add the description 'Ticket number'.
Modifying Field Properties
- Click on the TicketNumber field.
- Modify the field properties:
This property defines if this field is required in order to save the record. In our example, every ticket must have a ticket number.
- Click in Required.
- Click the drop-down arrow or double-click to change the property to Yes.
Allow Zero Length
This property defines if we are going to allow ticket numbers of zero length.
- Click in Allow Zero Length
- Change this to No.
This property defines if the field is going to be indexed. Indexing improves the efficiency of the database when searching through large numbers of records.
- Click in Indexed.
- Change this to Yes (Duplicates OK).
Lesson notes are only available for subscribers.