Excel In Access (Part 1)

Excel In Access (Part 1)

From Excel to MS Access: A Journey of Data Management

Many people find their way to MS Access from Excel. This transition is usually because Excel is becoming difficult to manage for various reasons. πŸ€” It could be that you are sharing an Excel spreadsheet with many users, which is often problematic. πŸ§‘β€πŸ€β€πŸ§‘ Your spreadsheet might have a lot of repeating data appearing in various places throughout the spreadsheet, often getting out-of-sync. Some data is updated and some is not – a situation that could cause issues, especially when dealing with sensitive data like delivery addresses! πŸ“¦

Microsoft Access: An Excellent Alternative

Microsoft Access offers an excellent alternative with its regimented structure. πŸ› οΈ By molding and reformatting your Excel data into a format suitable for Microsoft Access, you can solve all of these problems. However, if you don’t take this step, you’re just creating more problems. 😰

The Essence of This Blog πŸ“š

These problems are very common and have happened to practically everyone – that’s the essence of this blog. 🎯 That’s why it might seem like experienced Access developers are hard on you, advising you on your approach. It’s not that they scorn you, it’s because they don’t want you to repeat the mistakes they’ve made. As a seasoned user, I’m speaking from experience! πŸ‘©β€πŸ’»

So please, do read this blog on ‘Excel in Access’. There are 3 or 4 pages now, and a nice collection of videos to boot… πŸ“–πŸŽ₯

Transition from Excel to MS Access: An Insight for Excel Developers

Problem for Excel Developers πŸ€”
One of the challenges facing Excel developers transitioning to MS Access is the apparent similarity between MS Access tables and Excel spreadsheets.

MS Access is NOT Excel ❌
This similarity in the ‘look’ of both programs, and the data layout, often leads the Excel developer to mistakenly assume that a database operates in a manner similar to a spreadsheet.

Flat File Database πŸ“‚
Spreadsheets are sophisticated tools for manipulating numbers, and they also handle data well, especially in the form of a ‘flat file database’. While the flat file approach can be applied in MS Access, MS Access is primarily a relational database, meaning that it has the capability to connect tables of information together.

Relational πŸ”—
By leveraging the relational feature of the database, you can achieve significant improvements in data handling. So, if your spreadsheet needs upgrading to a database to benefit from these improvements, this article is for you. πŸ‘ˆ

Typical Spreadsheet Layout πŸ“„
Below is an example that shows columns you’d typically find in a spreadsheet, such as first name and last name. Then there’s a variety of subjects, with a check box next to each indicating whether the subject has been taken, passed, or possibly even failed! βœ”οΈβŒ

Image 1

Understanding MS Access: The Shift from Flat File to Relational Structure

Flat File is OK πŸ‘
A direct transfer of this spreadsheet layout into an MS Access table would be usable. 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 most common problem I have first noticed on forums for people with similarly designed tables imported from a spreadsheet is that they find it difficult to extract useful information. 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. This is not because no one wants to help you, but because you are embarking into uncharted territory, ‘where no one has gone before’. 🌌

Maintenance πŸ”§
Another reason, regardless of its order of importance (as the significance will change from project to project), is what happens if you need to add an extra subject? Let’s say the school starts offering French lessons. In the spreadsheet, you would 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, meaning 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. This is not something you want to do often! 😰

Column Limit πŸ”’
Many modern spreadsheet programs can handle thousands of columns; however, MS Access has a limit of 255 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 counterintuitive, 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) 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 termed these ‘TransposedSubject’ and ‘TransposedData’, and you can see what this should look like below: πŸ‘‡

Image 2

… …

Normalization ToolΒ 

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

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.

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

Excel in Access (Part 2)
Excel in Access (Part 3)

… …

More Useful Stuff HERE:-

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.