When VLOOKUP Isn’t Enough: 3 Indications Microsoft Access Might Be Useful

A few years ago, Ben wrote a great post about how wonderful Access is for SEO and why you should use it. I want to build on his post to explain how you might go about using Access, if he convinced you, and provide a few easy indications of when Access might be a useful tool, even if you haven’t read his post. I’ll take a few posts to explain things in a level of detail that might actually be understandable to someone who is starting out with essentially no knowledge of databases or Access, and then build up to create some relatively helpful queries for common issues.

The goal is to open your eyes to some of Access’ power, as apparently I’ve become a bit of an Access evangelist. Pretty much everywhere I’ve worked, I’ve seen a use for the program. There are usually a few “flags” that I start hearing and just think “Access. Yup. Access. Oh, definitely Access!” So for this first post, I’d just like to explain and elaborate on those in a pretty general sense.

Firstly, Access is accessible to nearly everyone. You don’t have to be a programmer to start making Access useful. You don’t have to know SQL. You don’t have to have experience with relational databases. The real power of Access is precisely this fact—that it’s powerful and has a low barrier to entry.

It may take you a few projects, and it’s certainly helpful to walk in understanding a bit of the architecture of the program and having an idea of what a database is all about. But Access uses an easy interface to provide complicated and powerful manipulation of data. Excel certainly has its place. But Access does too. Here are some instances when you may want to consider bringing Access into the picture. They are not mutually exclusive, and you are very likely to see them in the same sorts of circumstances. The ideal times to use Access are when you are dealing with:

  1. Lots of Data
    1. Example: Pulling Meta Titles from E-Commerce Sites and Analyzing Them
  2. Managing Redundancy
    1. Example: Running monthly reports for PPC Campaigns
  3. Linking Sets of Data
    1. Example: Correlating SEO and PPC Data to Determine the Most Relevant and Best Converting Keywords

Lots of Data

If you have a data set with several dimensions (columns) and hundreds (or thousands) of records (what Access calls rows/entries), you can quickly manipulate the data however you want in Access.

This power can be used to

  • run a replace query to change something standard about your data
  • change the formating of a particular field
  • count the length of a field
  • create a new field based on a specified set of criteria (ie: “if these two fields match, print 1, otherwise 0”)
  • many more!

Access handles massive amounts of data like a champ, and allows for use of VBA (visual basic) expressions to create new fields, which allows for a quick and repeatable analysis. You can even create a query on top of your first query that will list only those records that have identified issues, or records that you need to review more closely.

For example, I was recently doing a technical audit and wanted to analyze the titles of the pages I was auditing. I linked (we’ll talk about that) a table with the data I wanted to analyze, then was able to set up 2 layers of queries so that I open one and it lists all titles that are over 74 characters long. To link a table, under the External Data tab, click the “Excel” button in the “Import & Link” portion:

Then select “Link to the data source by creating a linked table.”

Boom. Now, it’s not that you cannot do this in Excel. You can. But the advantage of Access is that you can do this quickly and redundantly. Which brings me to my second point.

Automating Manipulation Redundantly

Herein lies the magic of Access. You can set up a system (using macros) that allows you to click a button and get the output you want. This reduces energy spent on thinking, which is energy you can spend on solving more interesting problems.

Of course, it’s not always necessary to do something redundantly. But when it is, Access is much better at that than Excel. Once you get quick at creating a query in Access, it doesn’t take much longer to set up than it would in Excel the first time. And then the second and third and fourth and fifth...etc, you start to see incredible gains in your efficiency.

An example of this is a monthly report. Let’s say you have two sets of data (we’ll discuss that in a second), or even three or four sets of data, that you want to throw together and then have a report about what kinds of insights there are among those two, three, four, etc reports. A common instance of this is in analytics reporting, which might include:

  • Google Analytics data
    • formatted with brackets around the keywords
    • by the date of the last day of the recorded week
  • data provided by your client or server-side
    • formatted as a list of keywords
    • broken down by day

You can set up a query, or several layers of queries, to link these data sets (again, we’ll come back to the linking part). Access allows you to create a query to standardize the data, and then link those two data sets in a way that you can reproduce very quickly with refreshed data, so that you can run the same report every week, month, quarter, etc and save yourself lots of time.

