UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Sanbox for TonyHine    


Excel in Access (Part 1)

Problem for Excel Developers

One of the problems facing Excel developers moving into MS Access is actually the apparent similarity between MS Access tables and Excel spreadsheets.

MS Access is NOT Excel

This similarity of the “look” in both programs, the layout of the data, leads to the Excel developer mistakenly thinking that a database works in a similar way to a spreadsheet.

Flat File Database

Spreadsheets are very sophisticated tools for manipulating figures, and they can also handle data very well particularly in the form of a “flat file database”. You can also use the flat file approach in MS Access, however MS Access is primarily a relational database, meaning that it has the ability to relate tables of information together.


It is by taking advantage of this relational property of the database that you can really make significant improvements to data handling. So if you have a spreadsheet that requires upgrading to a database to take advantage of this, then this article is for you.

Typical Spreadsheet Layout

Below is an example which shows columns that you would typically find in a spreadsheet, first name, last name for example. Then a variety of subjects, with a check box against each indicating that the subject has been taken, passed or possibly even failed!


Flat File is OK

A direct transfer of this spreadsheet layout into an MS Access table would be usable and indeed many Access databases are constructed in this way, and some sophisticated applications costing many hundreds of pounds are based on a flat file system. However as mentioned earlier, MS Access is a “Relational” database, meaning that it has the ability to relate your data together.

You’re Not Relational????

So how and why should you apply a Relational structure? Well it solves a lot of problems, there are many but I have noted the main ones below.

Uncharted Territory

The first most common problem I have noticed on forums for people with similarly designed tables imported from a spreadsheet is that they find it difficult to extract useful information, and this is one of the major reasons that you should consider constructing your new database in a different manner to your spreadsheet. No doubt you will be able to come up with your own unique methods of extracting the data, however you may find there is little help available, not because no one wants to help you, but because you are embarking into uncharted territory, “where no man has gone before”.


Another reason, (not in any particular order of importance, the significance will change from project to project) what happens if you need to add an extra subject? Let’s say the school starts offering French lessons, in the spreadsheet all you would do is add a new column “French” and indeed in your MS Access database you could also add a new column “French”, however if the construction of your database is well advanced, in other words you have many queries and forms based on the table you are adding a new field to, then you will have to modify every single query and form that extracts information from this table, not something you want to do often!

Column Limit

Many modern spreadsheet programs can handle thousands of columns; however MS Access has a 255 limit to the number of fields in any particular table. So in the student table example, if you were at the 255 limit and you needed to add another subject then you would find yourself in a difficult position

Relational Solution

So what’s the solution? This is the part that is sort of counter intuitive, you actually construct two tables from the original table and link them together. This is the “Relational” aspect of the database coming into play.


If you look at the original layout of the data above you can ask questions about it, is there any data in the original table that is related? Looking at it, I would suspect all of the boolean columns (the check box columns, yes/no data) they are all the same, so they are a likely candidate for a separate table. And indeed there is an obvious name for this new table, they are all “subjects” that the student is or could take.

The New Table

So now you have a name for the new table, “Subjects” and to link it to the data remaining in the original table, (first name,- last name) it will need to have a field which contains a match to the RecordUniqueID field. For this example let’s call this “MatchingID” then you need a field to record the subject and another field to record whether it is true or false. For the purposes of this demonstration I have terms these “TransposedSubject” and “TransposedData” and you can see what this should look like below:


Free Normalization Tool (see attachment)

How do you get that new table you may ask? Well originally it was quite a tedious task, especially if you had many columns of data to move, you had to construct an append query and append each column. However I realized it may be a process that would lend itself to automation of some sort, and I came up with a form for handling this.

Excel in Access (Part 2)


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


To this:


This was achieved with a form based tool available by DOWNLOAD. 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” There is a video showing how to do this here: (At time index 1min)

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.

Edit Discussion
Custom Search

Thank you for your support!
This page has been accessed 7,696 times.  This page was last modified 23:18, 18 March 2010 by Alan Greenwood. Contributions by Tony_Hine  Disclaimers