Excel vs Access?

0

I have thousands of rows of product data that I keep in excel because it's easier for me to manage. But, I keep hearing about using a database program, access, to manage the parts.

Seeing as how I am able to do all data entry/filtering/sorting without issue in my current spreadsheet, is there really any reason for me to learn a new software and switch to access or some other database program?

I realize this forum is for excel but I'm hoping you guys might have some more insight on this.

Thanks!

rich

Answer
Discuss

Answers

0

I think you answered your own question. As long as your business needs are met using Excel, and it doesn't seem like they will soon outgrow it, then just stick with what you know best. On the other hand, if you plan to grow business quickly or start to manage tens of thousands or hundreds of thousands of rows, then moving to a database solution will make your life easier in the long-run.

The benefit of a database program is that it allows you to manage more data in more ways than you can simply using Excel. For simple needs, a database program is overkill most of the time.

Discuss
0

SQL is a database, Access is a "database light", and Excel is a data table mocked up as a UI.

The essence of a database is that it keeps indices, basically a sequence of rows by number. So, when you sort an Excel sheet alphabetically the sequence of its rows is changed which takes time and has a lot of other implications. The rows in a db always stay in the same sequence in which they were entered. What happens in a sort is that an index is created defining the new sequence of the rows and, if you will want to see it (who wants to look at a million rows of data?!) a copy can be created on the fly to show you what it would look like. You can access that copy programmatically, however, filter parts of it and minipulate the data in any way you can imagine and, since you come from Excel, probably in many ways you can't. A db keeps many different indices representing, for example, filters in Excel.

In your decision whether to use Excel or Access you should be driven by two criteria, speed and demand.

Speed is a function of volume. Excel can handle a million rows of data in a single spreadsheet, each row with many thousands of columns. When you find that sorting or retrieving data gets too slow for your patience it may be time to consider either splitting your data or upgrading.

Demand is what you can imagine. Excel's capabilities can be expanded considerably with the deployment of VBA (and so, again, can those of Access). However, if you have regular need to present a 10 x 6 cell report extracted from 250,000 data rows with 50 columns you will surely come to believe that Excel does it clumsily with much more effort (on the part of the user) that Access seems to require.

The main reason isn't, actually, in the application itself but in the demands the application makes upon its owner. When you look into Access you will be encouraged to spend time on designing your database - create a series of tables interlinked in relationships like one-to-one, one-to-many or many-to-many. Nobody ever said that such planning wouldn't make an Excel workbook more efficient as well. It would.

However, in Excel you are encouraged to think of a single data table. Excel doesn't demand of you to create worksheets just to manage the access of or to other worksheets. That is a huge advantage which you shouldn't give up unless you can see very clearly that the benefit you will derive from using a more capable application will be worth the time you spend on implimentation. You don't want to buy a Rolls Royce to drive to the pub at the corner of your block.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login