Create Database from scratch in MS Access

How to create a database from scratch in MS Access – tables, relationships and forms. Watch the video below:

Create complete database from scratch in MS Access

A computer training institute asks its students to fill out the following form when they wish to join a course:

Salutation:
First Name:
Middle Name: Photograph:
Last Name:
Date of Birth:
House No:
Street:
Locality:
City:
Pin code:
Email:
Mobile:

Course joined:
Course Name:
Course Fees:

Payment mode:
Payment Amount
Payment Date:
Payment Type:
 Cash
 Cheque
 Credit Card
 Net Banking

How did you get to know about us?
 Word of mouth
 Flyer
 Ad in Market Informer
 Website

From the above items we can construct the following tables:

Student DetailsCourse DetailsPayment DetailsQuestionnaire
SalutationCourse NamePayment AmountWord of mouth
First NameCourse FeesPayment DateFlyer
Middle Name Payment TypeAd in Market Informer
Last Name  Website
Date of Birth   
House No   
Street   
Locality   
City   
Pin code   
Email   
Mobile   
Photograph   

Now we can start MS Access and after creating the database file and saving it in a specific folder of our choice, we can start designing the tables as shown in the video above.

Here is the students table in design view and data-sheet view:

Students Table in design view
Students Table in Datasheet view

Courses Table in Design View

Courses Table in Datasheet View

Payments Table in Design View
Payments-Table in Datasheet View
Questions Table in Design View
Questions Table in Datasheet View
Relationship between Tables
Form for Data Entry created from all the four tables

After creating the individual tables, we place a foreign key (studentID) in the other tables to create the one-to-many relationships. Once the relationship is created we can access data from all the tables and create a form. This form allows us to make data entries into all the four tables quickly and easily.

What is referential integrity? You noticed how we collected the items for each of the tables – students, courses, payments and questions. We then divided our database information into many subject-based tables to minimize data redundancy. Next we gave Access a way to bring the data back together by placing common fields called foreign keys (StudentID) into related tables. For example, to represent a one-to-many relationship we took the primary key from the “one” table (Students) and added it as an additional field to the “many” table (courses, payments, questions). Access takes the value in the “many” tables (courses, payments, questions) and looks up the corresponding value in the “one” table (Students). In this manner the values in the “many” tables reference the corresponding values in the “one” table. Let’s say we have a one-to-many relationship between Students and Payments tables and we wish to delete a Student. If the student we wish to delete has made payments in the payments\s table, then those payments will become “orphans” when we delete the Student’s record. The payments will still contain a StudentID, but it will no longer be valid, because the record that it references no longer exists.

The aim of ‘enforce referential integrity’ option is to prevent orphans and keep references in sync so that this situation doesn’t occur.

We enforce referential integrity by enabling it for a table relationship. Once enforced, Access rejects any operation that violates referential integrity for that table relationship. This means Access will reject both updates that change the target of a reference, and deletions that remove the target of a reference. It’s possible we might have a perfectly valid need to change the primary key for a student that has payments in the payments table. For such cases, what we really need is for Access to automatically update all the effected rows as part of a single operation. That way, Access ensures that the update is completed in full so that y\our database is not left in an inconsistent state, with some rows updated and some not. For this reason Access supports the ‘Cascade Update Related Fields’ option. When we enforce referential integrity and choose the ‘Cascade Update Related Fields’ option, and we then update a primary key, Access automatically updates all fields that reference the primary key.

It’s also possible that we might have a valid reason to delete a row and all related records — for example, a Student record and all related payments for that student. For this reason, Access supports the ‘Cascade Delete Related Records’ option. When we enforce referential integrity and choose the Cascade Delete Related Records option, and we then delete a record on the primary key side of the relationship, Access automatically deletes all records that reference the primary key.

Reference: Guide to Table Relationships

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.