Excel in Access (Part 2)
A Common Misconception for Excel Users Transitioning to MS Access 🚫
One of the biggest mistakes people make when they move from Excel to MS Access is that they assume MS Access is just 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. 😖
Introducing: The Nifty Access Normalization Tool 🛠️
Using the ‘Nifty Access Normalization Tool’, we converted a spreadsheet-type table into the beginnings of a relational set of tables. From Table 1a, we derived a look-up table, Table 1b. This process results in a total of three tables:
- tblStudent: Contains the student names, and is what remains of the first table. 👥
- tblStudentSubject: Stores the subjects related to each student. 📚
- tblSubject: Serves as a look-up table to store the actual subject descriptions. 📖
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……
Table 1b
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”.
The "Unselected" Check Boxes can be removed
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”
Form based on Students List
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”.
create a look up table
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. .
This "unique list" called "tblSubject" (a lookup table)
Replace the entries in the student subject table "tblStudentSubject" with the ID
Replace the entries in the student subject table "tblStudentSubject" with the ID
These changes to the table make it efficient
These changes to the table make it efficient
you never know how big your dB is going to get. Get it right, right from the beginning.
you never know how big your dB is going to get. Get it right, right from the beginning.
Open the Subform Property Sheet
Open the Subform Property Sheet
set its default view to "Datasheet View"
set its default view to "Datasheet View"
Open the query builder
Open the query builder
In the query builder select the table "tblSubject"
In the query builder select the table "tblSubject"
Drag both fields into the query builder grid
Drag both fields into the query builder grid
Click on yes to save these options
Click on yes to save these options
Set the combo box limit to list property to "Yes"
Set the combo box limit to list property to "Yes"
Still in the combo box properties, set the column count to "2"
Still in the combo box properties, set the column count to "2"
Still in the combo box properties, set the column widths as shown.
Still in the combo box properties, set the column widths as shown.
Still in the combo box properties, Change the name of the combo box as shown or to your own particular naming convention.
Still in the combo box properties, Change the name of the combo box as shown or to your own particular naming convention.
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.
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.
In the original table; you can see it contains the now redundant check box fields
Delete the redundant rows
Delete the redundant rows
Table with redundant rows deleted
Table with redundant rows deleted
