Is Access the Answer?

cwoodruff08

New Member
Joined
Sep 8, 2014
Messages
1
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

TerryHogarth21

Board Regular
Joined
Mar 20, 2012
Messages
245
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_

Board Regular
Joined
May 24, 2011
Messages
52
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,975
Members
430,099
Latest member
rdhoy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top