Secrets from the Data Cave, October 2014
by Sarah McCruden
Welcome to CRC’s monthly series of articles on all things techie: Secrets from the Data Cave! (For those who don’t know, the title references our room — fondly referred to as “the bat cave”— where data staff can geek out in an isolated setting.) Here we’ll be offering you a fascinating sneak peek into the cave, with the latest updates & tips on what we’re 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.
I should start by saying that Excel CAN do a lot of things that Access does. For example, I’ve 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 that’s 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 client’s driver’s license number on it)
Please note that even in the above cases, you’ll 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’ driver’s 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 firstname.lastname@example.org with some details about your data and I’d be happy to suggest whether I think you’d be better off using Excel or Access for your project.