Excel in Access (Part 2)
One of the biggest mistakes people make when they move from Excel to MS Access is that they assume that MS Access is a more powerful version of Excel, and their Excel tables will work as is in MS Access. The problem is they do work!!! This MISTAKE leads to a variety of problems, which, if not nipped in the bud cause a massive headache for the would-be MS Access Developer VERY Quickly… Using the “Nifty Access Normalization Tool” we converted a spreadsheet type table into the beginnings of a relational set of tables. From this table (Table 1a) we derived a “look up table” (Table 1b) 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.
In Excel in Access (Part 1) we went from "Table 1a"
Table 1a
In Excel in Access (Part 1) we went to " Table 1b"
The Nifty Access Normalisation Tool turns the preceding table “Table 1a” into several tables one of them this one:- “Table 1b” As you can see “Table 1b” contains all of the information from the the unnormalized table “Table 1a” but in a normalised format. However, it does need some extra helper tables……
The "Unselected" Check Boxes can be removed
However you may be looking at the new resultant table “Table 1b” 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”.
Form based on Students List
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. Now the table only shows checked items so the Check Box itself is superfluous and we can delete of that Column… To be clear, you delete the checkbox column, not the “checked rows”
create a look up table
Using the “relational” properties of the database there 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”.
This "unique list" called "tblSubject" (a lookup table)
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. .