MS Access - Create Tables


Advertisements

When you create a database, you store your data in tables. Because other database objects depend so heavily on tables, you should always start your design of a database by creating all of its tables and then creating any other object. Before you create tables, carefully consider your requirements and determine all the tables that you need.

Let us try and create the first table that will store the basic contact information concerning the employees as shown in the following table −

Field Name Data Type
EmployeelD AutoNumber
FirstName Short Text
LastName Short Text
Address1 Short Text
Address2 Short Text
City Short Text
State Short Text
Zip Short Text
Phone Short Text
Phone Type Short Text

Let us now have short text as the data type for all these fields and open a blank database in Access.

Home

This is where we left things off. We created the database and then Access automatically opened up this table-one-datasheet view for a table.

Fields

Let us now go to the Field tab and you will see that it is also automatically created. The ID which is an AutoNumber field acts as our unique identifier and is the primary key for this table.

The ID field has already been created and we now want to rename it to suit our conditions. This is an Employee table and this will be the unique identifier for our employees.

Unique Identifier

Click on the Name & Caption option in the Ribbon and you will see the following dialog box.

Field Properties

Change the name of this field to EmployeeID to make it more specific to this table. Enter the other optional information if you want and click Ok.

Table1

We now have our employee ID field with the caption Employee ID. This is automatically set to auto number so we don't really need to change the data type.

Let us now add some more fields by clicking on click to add.

Click To Add

Choose Short Text as the field. When you choose short text, Access will then highlight that field name automatically and all you have to do is type the field name.

Short Text

Type FirstName as the field name. Similarly, add all the required fields as shown in the following screenshot.

First Name

Once all the fields are added, click the Save icon.

You will now see the Save As dialog box, where you can enter a table name for the table.

Save As

Enter the name of your table in the Table Name field. Here the tbl prefix stands for table. Let us click Ok and you will see your table in the navigation pane.

Table

Table Design View

As we have already created one table using Datasheet View. We will now create another table using the Table Design View. We will be creating the following fields in this table. These tables will store some of the information for various book projects.

Field Name Data Type
Project ID AutoNumber
ProjectName Short Text
ManagingEditor Short Text
Author Short Text
PStatus Short Text
Contracts Attachment
ProjectStart Date/Time
ProjectEnd Date/Time
Budget Currency
ProjectNotes Long Text

Let us now go to the Create tab.

Create Tab

In the tables group, click on Table and you can see this looks completely different from the Datasheet View. In this view, you can see the field name and data type side by side.

Design Tab

We now need to make ProjectID a primary key for this table, so let us select ProjectID and click on Primary Key option in the ribbon.

Primary Key

Field Name

You can now see a little key icon that will show up next to that field. This shows that the field is part of the table’s primary key.

Let us save this table and give this table a name.

Saveas Project

Click Ok and you can now see what this table looks like in the Datasheet View.

Datasheet View

Let us click the datasheet view button on the top left corner of the ribbon.

Left Corner

If you ever want to make changes to this table or any specific field, you don't always have to go back to the Design View to change it. You can also change it from the Datasheet View. Let us update the PStatus field as shown in the following screenshot.

Pstatus

Click Ok and you will see the changes.

Project Status
Advertisements