Playing with Access

I’m working on a new project that entails a lot of data which unfortunately Excel does not handle well – not on top of all of the formulas I need to use to generate categories and analyses, plus the pivot tables. Eventually, I scrunched the data down to just what my boss needed but then a question came up. It necessitated going back to the larger dataset and immediately, I began to have problems running pivot tables. They no longer worked with such a large data set. I was reduced to creating formulas to generate an answer, then copying and pasting those formulas as values, with the exception of the first row. The first row was retained as a formula in case I needed to re-do the formula. Then I went on to the next set of formulas to create another answer and those set of formulas were copied and pasted as values. And so on until I had all of my answers.

Excel is great for what-ifs and some slicing and dicing but it really was not designed as a database, and it certainly cannot handle the large amount of data and the kinds of formulas I create. I remember when I worked at my last company, I encountered a similar situation where I was generating a large database in Excel (monthly financial data that went back 3 years) and the file started to slow down tremendously, if not crash. Then I read that you could use pivot tables to pull data from Access, so I decided to try that methodology. Once I had everything set up in Access, I found my pivot tables ran so much faster. They worked like a dream.

So, for this new project, I finally got Access and I find that it calculates the answer to the question instantaneously. Unfortunately, I was hoping I could use pivot tables to pull the information from Access and answer the question from Excel. I wanted my boss to be able to use Excel for querying. But the question being asked involved the entire data set so I was back to being unable to use Excel. However, if I ask the question in Access, it appears to run with no problems. The trick is knowing how to set up the query questions properly. That is the stage I’m at right now: re-learning how to use Access, especially the queries, relationships set ups and formulas.

So, I’m off to have some fun this week in playing around with Access.