WORKING WITH TABLES IN MS ACCESS 2016

 In the previous class, we have discussed the basic concept of a database and the various objects of MS Access 2016 database. A table is the basic component of a database in which you enter data. In this chapter, you will learn to work with tables.

 Tables in Access

 A table is a database object, which is used to store information in an organised manner. It consists of rows and columns. A database can have one or more tables.

Components of a Table 

The important components of a table are: 

1. Field or Attribute 

The columns in a table are known as fields or attributes. A field is a part of record, which consists of single piece of data. A field stores one type of information about all the objects or items. Every field has a data type that determines the type of values that can be stored under it. 

Data types are used to declare the fields of a table. A data type determines the type of data in it. Once the data type is defined, we cannot enter a different type of data in it. If we tr to do so, an error message will be displayed. 

Field Data Types 

The various data types available in Access are described in the table given below.

After setting the data type for a field, you can set its properties. 
Let us learn more about field properties. 
Field Properties 

Every field in a table has its properties to define its characteristics and behaviour. The field properties let you have more control on data that can be entered in a particular field. Some of the field properties are as follows :

2. Record 

The rows in a table are known as records. A record stores complete information about an object or an item. Each record contains the complete information about a specific entity. 
3. Primary Key 
A primary key is a field or a combination of fields that uniquely identifies the records in a table. A primary key field cannot have repetitive values and cannot be left blank. 

Views of a Table 


You can work on a table in two views – Design View 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 Pane : It is used for entering field names and their data types. You can also give an optional description about each field in this pane.
Field Properties Pane : This section is used to set properties for the fields in the table.

Datasheet View

Datasheet View is used to enter data in a table. When you double-clik the table name in the database windows, it opens in the Datasheet View.

Switching Between views

Microsoft Access allows you to switch from one view to another by following either of the two methods :

Method 1 : Click on the View option in the Views group on the Home tab to change the desired view.

Method 2 : Click on the Design View or Datasheet View buttons 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 using Templates
  • Creating tables by entering data in Datasheet View
In this chapter, you will learn how to create a table in the Design View. This lets you design the structure of a table by specifying the field names, data types and their properties.

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 for the field in the Field Name column.

Step-4 : Select a data type for a field from the Data Type list.

Step-5 : Use the Field Properties pane to set the properties for the fields.

Step-6 : Click on the Save button on the Quick Access toolbar or click on the File tab and select the                      Save option. The Save As dialog box appears.

Step-7 : Place the cursor on the field to be set as the primary key.

Step-8 : Click on the Primary Key option in the Tools group on the Design tab under Table Tools. You                  will notice that a key symbol appears on the field.

Step-9 : Type the name of the table in the Table Name: text box.

Step-10 : Click on the OK button.

The table is now added to the Navigation Pane.

Entering Data in a Table

After finalising the structure of a table in Design View, you can add records to it in the Datasheet View. 

To enter data in a table, follow these steps :
Step-1: Open the table in the Datasheet View by double-clicking on the table in the Navigation Pane.

Step-2: Apart from fields, you will also find a blank row with an asterisk (*) symbol. Type the data in the appropriate fields and press either the Enter or the Tab key to move to 
the next field. When the data is entered in a row, a new blank row will appear automatically. You can insert any number of records in a table.

You can view the record number at the bottom of the screen in the Record Navigation Bar.

Adding Records in a Table

To Add a New Record

You can add a new record to a table in three ways.
Method 1: Select the New button in the Records group on the Home tab.

Method 2: Click on the New (blank) record button on the Record Navigation Bar.

Method 3: Simply start typing in the row below the last added record.

To Save a Record 

Click on the Save button in the Records group on the Home tab.

Editing Records in a Table 

After entering data in a table, sometimes it is required to make changes in the database. 
To edit records in a table, follow these steps: 
Step-1: Use the Record Navigation buttons to find the record to edit or double-click the cell where you want to make changes. 
Step-2: Type the information into the field. A pencil icon appears to indicate the Edit mode, Click outside the record to apply the changes.

 Deleting Records in a Table 

To delete a record, open the table in the Datasheet View and follow these steps : 
Step-1: Click on the record that needs to be deleted. 
Step-2: Click on the Delete option in the Records group on the Home tab. 
The selected record will be deleted. 

Sorting Records in a Table 

To sort records, open the table in Datasheet View and follow these steps: 
Step-1: Select the field on the basis of which you want to sort the records. 
Step 2: Click on the  or  option in the Sort & Filter group on the Home tab.

Searching Records in a Table

 To search for data quickly, follow these steps :

 Step-1: Enter a part or all of a word, phrase, date or number in a Search box at the bottom of the Access screen.

Step-2: Access highlights the corresponding characters in the first record that match your search. To find the next matching record, press the Enter key again. 

Access finds all records that match your search data, anywhere in your datasheet

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, open the table in Design View and follow these steps: 

Step-1: Place the cursor on the field before which you want to insert a new field. 

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. Enter the field name, data type and properties for the new field.

Deleting a Field

To delete a field, open the table in Design View and follow these steps :

Step-1: Place the cursor on the field before which you want to insert a new field. 

Step-2: Click on the Delete Rows option in Tools group on the Design tab under Table Tools

Step-3: Access displays a warning box confirming whether you want to delete the field permanently. Click on the Yes button.

The field is deleted from the table. 


Leave a Comment