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.
2. Record
Views of a Table
Design View
Datasheet View
Switching Between views
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
- Creating tables in Design View
- Creating tables by using Templates
- Creating tables by entering data in Datasheet View
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
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
To Save a Record
Editing Records in a Table
Deleting Records in a Table
Sorting Records in a Table
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.