Why Your Data Analysis Toolkit Needs Microsoft Access

04-Jan-2020
Oh boy. This is a big topic.

Microsoft Access is the one tool that professionals, whether they are researchers or engineers, accountants or billing clerks, look at with hope and a kind of longing, day after day in their start menus, as they are overwhelmed by pile after pile of data. They wonder, "how much better could I be at this stuff, if I was a pro with this tool?"

This is the right question.

How do I know this? I worked for decades supporting big and small systems created using Access and SQL Server. When I would work with professional staff at my client sites, I could always see the look on their face - Access was the tool that was just out of reach; the tool that had the capability to get them where they needed to go, if only they knew how to apply it. Many of these people had really (really) pushed Excel to the limit but were still frustrated.

The answer may not be what they are expecting, but they certainly are on the right track. Microsoft Access is kind of like an old magician's wand from a Harry Potter story. It can do all kinds of weird and wonderful things for you, but it can also get you into trouble if you don't pay attention. This old wand has many uses.

And certainly, an old wand it is. Access traces its roots back to the early 1990's, making it older than many of its users nowadays. It is a product that brought the power of relational databases and the language of SQL into the hands of everyday workers, and did it in a way that empowered them like never before.

In fact, this product is unique and has no serious competition.

If you are a data analyst and do not have solid skills in Access, you're limited in the realm of data transformation. This is because you don't have the power of relational database systems at your fingertips. When I mentor an analyst who cannot use it well, this is one of the skills I get them to work on.

Want to join together data *without importing* from a text file, a csv, two spreadsheets, an access table, a SQL Server table and an Oracle table? You can do this with Access using linked tables.

Want to select some data and sort your output by 10 fields in order? No problem.

Need to quickly "walk" a table so that you can make very specific changes on each row? Easy, use the built-in VBA programming language.

Need to quickly query some server table with millions of rows, but limit your selections to only 800 parts that you have in a text file? Easy.

Want to easily share your datasets with other people? Access is used almost everywhere in business around the globe. Access files are portable, easy to open Microsoft Office files.

Need a file database to do some coding in Python or R against, or to prepare data for Power BI or other tools? Access tables can be consumed by almost every statistical tool, suite, programming language, or framework.

You'll note that I didn't mention creating applications in Access. This is because I don't view this as one of the key uses of Access for people learning data analytics and data transformation. It is literally a whole other aspect of the application.

What you'll find with other tools is that they are either very specialized and targeted at programmers, or they do only some of the functions that Access does. It really is an incredible product that people rely on in almost every business environment.

So, from a professional's perspective, not having these data skills, and at least the foundational knowledge of relational database systems, means that you'll always be the last one "in the know"; you'll always be the one waiting for others to pull data from systems for you. You'll always be the one waiting for them to transform data before you can use it.

In my opinion, they hit the nail on the head with the name on this product, because it literally gives you access to the world of data. Have fun, learn it well, study database concepts and apply them, and you will increase your analytical ability immensely.