Excel in Access (Part 2)

Excel in Access (Part 1)
Excel in Access (Part 3)

Video Version HERE:

How to use the Normalization Form

In Excel in Access (Part 1), we went from this:

\1

To this:

\1

This was achieved with a form based tool available to DOWNLOAD here.

See instructions on its use here:

However you may be looking at the new resultant table and wondering what on earth to do with it. I hope to take you through the process of making it into something useful in this thread.

The first thing you will notice is that where the check box is not checked, then that whole row is redundant, for instance there’s no need to record that ID number “1” --- “Has Not” taken Maths, English, Geography, Physics etc, it would suffice just to record the subjects that have been taken , In this case Biology, PT and Social. Looking at those entries in particular, then a general rule of logic can be defined, “delete all the rows where the check boxes are false”.

\1

Once you have deleted all of those rows,

\1

then it becomes obvious that the check boxes themselves which now “All” contain a true value are also redundant, they can be deleted just leaving you the text entry identifying the subject taken by each student.

\1

Using the “relational” properties of the database that is one more thing you can do which will improve efficiency and that is to replace each text entry --- Maths, English, Geography, Physics etc, with a number linking that field to a look up table.

First of all you need to create a look up table; this can be done by applying a create table query to extract just the unique values for the “subject” There is a video showing how to do this here: (At time index 1min)

\1

This unique list should be called “tblSubject” this table is not quite finished, you need to add an identity column to the left of the text representing the individual subject, this identity will then appear in the previous table.

\1

Once you have completed the “look up table” you then need to replace the entries in the student subject table “tblStudentSubject” where it shows subject in text form with the number representing the link to the look up table. This is the query:

\1

And here is the new column created:

\1

This way your design changes to the table are making it much more efficient, holding the same information but with less data.

\1

Now close the form and reopen it; it should display in “datasheet view” and the numbers should be replaced by the text entries provided by the look up table.

\1

In the original table; you can see it contains the now redundant check box fields

\1

Open the table in design view and remove these redundant check box fields

\1

The original form should now looks something like this:

\1

Excel in Access (Part 3)