Revisit Access

I’m revisiting a project that had lots of data – so much that I could not work with it in Excel very well. I was forced to build up the formulas, file by file. I would build a column of formulas to do one thing and if it worked, save that file with the formula. Let’s call that file1. Then I would copy that file into file2 and make the formulas pure numbers. I would then add a new column of formulas to do another task. So file2 would contain the values of the formula result of file1 and a new column of formulas. File3 would build on file2 with the column of formulas in file2 converted to values and a new column of yet another set of formulas. And so on. I believe I created 12 files to get to the data structure I needed.

I eventually put everything into Access and built up a query that pulled the same set of data structure as the 12th Excel file.

Now I’m revisiting that project and pulling data from Access via pivot tables, and boy is it a lot faster using pivot tables and Access. When I open up the last Excel file just to double check the pivot table/Access results, that file was very slow, especially in filtering. So I wasn’t dreaming it, it is much, much faster to use Access tables to store the data and utilize pivot tables to pull the information.