How to create a database from scratch in MS Access – tables, relationships and forms. Watch the video on YouTube (104 MB):

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 Details	Course Details	Payment Details	Questionnaire
Salutation	Course Name	Payment Amount	Word of mouth
First Name	Course Fees	Payment Date	Flyer
Middle Name		Payment Type	Ad 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:

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