Excel in Access (Part 3)

Excel in Access (Part 1)
Excel in Access (Part 2)

Free (MS Access) Normalization Tool —         Download it Here         How to use the Normalization Tool

Using the normalization form we converted part of a spreadsheet type table into the beginnings of a relational table. From this table we derived a “look up table” now giving us a total of three tables, the remains of the first table, “tblStudent” (the student names), the next table “tblStudentSubject” stores the subject(s) related to each student, and finally a third table, “tblSubject” a “look-up table” to store the actual subject description.

Click on Image for Fullscreen View

From the table “tblStudentSubject” we created a form in datasheet view:

Now all we need do is combine this datasheet view form with a form based on the students list, and this will give us a form for correctly displaying the student names and the subject(S) the student is taking in one Form:

Using the wizard create a basic form from the student table and name it “frmStudent” arrange its size so it has some open space as shown.

Now open “frmStudent” in design view

Click on Image for Fullscreen View

And drag the subform “sfrmlStudentSubject” into the clear area on the student form “frmStudent”

Click on Image for Fullscreen View

You may wish to delete the text box, you don’t have to but I usually find it looks better without it.

Click on Image for Fullscreen View

Size the form to suit

Click on Image for Fullscreen View

Now save the form and have a look, you will notice that it incorrectly shows all of the records in the subform,

Now the next bit is tricky, for two reasons, the form isn’t really on top of the other form, it actually sits in a subform window and you need to gain access to the properties of this subform window by clicking on the tiny line that you can just see around your subform. This can be a difficult task to master first time.

Click on Image for Fullscreen View

Now open the “subform field linker” dialog box by clicking on the ellipsis (…)

Click on Image for Fullscreen View

Then select the fields which contain the data that link the two forms together, the master field will be the “Main Form” and the child field relates to the subform.

Click on Image for Fullscreen View

Click on Image for Fullscreen View

Notice how the master form ID and the subform ID(s) now match. You may wish to delete the ID field from the subform, it is not necessary for the maintenance of the link between the two forms.

You can download HERE, a database with the normalization form, — — and the tables and forms shown in this thread.

Excel in Access (Part 1)
Excel in Access (Part 2)

Free (MS Access) Normalization Tool —         Download it Here         How to use the Normalization Tool

New! a follow on article

MS Access Your Data With Sub Forms

Mobile: +44 7747 018875
Email: mailtonyhine@gmail.com