- Local time
- Today, 12:12
- Joined
- Jul 9, 2003
- Messages
- 17,023
Excel in Access (Part 1)
Unfortunately photobucket have decided to shove all of their pictures behind a paywall rendering this blog practically useless! I have a copy of it on my website which I have kept up to date you can find it here:- http://www.niftyaccess.com/excel-in-...access-part-1/
Video Version HERE:
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.
Relational (See Note)
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???? (See Note)
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 will 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”.
Maintenance
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 (See Note)
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.
How?
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)
Note:-
The Normalisation Tool is NOW available on my website here:-
Normalization Tool
You can download it straight away for a couple of Dollars.. (Note, the plus (+) sign next to the price you pay, you can pay more if you want to!) Alternatively if you want a free copy, send me an email and I will explain how you can get a free copy....
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.
Unfortunately photobucket have decided to shove all of their pictures behind a paywall rendering this blog practically useless! I have a copy of it on my website which I have kept up to date you can find it here:- http://www.niftyaccess.com/excel-in-...access-part-1/
Video Version HERE:
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.
Relational (See Note)
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???? (See Note)
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 will 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”.
Maintenance
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 (See Note)
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.
How?
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)
Note:-
The Normalisation Tool is NOW available on my website here:-
Normalization Tool
You can download it straight away for a couple of Dollars.. (Note, the plus (+) sign next to the price you pay, you can pay more if you want to!) Alternatively if you want a free copy, send me an email and I will explain how you can get a free copy....
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.
Last edited:
