Is Access the Answer?

[cwoodruff08]

Hello,

This is probably a stupid question so please forgive my limited understanding. Each month I record and analyze profitability data for my organization and add it to a spreadsheet month over month. Saved in an excel spreadsheet in binary format each month is usually 15-20 megabytes. When I get to about a quarters worth of data (about 300,000 rows x 54 columns) pivot table processing really bogs down. I know excel pretty well but have only a vague understanding of Access. Would an Access database be a good program to use instead of Excel? Any suggestions would be greatly appreciated.


Thanks,
cwoodruff08

 

[TerryHogarth21]

Here's an insight from MS's reference's on Excel and Access.
Using Access or Excel to manage your data - Excel

Access has a 2GB limit and if you ever gone past that would need SQL Server to start storing your data. 300k records by 54 columns is quite a good chunk of data every quarter.

The good thing is if you put it into an Access DB, for your audience you could connect Excel to Access and automate a few things.

Just some other questions to think about as well in Excel.

Why is there so much information - is there a way to cut that out or limit what you really need?
Where does the data come from - do you cut and paste it from somewhere, or is it calculated from Excel?

Have you reviewed spreadsheet design?

 

[Rx_]

Access and Excel play very well together.
Access provides an easy way to set data types. For example, make sure that currency, date, text, numeric ... are enforced in a field.
The other advantage is the use of Lookup fields as you learn more about the SQL (structured query language).
There are many good step-by-step tutorials in books and on-line.
As a former Access trainer, let me advise taking the time to learn the tool... that goes for any tool.
Otherwise, you will fall into the trap of doing things the way you know, instead of a better way.

Access can Link to Excel for example. An access worksheet can be treated like a Linked Table.

All of that said, if you are only doing one task once a month... Access may not be worth the learning curve.
For example: if it took 80 hours to become competent in Access to save 2 hours a year.
If you have other uses, then it might be justified.