Again, it’s not that it is impossible to do the linking and querying in Excel, but it takes some time to set up, and needs to be re-set up every single time you have new data. Access streamlines that process, making it quicker, and using much less of your precious energy. This also reduces the chance of errors in your analysis.

Here’s an example of a “cleaning” expression, that replaces some strange characters I was finding regularly in titles, and replaces them with what they should be. It includes some nesting, (different layers of an expression). Look for the [data_OnPage]![Title] field, which is the one I’m using for the expression, and notice that it’s only mentioned one time in the expression, but is “nested” in the replace commands:

Linking Sets of Data 

The other key time when Access is a much more useful tool than Excel is when you have multiple sets of data. Access makes it super easy to link those data sets. You can use VLOOKUP in Excel (though I’ve got to confess that it confuses me), but I find it much more intuitive and simple to link data sets in Access (literally a matter of seconds). You see the two data sets in the Design View mode:

drag your mouse from the field you want to link in the one data set to the field you want to link in the other:

and then you double-click the line that has formed between the two fields to edit the kind of join (relationship between the two fields):

There are three types of joins, which are explained every single time you do this in the relationship window, just in case you get confused or forget:

Some Limitations

There are a few instances where Access really isn’t the answer. Unfortunately, Access isn’t available for OSX. So if you’ve got a Mac, you’ll have to either install Windows or use someone else’s computer. I haven’t found any really great Mac alternatives (in terms of ease of use and accessibility).

If you are doing something only one time, and you know for sure you only have to analyze one set of data (and you know the first time you do it, it will be perfect), Excel will be better. If you’ve got a simple, straightforward sorting you need to do, Excel will totally do the trick. Once you’ve used Access, you can export to Excel and create beautiful graphs and charts to visualize your analysis. But for redundantly manipulating and analyzing bigger sets of data that need to be linked, go with Access.

Digging In

For anyone who’s ready to move ahead and try out Access, get the trial version and play around a bit. There are some good tutorials out there as well, and once you get started, the help menu is your friend. My favorite VBA library is Tech On the Net. It provides really simple and easy to understand explanations, and then examples of different available functions. It can be sorted either alphabetically or by category, which is helpful if you know you need a date function but aren’t quite sure what it’s called.

There is SO MUCH MORE that you can do with Access than I can even begin to cover here. It is a powerful tool, letting you create custom programs that do whatever you want with data—import and export unified reports from multiple data sets with the click of a button, automate email functions, create reproducible cross-tab queries (essentially pivot tables), etc.

To summarize, times when Access would be super useful are when you:

  • have lots of data
  • are linking sets of data along a given (or several) axis/es
    • especially if they’re from multiple locations
    • especially if their format varies slightly (ie: one includes brackets, another does not)
  • are performing the same manipulation of data on similar but date-updated data sets multiple times

When you see an opportunity, go ahead and get Access and try some of it out! In a future post, I’ll cover the basic anatomy of Access.

Shannon Skinner

Shannon Skinner

Shannon comes to Distilled from the non-profit world. While working at a community health center in New York City for several years managing an HIV testing program, she discovered a love of data, and found Distilled to be a place that she could...   read more

Get blog posts via email

4 Comments

  1. Re. your sentence: "I haven’t found any really great Mac alternatives (in terms of ease of use and accessibility)."

    Filemaker (now owned by apple) is the mac alternative, its also far easier to use, scales better, fully cross platform compatible, networks using http, has integrated ipad support, and crucially, works just as well on the PC as it does on a Mac.

    Its superior to access in pretty much every way. Apart from the fact that it doesnt come as part of office pro.

    reply >
  2. Sarah

    Thanks for this Shannon, I look forward to experimenting with this.

    reply >
  3. Great intro! Now that I have parallels set up I'll have to give Access more of a chance and mess around with it.

    reply >
  4. Thanks so much, Shannon. These are some great getting started tips and explanations. I for one haven't decided if I want to have a SQL vocabulary and this makes it so much easier.

    Looking forward to more automation tips from you!

    reply >

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>