Excel in Access (Part 2)

Excel in Access (Part 1)
Excel in Access (Part 3)
Excel in Access (Parts 1, 2 and 3 as Video Instructions)

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

To this:

This was achieved with a form based tool available 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".

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:

Phone: +44 1635 522233
Mobile: +44 7747 018875
Email: email@tonyhine.co.uk

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

Cheers Tony...