(e-mail address removed) wrote...
....
ADP-- Access data projects are an awesome tool for entry-level developers to
implement views and procs (together they are labelled 'queries' in an ADP)
and this is an excellent choice for entry-level developers to use in order
to leverage their existing skillsets.
You're repeatedly misunderstanding the Excel user base. Most Excel
users, even most of those who wind up creating spreadsheets other
people will use, are *NOT* developers. This is just the way it is in
spreadsheets. No different from most PowerPoint users not being trained
visual artists or even commercial artists or desingers with any formal
training.
Reports in the bad old days were put together using paper, pencil,
calculator and eraser, the final draft then handed to a departmental
secretary (who else remembers the days when there were departmental
secretaries - in the plural) to be typed and distributed. Spreadsheets
and word processors have replaced that process with one that makes it
easier both to make and correct mistakes, but it hasn't changed the
essential manual nature of most spreadsheet and reporting tasks.
Many (most? almost all?) reports *could* have been automated back in
the days when all business software was written in COBOL, PL/I, FORTRAN
or VS BASIC, but the *developers* claimed to be too busy to do so. If
you want to blame a group of people for the ubiquity of poorly designed
spreadsheets used in business, look no farther than you in-house
developer colleagues.
If developers want to assume (for the first time) the *bulk* of the
responsibility for generating most reports, then I doubt anyone would
oppose their use of whatever software they deemed most appropriate for
the task. However, experience says this ain't gonna happen in the next
several lifetimes. So nondevelopers are going to continue doing what
they have been doing with all the lack of discipline and automation
(and automatability) that they've demonstrated in the past.
Simply put, most spreadsheet creators and users aren't developers, and
it's pure folly to believe they'll act like developers or even want to
do so. They'll use the tools they're comfortable using, and that means
Excel (and other spreadsheets) have a long and healthy future.
This ease of use isn't found anywhere else in the world; yet Microsoft-- and
other XLS-centric people-- aren't moving in the best direction.
I'll repeat my simple challenge: compare the development time for
creating an amortization table in Excel vs Access. And a new challenge:
compare the development time and effort creating a set of pro forma
financial statements to compare financial results under varying
interest rates for a moderately leveraged business.
There are things spreadsheets really do handle far more easily,
efficiently and even reliably than *ANY* DBMS or database front-end
can.
I still see RPG developers writign against AS400 and Microsoft says that
they are trying to combat this phenomnenon-- the problem is that Microsoft
hasn't ever dealt with the marketing problems that Access has-- and I think
that a lot of this prejudice comes from beancounters.. Because Access is in
the same package as Excel; and because beancounters are threatened by this
tool-- that they think is 'too hard to use' that is the root of the
problem.
Calling them beancounters may be one of the reasons they don't warm to
the prospect of using Access.
Also wrong in detail. There are several versions of Microsoft Office.
All include Excel, few include Access. Why? Maybe because most IT
departments buying Office realize that most users lack the training
needed to use Access effectively, and the benefits of providing them
that training don't justify the costs of doing so? Maybe using Excel
rather than Access is often (not always!) the economically rational
choice?
And most beancounters need to get into Analysis Services and MDX.
No they don't. Claiming they do only demonstrates how little you
understand what it is they get paid to do. You don't like cleaning up
the messes they make. Fine. But you need to understand that those
messes are a *small* part of all they do, and the cost of clean-up is
cheaper than the disruption that would be caused by changing how they
do *most* of their jobs.
MDX is a tool that allows you to basically emulate spreadsheets-- it is 100x
more powerful that Excel itself.
Again, how would this make amortization tables or what-if analysis
using pro forma financial statements easier?
And I think that it is dangerous to be an Excel user; and not move towards
MDX.
It's dangerous to use anything with some programmability in an
undisciplined and often ignorant way. Using DBMSs the same way would
also be dangerous. It's not the tool, it's how it's used that's the
bigger problem.
The first step towards using OLAP Cubes instead of spreadsheets is to have
your data in a relational format.
Normalized relational tables are *NOT* the most efficient or effective
data structures for a great many objects. This demonstrates limited
breadth of experience in business applications on your part. Simple
example - exchange rates. A normalized table would require 3 fields:
from country, to country, conversion rate. Typical spreadsheet design
would have from country listed in the topmost row, to country listed in
the leftmost column, and conversion rates displayed in an N-by-N grid.
Now with a more intelligently designed spreadsheet than Excel, e.g.,
Lotus 123 with it's @XINDEX function, it'd be easy to dereference the
conversion rate as
@XINDEX($CURRCONVTBL,"USA","UK")
Do I believe most users would find this more intuitive than
SELECT CurrConvRate FROM CurrConvTbl WHERE FromCountry="USA" AND
ToCountry="UK"
? Yes, I would indeed consider most users would find the former easier.
Sadly, in Excel they'd have to use an abomination like
HLOOKUP("USA",CurrConvTbl,MATCH("UK",INDEX(CurrConvTbl,0,1),0),0)
unless they get clever with their row and column labels, in which case
they could use something like
FromUSA ToUK
use of Access allows you to keep your data and reuse busienss logic when you
start brushing the limits of the tool
Excel reuse is more difficult than it should be, but since Excel itself
is an ODBC data source, your claim is difficult to substantiate. If you
know the workbook path, worksheet name and range address, you have all
you need to access the data. Would that be less intuitive than
referring to fields in some view? Maybe. But well-designed spreadsheets
would have information that would likely be reused in named ranges that
would possibly be more intuitive than a SQL query.
Excel is improperly used in about 70% of the situations where people
use it.
Presumably based on the spreadsheets you've seen. If most of those have
been reports of one kind or another, then you've seen a very limited
subset of all spreadsheets.
Excel isn't for financial reporting.
Only a few million people would disagree with you. Is it an ideal tool
for financial reporting? Seldom, but it depends on the precise nature
of the report. For summarizing monthly transactions, Excel is a poor
choice. For calculating and displaying current risk-based capital (bank
or insurance companies), Excel make a lot more sense than Access.
Databases should be used for financial reporting.
For the more mundane, repetitive sorts, sure. For the more
computationally challenging ones, probably not. Just because Access may
be better than Excel at *some* things doesn't mean Access is better
than Excel at *all* things.
