Is Excel the best?

[ac3100]

I am curious to know if there are any other applications that are better than excel for the enduser for creating analysis tools that comes with incredible flexiblity and provides the maximum amount of relevant information.

I have been creating inventory management tools for several years. My company is in the process of pulling information from several stores nationwide. This will allow us to review individual store perfomance and ID national trends etc.

The information will be captured in SQL. I am proposing the information be digested in excel, partly because I can do just about anything from a programming standpoint and I don't know of anything else that comes close from an end user standpoint.

I am looking for "unbiased opinions" and any other feedback i.e. pros and cons to this question.

Thanks for taking the time to read this.

Andy

 

[Norie]

What about Access?

Unless you are going to use Excel/spreadsheet specific functions that might be better.

It would also depend on exactly what type of data you are dealing with.

Another consideration would of course be what you want the end user to 'see' and what you want them to be able to do.

With Access you could create forms/reports/queries and 'seperate' the end user from the actual data, whcih could even be stored in a seperate database.

 

[ac3100]

Access is probably to small.

I have found that excel is much easier to program to customize. I usually will take something very complicated and difficult to understand and make it super easy for the enduser.

Most retailers use reports to look at inventory perfomance form the item level to the category level. I like to create tools that are several reports in one so that the user does not have to go multiple places. This involves alot of complicated formulas and macros. A lot of sorting a filter functions as well.

I have been extracting data and put it into a "Data" worksheet from which to grab information and put into the worksheet that has the information digested.

I have not worked with SQL yet but I am getting ready to learn. My plan is to be able to have the excel reporting tool that will interface with the SQL database.

Andy

 

[Norie]

Access is probably to small

What do you mean, Excel can only deal with 65536 rows/records whereas I've worked in Access with 2,000,000+ records.

It was slow but it worked.

Most retailers use reports to look at inventory perfomance form the item level to the category level. I like to create tools that are several reports in one so that the user does not have to go multiple places. This involves alot of complicated formulas and macros. A lot of sorting a filter functions as well.

This sort of thing could probably be done easily with queries which can look at all levels of data, grouping as needed.

Sorting and filtering can also be done in Access.

All of this sort of thing could be accessed by the end user via forms so the user doesn't have to go to 'multiple places.

And you can of course program in Access VBA.

 

[Joe4]

I agree with Norie. I work with both Excel and Access, and am more comfortable with Excel, but given the choice between these two products, Access usually works better for the type of project you are ralking about.

Access will allow you to house much more data, and is designed to be a database, so it handles queries, reports, and forms better than Excel. With a little bit of knowledge, it is easy to make simple GUI forms for users to use. VBA for Access is also very similar to VBA for Excel (though it does not have a macro recorder, and the documentation isn't as good as Excel's).

Access can also be used as a front-end for a SQL database. So if you data gets too big for Access someday, you can move it to SQL, but continue to use Access as your front-end.

 

[NateO]

Why not use SQL Server as the back-end and Excel as the front-end?

Access can't touch Excel in terms of a quantitative analysis tool or for visually presenting data (Charts). The thought of cranking out MIRRs in Access, while is doable, makes me cringe a touch...

You can use ODBC or OLE DB/ADO to interface Excel with SQL Server, I prefer the latter.

My two pennies.

 

[ac3100]

Thanks Nate,

This is really about the end-user.

They do not care what is going on behind the scenes.

The tools that are created for the end-user must be easy to understand and use.

Access seems too limited or difficult.

I have felt strongly that Excel is the best Front-end application for reporting and basically helping people make the best decisions.

Excel seems to be the best for presenting and interacting with information
SQL seems to be the best for warehousing data. (my assumption)

Access, although can do both seems to be average for both the front-end and back-end.

Thanks for your insights.

If anyone else has thoughts to this, please respond!

Andy

 

[Joe4]

The tools that are created for the end-user must be easy to understand and use.

Access seems too limited or difficult.

I know that Nate is an Access hater , and I usually prefer Excel over Access myself, but when creating easy-to-use GUI screens and applications for the average (or below average user), I almost always use Access.

Once you are comfortable creating user forms and writing a little VBA code, you can do a lot with Access. I think creating forms (GUI screens) and reports is much easier in Access. Access is also written to be a database program, so it does a better job at maintaing and updating data

However, the real determining factor should be what your goals are. For example, as Nate pointed out, if you want to do charts and graphing, Excel is definitely better for that. However, if you want to use it more as a database where people can enter, view, and query data and produce reports, I would recommend Access.

Of course, your knowledge and comfort level with Access may also come into play.

I should also note that I haven't done much with SQL, but you may be able to do much of what you want in SQL instead of Access and use Excel as your front-end, though I still prefer Access over Excel for reports and forms.

 

[bat17]

One other thing that may be worth considering is that Access can be a secure enviroment, Excel cant.

Peter

 

[XL-Dennis]

Hi guys,

Nowadays it's important to split up the datastorage and the value-added processes/analysis of numerical data.

I´m with Nate on this and highly recommend to use Excel as a front-end tool for the end-users. And why bother the end-users with things that they are not forced to deal with as Andy point out very clear.

By using a flexible tool like Excel we can also add some business-logic into it which means that the back-end database can even be more easy to create & maintain.

Anyway, depending on the situation there exist lot of options when it comes to databases but for best performance the only choice would be a database that can deal with stored procedures (which MS Jet Databases can't but SQL Server can). MySQL will be capable of working with stored procedures in the next coming version (5.0).

From a strictly technical point of view there exist no alternatives to the de facto standard that ADO represent in combination with SQL (although I'm looking forward to use ADO.NET in Excel ).