Excel vs Access - Excel Experts Opinion

[duplinguy]

Dear Excel Experts,
Can I please have your feedback? I've been told many, many times from my company's I.S. guys that Access is better than Excel (they never explain why though). I work in an accounting department and am very partial to Excel. I know how to use Access somewhat, but I've found I can use Excel for almost anything. What's your take?

[tstumpf]

It depends on what the task is. I am in accounting and use both products extensively. If you are talking about database work, Excel has limited abilities compared to Access-specifically the ability to create a relational database. Excel works great for a "flat file" that is one table only. However, if you want to relate two tables (for example a customer master table to a sales table) then Access is the product to use. Development of Access databases can be quite a bit more complicated-it requires a bit more planning.

Reporting from a relational database has great advantages. I can create summaries versus detailed reports very easily.

Excel has analysis capabilities that are better than Access. I use Excel to actually create some reports from Access data.

If you let me know what you are trying to do, I can help steer you in the right direction.

trevor@exspeed.com

[Mark W.]

Which is better... an apple or an orange? An Excel worksheet is relational! Unfortunately, it's tables are limited to 65,535 rows and 256 columns, and it isn't regulated by an RDMS. For those that think that you can't "relate" two tables should see my postings at...

http://www.mrexcel.com/board/viewtop...7549&forum=2&3
http://www.mrexcel.com/board/viewtop...c=7572&forum=2

...but, if you do need an "industrial strength" RDMS you use ODBC drivers supplied by Microsoft to connect direcly to Oracle or SQL Server... or database applications such as Access, dBase, Foxpro... just to name a few.

For more on this see the Excel Help topic for...

"Ways to retrieve data from an external database"

[ This Message was edited by: Mark W. on 2002-05-15 17:28 ]

[Mark O'Brien]

Access sucks a$$. (this is an informed opinion)

(Real answer: I agree with Mark W)

_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-05-15 19:17 ]

[Brian]

Some thoughts in no particular order:

1) One problem with Access is that few people know how to use it and many people seem scared of it. Whereas almost everyone is comfortable with simple spreadsheets.

So if you want something that others can use when you're gone and won't keep comming back to you for simple changes, then excel could be better - depends or your co-workers.

2) A major disadvantage I find with excel 97 is that protecting a sheet blocks too many functions that I would like the user to be able to use (like autofilters) so I end up with unprotected sheets that can be inadvertantly modified and thus broken. {This has been improved in later versions.}

Or, I set up a print range to produce a nice report. Someone changes the print range to print a special report. The other users don't know how to change it back and come running. I could use VBA and dynamic ranges and such to control things, but that takes time to set up.

With access, it is much less likely that the user will inadvedently modify a table, form, or report. Security is easier to set up for various users.

3) In access, you place type an equation once and it is used for the entire query/report.

In excel, since every cell has its own formula, then if the sheet is not protected, then someone can change the single cell. If the result is not grossly in error, then you may never notice. (Using arrays in excel could make one formula calculate the results for every cell in a range, but I like many other, don't have much experience in this, I expect most people simple fill down the formulas to create a table.)

Howver, in excel I find it easier to check formulas. In access, I have placed formulas to calculate subtotals within a box on the report. If I make a mistake, it is hard to find, especially when I've used the name of another calculated box in the name. Therefore I assume that it is best to perform as many calculations as possible in queries, but I don't have enough depth of experience how best to do that.

4) The previous posting recommending SQL queries is fine, but again, if I handed that to anyone else in my office, they would not immediately understand what is happening or how to modify it. Whereas, if they look at the access query interface, they will probably catch on quicker.

5) How you obtain your data can make a big difference. Manual entry. From another database? another spreadsheet? Incremental text files?

6) If you are looking to amalgamate the reports from other systems to make a balance sheet or the like, then a spreadsheet may be good. However, if you want to track invoices and accounts payable by customer and things like that which require interfacing with customer lists and product lists and things, then a database is more likely the way to go.

7) If we did not end up in this Microsoft Oligopoly, then maybe someone would have invented a database/spreadsheet hybrid that would blow both Excel and Access out of the water...

[crimlet]

I think that maybe we are all missing one fundimental point, That the ease of data sharing is becoming one or such ease that soon you will be able to harness the functionality of both access and Xl. But again I do agree with the points memtioned before, that people are scared of Access. I was only having a conversation the other day with a work mate about two of the main quesions I have been asked at all job interviews: -
1) Can you use excel to Macro level ?
2) Can you use Access,

