Should I learn Access?

[kenshinxue]

Help!!! Should I learn Access?

Hi Guys,

Hope I can get your help.

I'm dealing with huge amount of data:
50+ excel files each with 60,000+ rows and 20+ columns (15MB+ each file)
I need to synthesize these files since they are weekly data - so I need to put them all together to get a better picture (in excel, basically what I need to do is lots of lookups and pivot table)

Even I am using Excel 2007, I find dealing with data with big size a pain.

Should I use Access instead of Excel? I find Access not that intuitive as Excel. So I am just wondering whether it makes sense for me to take time to learn it.

Thanks a lot!

 

[BrianB]

With that amount of data the answer is a firm "Yes".
I regularly worked with such amounts of data & beyond.
You are right in thinking that it is a steep learning curve, but it will be worthwhile in the end.

Hint. Just use Access to analyse data. When you want reports, make a special table (via a Make Table Query) in Access for just the data you need and import that table into Excel. A pivot table is often the next step. Access reports are a total waste of time (in my humble opinion ).

Focusing on that method will reduce the learning curve too.

 

[kenshinxue]

Thanks Brian!

 

[Norie]

I've got to agree with Brian on some points, but disagree on a couple of others.

If you are dealing with this amount of data Excel is not the best tool to use, a database like Excel would be far more easy to work with.

Mind you I'm not sure about the report/analysis thing.

Reports in Access can be very useful, though I'll admit they can sometimes be a bit hard to work with to get the results you want.

Exporting from Access to Excel could be an idea under certain circumstances, perhaps when you need some functionality that isn't available in Access.

 

[BrianB]

Hi Norie

I somewhat agree too. So let's be more specific.

It is all about what sort of job you want to do. Think of "and-and" rather than "either-or". The right tools (plural) for the right job. What sort of people are your "customers"? What is the output ? I had 20 years of monthly finance reporting for a large comms company (actually several in a queue) (surviving numerous possible redundancies) - dealing in millions of pounds and numbers of records increasing to millions as the year progressed. Basically extracting data from the corporate system (text files) and turning them into reports - all to tight deadlines at the end of each month. Literally "burning the midnight oil" - and after - to begin with. Big motivation to use the best methods. I had the rest of the month to develop them. The output was printed documents in the early years, worksheets later on. In the latter years there was the need to to have immediate "drill down" to show a list of records that made up any total. My "customers" were people with little knowledge or interest in learning Excel. They just wanted to see numbers.

For example, jut a few years ago I took over the additional job of producing and uploading a detailed £1million plus journal at a specific time at end of each month. Had to wait until the books closed at 6.00pm before I could start. The Excel only method let me leave work around 10.00pm. With Access and Excel I was eventually leaving before 7.00pm.

One of the keys of considering Access or Excel as the best tool is the number of records you need to handle. Excel has (had? does it actually work ?) a natural limit of 65,536 - but approaching this became a problem with speed of running. And file size! Also there is no way Excel will handle relational databases - a big key in my work where I had to "translate" records into various analytical groups. The company kept reorganising several times each year which meant that in any particular month I had to go back to January and recast all the numbers - in time for the meetings with department heads. Often I only found out about a reorganisation when I started analysing data and uncovered anomalies.

The basic method eventually boiled down to Text Files -> Access -> Access "Make Table" query -> Excel pivot table (easy to get drilldown here. A waste of time trying to format them) -> pretty formatted annual/monthly reports. Keeping the procedures separate enables a simple check total to be made at each break point, so can see exactly where anomalies occur. One key analytical item is "Other" which includes anything not categorised "above" but is included in the total.

Hope this helps.