Secrets from the Data Cave, October 2014

Posted on October 20, 2014 | in Uncategorized | by CRC

by Sarah McCruden

Welcome to CRCs monthly series of articles on all things techie: Secrets from the Data Cave! (For those who dont know, the title references our room fondly referred to as the bat cave where data staff can geek out in an isolated setting.) Here well be offering you a fascinating sneak peek into the cave, with the latest updates & tips on what were implementing here at CRC!


Access vs. Excel: Which Will Reign Supreme (for your storage needs)

Access and its less showy cousin, Excel, are both good options for data storage. In this installment of Secrets from the Data Cave, I’ll highlight some things to consider when deciding between using Excel and Access for your data storage needs.

IBM.CardComputing.19xx.102645452.lg I should start by saying that Excel CAN do a lot of things that Access does. For example, Ive built some incredible automatic scoring programs in Excel. However, in some cases the use of Excel may prove (much) more time consuming because of all the formulas and manipulations one would have to use to get the same results that Access would very quickly get to.

Then again, if you do not know how to use Access already, it can be intimidating to learn a new program. If thats you, then I would encourage you to check out this free, 12-part tutorial on Youtube to learn the basics of Access. You can find the first video here.

A brief rundown of key considerations

EXCEL works well for:

  • Computing aggregate totals from a single, flat data source (an example of this would be answers given to a single questionnaire or a table of demographic info)
  • Computing totals and/or organizing information where you have a single common identifier on everything (e.g., if you have a bunch of forms completed by clients, but every form has the clients drivers license number on it)

Please note that even in the above cases, youll need a pretty firm grasp of how to write Excel functions in order to get the results you may need.

ACCESS works well for:

  • Integrating many different data sources relating to one central population (e.g, many different datasets that all give information about the clients your program serves)
  • Computing totals and organizing information when you have many different identifiers (such as if some of your forms have clients drivers license numbers on them, but other forms just have the last 4 digits of their SSN, you need a relational database that can match up your client list to any and all IDs that correspond to particular people)


And when in doubt, feel free to ask! Drop me a line at with some details about your data and Id be happy to suggest whether I think youd be better off using Excel or Access for your project.