In Database, tables are used to store the data so tables are the building block of a database. to store, fetch, update or delete the data in the tables are the easiest way. There are various operations that can be performed on tables.

·        Inserting data in the table

·        Editing records in the table

·        Deleting records from the table

·        Sorting data in the table

 

INSERTING DATA IN TABLE

To insert data in a table, follow the given steps:

·        Open the database in which the table exists. For example, database name is dbschool.

·        Select any table in the Navigation pane. Double-click on it to open it.

Or

Right click on the table in the navigation pane. A context menu will appear. Click on open table. The pointer will appear in the first cell of the table.

·        Start typing in the cell and press Tab key to go to the next field.

·        Thus we can insert the data in a table.

 

 EDITING RECORDS IN TABLE

To modify or edit the records in the table, follow the given steps:

·        Open the table and click on the record you want to edit.

·        Use the Backspace or Delete key to remove the record.

·        Make changes in the records and click on the Save button or press CTRL+S to save the changes.

 

DELETING RECORDS FROM TABLE

To delete or remove the records from a table, follow the given steps:

·        Select the record that we want to delete.

·        After selecting the record, right-click on it and select the Delete Record option from the Context menu.

OR

Click the Delete button in the Records group in Home tab.

 

SORTING DATA IN TABLE

Sorting is the process of arranging data in an ascending or descending order. It is more convenient to work with the sorted data rather than the tangled data. Sorting can be applied on either a single field or multiple fields.

SORTING DATA IN DATASHEET VIEW

To sort the data using a single field, follow the given steps:

·        Open a table and select the field on the basis of which we want to sort the data.

·        Click on Home tab and select the Ascending or Descending option in the Sort & Filter group.

·        The data will be sorted

 

SORTING DATA USING MULTIPLE FIELDS

 

Data can be sorted in a table using multiple fields. Follow the given steps to sort the data in a table using multiple fields

·        Select the Home tab and click on the Advanced button in the Sort & Filter group.

·        Dropdown list will be opened, select the Advanced Filter/Sort option… .

·        Drag the field student name from the student table into the Field grid or click on the grid in front Field and select student_name from the dropdown list and click on the Sort option.

·        Select the Ascending or Descending option from the drop-down list.

·        Repeat the process to add multiple fields for sorting the data. For example, email_id by taking sort option descending.

·        Click on the Save button on the Quick Access Toolbar to save.

·        It will ask to save the query. Enter the name and press ok

·        To view the sorted data or run the saved query, either double-click on the saved query in the All Access Objects panel or right-click on the query and select the Open option.

·        The sorted data will be received according to the student_name ascending and email_id descending.

 

 REFERENTIAL INTEGRITY

Referential Integrity is used to fetch data from two or more tables. It is possible only if all the tables have at least one common field. Referential Integrity (RI) states that a foreign key must have a matching primary key, i.e., all its references must be valid and the data cannot be accidentally deleted.

Features of Referential integrity:

It ensures the accuracy and consistency of data within the tables. It prevents the users from entering inconsistent data.

Referential Integrity does not allow to enter a new record in the child table if the value of the foreign key value does not exist parent table.

If any change is made in the primary key field value, the same must be applied in the foreign key value too.

For example Student is the parent table with the primary key ‘id‘ and marks is a child table with the foreign key ‘stuid’. Now the data can’t be stored in the marks table (child table) if we enter such value in stuid field which does not exist in the id field in the student table (parent table).

 

RELATIONSHIP

In MS Access, Data can be stored in multiple tables. Relationships are the links that associate a field in one table with a field in another table. To fetch common data from multiple tables, we need to define relationships between the tables. After defining relationships, data from both the tables can be used to display collective information. This information can be used in Forms or Reports.

A relationship among multiple tables can be defined using matching field with the same name in both the tables. In most of the cases, the matching fields are the Primary key from one table and a foreign key in the other.

 

TYPES OF RELATIONSHIPS

There are different types of relationships:

1.     One-to-One

2.     One-to-Many

3.     Many-to-Many

 

1.     One-to-One

In this type of relationship, a record in a table can have only one matching record in another table, and vice versa. For example, in UIDAI database, each person has only one Aadhaar number, and each Aadhaar number is assigned to only one person.

 

2.     One-to-Many

In this type of relationship, a record of one table is associated with several matching records of another table, but a record from the second table is associated with only one record of the first table. For example, one class consists of multiple students, but a student can belong to only one class.

 

3.     Many-to-Many

In this type of relationship, multiple records of one table are associated with several records of another table, and vice versa. For example, a student can be taught by many teachers, and a teacher can teach many students.

 

HOW TO CREATE A RELATIONSHIP BETWEEN THE TABLES

To create a one-to-one relationship, create two tables, Firrst is student table having fields ID, student_name, Institution_name, web_link and email_id and Second table is the marks table with fields mark_id, student_id and marks.

 

Open a database and click on the Database Tools tab.

Click on the Relationships button in the Relationships group. Show Table dialog box will be displayed

To select both the tables, press the Ctrl key and click on the table names in the list.

To create a relationship, drag the primary key field from the parent table and drop it on the child table's primary key field.

The Edit Relationships dialog box will be appeared.

To maintain the integrity of data in both the tables select the Enforce Referential Integrity Button.

The relationship between both the tables will be created.

To edit a relationship, double-click on the relationship line. The Edit Relationships dialog box appears. Make the required changes, and then click on OK.

The relationships applied on the tables can be removed also by doing right-click on the relationship line and select the Delete option.

 

FIELD PROPERTIES

Field property defines the characteristics of a field.  The properties for each field are set in the Design View of the table using the Field Properties pane.

 

Following are the different properties that can be used while creating the fields.

 

Field Size: It tells us that how much maximum number of characters in the field can be stored.

Format: It displays the format of the field

Caption: It defines the caption when the field is used in a form.

Validation Rule: It specifies the criteria that limits the values that can be entered in the field

Validation Text: It specifies the message when a value entered by the user does not satisfy the criteria specified in the Validation Rule.

Default Value: The given value in default value is automatically entered if no other value is entered by the user.

Required: This field property enforces data entry for the field.

:05/02/2021