Excel in Access (Part 2)

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

New! a follow on article
MS Access Your Data With Sub Forms

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


To this:

Normalization Tool —         Download it Here         How to use the Normalization Tool

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”.

Once you have deleted all of those rows,

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.

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”

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.

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:

And here is the new column created:

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

Next use the form wizard to create a simple form based on the student subject table “tblStudentSubject” the form should show two text boxes one for each of the columns in the table.

Open the form in design view and change the subject text box to a combo box:

This form is going to be displayed as a sub form on your main form in datasheet view so you need to go into the form properties and set its default view to “datasheet view”

While in design view select the combo box and access the combo box properties press the Ellipsis (…) in the “row source” property box to access the query builder.

In the query builder select the table “tblSubject”

Then drag both fields into the query builder grid

Click on yes to save these options


Set the combo box limit to list property to “Yes”

Still in the combo box properties, set the column count to “2”

and the column widths as shown.

Change the name of the combo box as shown or to your own particular naming convention.

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.

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



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

Please note:

These “rows” actually represent the fields in the “Tables property window”, they control the display of the fields in the table.  On a re-reading this article just now I realised there was a source of confusion.  I am not suggesting you delete any rows in the table I am actually explaining how to delete “fields” from the table using the term “rows” could be very confusing!



The original Table should now looks something like this:

Mobile: +44 7747 018875
Email: [email protected]

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

Normalization Tool —         Download it Here         How to use the Normalization Tool

New! a follow on article
MS Access Your Data With Sub Forms

Cheers Tony…

This website uses third-party software - WordPress Add-Ins to be exact. I don't know what any individual add-in does, but I'm sure that many of them collect information about you. So be aware, if you continue using this site, then you are likely to be sharing your information. I don't know how to disable this sharing for any, or all of the plugins for individual users. So I can't stop the sharing of information. If this worries you then please do not use this site... If you continue to use this site I will assume that you are happy with it.

Do you need a hand in the right direction?

You are in the right place.