so to get ahead I think that we should all be using both tools to the best of their ability and web pages like this help to promote that

[Audiojoe]

I agree, Access blows donkeys. It's too fiddly and rubbish (how expert is that) but unfortunately we are forced to use it all the time as it does run queries well.

Much rather use Excel though

Plus it rhymes with "abcess"

[Jack in the UK]

to me i post this without reading above so you have non driven view

DATA size excel has limits, so access will do this.

You can link access as data source to excel so best of both worlds, simple use access as storage.

As complicated as that, excel give to power in a know application.

That’s my call and that’s what I would and have done.

[MarkHenri]

Let me be the first to answer your questions point by point--

"I've been told many, many times from my company's I.S. guys that Access is better than Excel..."
Their answer is rediculous. Better at what? If you're totalling numbers and creating graphs, complex formula calculations and what if scenarios, I think not. These are things a spreadsheet does, not a database. Sure I can write SQL that imitates some of Excels calculation ability but for hard core number manipulation, a spreadsheet is the correct tool.

However, if you need a repository for information that can be retrieved at any point on the network, forget Access. It doesn't have an TCP/IP client middleware nor a database instance running that controls in/out from their file structure. Access is a good desktop database but it should never be considered for serious use in a company where data integrity equals dollars. It simply does not have the robustness or controlability to make it a good choice.

"they never explain why though..."
Undoubtedly. It's because they have no clue what they are talking about. If they started saying words like MySQL, SQL Server 2000, Oracle, ASP, Schema etc., I'd consider giving them more credibility (unless you think they are just trying to blow you off).

"I work in an accounting department and am very partial to Excel."
I'll bet! Excel is a fantastic program for anyone working with numbers. This reminds me of the story when the two guys that invented Visicalc, (the original spreadsheet) were showing the prototype for the first time to a group of accountants. They changed a value in a cell and the whole sheet recalculated in a couple of seconds. Hands started trembling and checkbooks came flying out immediately. We know why you guys love Excel-- it save you tons of time and is easy to implement ideas in.

"I know how to use Access somewhat, but I've found I can use Excel for almost anything."
Yes, the database learning curve is kind of steep and it would probably be better to hire a programmer/analyst to come in and integrate a database solution for you rather than attempt it yourself. There are a lot of issues to getting one right so it provides useful information and is secure, reliable and fast.

If you would like to discuss this further, look me up on my site, www.markhenri.com, and I'll be glad talk to you further.

[Anne Troy]

Why not just pick the one with the prettiest icon?

Ok. Just kidding.

From what I understand from my ex-coworker Access gurus, and in addition to the posts above:

1. Don't use Access for more than 50 users, unless you're only using it to be the front end of one of those other databases mentioned above.

2. You can easily LINK Excel spreadsheets as your tables in Access. Bene: Your users are comfortable with and also can't tamper with the database itself, but CAN change the data in it. You can then minimize the number of people who must be Access-literate and/or have access to ALL data. Example: You have a network of stores. You put their Excel files in their individual network directories. The home office has them all wrapped up in Access.

3. If you have really lame-o users, Access allows you to provide the simplest interface for two-fingered typists.

4. I have listened to the heartache of users in HUGE companies when the developer leaves, the database has security all over it, and now they need to get into it. There was no easy method, though I understand there's a cracker out there now. But...used wisely, Access security is far better and much more detailed than anything you could do in Excel (though Excel XP has introduced some really nice protection options).

5. The most common mistake I see in Access that hurts in terms of cost and money is the table design. Planning tables is THE single, most important part of designing a database. I once had a user with 192 queries in her database: a sure sign that something is VERY wrong in terms of table design. It's difficult to tell someone like this that her database design is way bad. And then you design some fancy-schmancy union query that wouldn't be required if her damn tables were created right: called Normalization; basically if you've got the same type of data in more than one table in Access, your data isn't normalized. I would wager that 85% of Access databases out there suffer from this problem.

6. If you take a completely non-computer-literate person, the learning curve for Access is probably no more than that of Excel.

7. I love the want-ads that require experience with Access because that's what their database uses. And the people who have USED an Access database that now say they have Access experience. Ditto for the other apps tho.

8. What sucks about Access: graphing capability, reports export file formats (tho Word is there, it's really RTF), missing common functions like NETWORKDAYS and ROUND (these need to be done with VBA), the need to compact (have you compacted your DB lately?). There's more; I just can't think of them right now.

My 2 cents after four years of providing end-user support to *the big guys* employees.