MS Access Create Tables - MS Access

How to create tables in MS Access?

After creating a database, you can store the data in tables. As other database objects are most dependent on tables, you should design the database by creating all its tables and then create any other object. Before creating tables, you should carefully consider your requirements and determine all the tables that are required.

Let’s try and create the first table which stores the basic contact information of the employees as shown in below 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

Now use short text as the data type for all these fields and open a blank database in Access.

How to create tables in MS Access

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

one-datasheet view for a table.

Click on Field tab and you will see that it is also automatically created. ID which is an AutoNumber field will act as a unique identifier and is the primary key for this table.

ID field is already created and you can rename it to suit the conditions. This is an Employee table and this will be the unique identifier for the employees.

Employee table

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

dialog box

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

EmployeeID to make it more specific to this table

We now have employee ID field with the caption Employee ID. This will be automatically set to auto number. Therefore, changing the data type is not needed.

Now add some more fields by clicking on click to add.

add some more fields by clicking on click to add.

Select Short Text as the field. When you choose short text, Access will highlight that field name automatically. Now type the field name.

Short Text as the field.

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

add all the required fields

After adding all the fields, click on Save icon.

Save As dialog box will be displayed, where you can enter a table name for the table.

dialog box will be displayed

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

name of the table in the Table Name field

Table Design View

As we have already created one table using Datasheet View. Now let’s create another table using Table Design View with below mentioned 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

Now go to the Create tab.

create_tab

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

see the field name and data type side by side

Now make ProjectID as a primary key for this table and select ProjectID and click on Primary Key option in the ribbon.

primary key for this table and select ProjectID

primary key for this table and select ProjectID

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

Save this table by giving a name.

saveas_project

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

Datasheet View

Click the datasheet view button on the top left corner of the ribbon.

datasheet view button

If you wish to make changes to this table or any specific field, you don't have to go back to the Design View for changing it. It can also be changed from the Datasheet View. Update the PStatus field as shown in below screenshot.

PStatus field as shown

Click Ok and you will see the changes.

project_status


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

MS Access Topics