Working with tables in MS Access

Working with tables in MS Access

Views of a table

The tables in MS Access have only two views – Design and Datasheet View.

Design View

In design view of the table, you can enter the field names, their data types and description. The design view window is divided into two parts – Field grid panes and Field property pane.

Field grid pane – It is used for entering field names and their data types. You can also give a description to the fields in this pane. This description column is optional.

Filed property pane – This section is used to set properties of the fields in the table.

Datasheet View

Datasheet view is used to enter data in a table. When you double click the table name in the left hand side pane window, it opens the datasheet view of the table.

Switching between views

Microsoft Access allows you to switch from one view to another by following options:

Option 1 – Click on the View Option in the View Group on the Home Tab to change from one view to another after opening the table.

Option 2 – Click on the Design View or Datasheet View at the right corner of the status bar.

Creating a table

There are various ways of creating a table in MS Access. They are:

• Creating tables in Design View
• Creating tables by entering data in Datasheet View
• Creating tables by using pre-defined Templates.

To create a table in design view, follow these steps:

Step 1 – Click on the Create tab.

Step 2 – Select the Table Design option in Tables Group.

Step 3 – Enter the name of the field in the Field Name column.

Step 4 – Select the data type list.

Step 5 – Use the Field Property Pane to set the properties for the fields. (Repeat step 3 to 5 for all the fields in a table)

Step 6 – Place the cursor on the field to be set the primary key and click on the Primary Key option in the Tools Group on the Design tab under Table Tools.

Step 7 – Click on the Save button on the Quick Access toolbar or press Ctrl + S to save the table.

Step 8 – Type the name of the table in Table Name.

Step 9 – Click on the OK button.

Modifying the Table design

You can make changes to the design of a table after it has been created.

Inserting a New Field

To insert a new field or column in a table, open the table in design view and follow the below step:

Step 1 – Place the cursor on the field before you want to insert a new filed.

Step 2 – Click on the insert rows option in the Tools group on the Design tab under Table Tools.

Step 3 – A new row is inserted. Now enter the field name, data type and properties for the new filed.

Deleting a Field

To delete a field open the table in Design view and follow the below step:

Step 1 – Click on the field to be deleted.

Step 2 – Click on the delete rows option in Tools group on the Design tab under Table Tools.

Step 3 – Click on Yes button to delete the field.

Addition, deletion and sorting of record in a table

Adding Records in a table

You can add new record to a table in three ways.

Step 1 – Select the New button in the Records group on the Home tab.

Step 2 – Simply start typing in the row below the last added record.

Deleting records in a table

To delete a record, open the table in the Datasheet View and follow the below step:

Step 1 – Click on the record that needs to be deleted.

Step 2 – Click on the Delete option in the Records group under Home tab.

Searching records in a table

To search any record from the table, open the table in datasheet view and follow the below step:

Step 1 – Enter a part or starting character of that text you want to search in the status bar.

Step 2 – Your typed text would get highlighted in the table records, press enter to find the next matching record.

Sorting records in a table

To sort records, open the table in Datasheet View and follow these steps:

Step 1 – Select the field which you want to sort.

Step 2 – Click on Ascending or Descending option in the Sort & Filter group on the Home Tab.