Vlookup nightmare

[Harlan Grove]

btw, i woudlnl't do that in ONE query.. i could do anything like that
in multiple queries

....

And if I had a million fingers and toes, I wouldn't need to know arithmetic.

It's the fact that it's a single formula in Excel vs multiple queries in
Access that's the telling point. IT'S SIMPLER IN EXCEL.

Now the nasty point: you can't do it in a single query in Access. Now if you
had a real ANSI SQL database with a TOP qualifier in SELECT queries . . .

 

[Mangesh Yadav]

flexibility of spreadsheets isn't all glamorous-- and im more flexible
with Access than you are with Excel.. i mean-- i build something that
works week in and week out.. and you build something that you have to
change every week

you call that flexibility?

And I always thought that when you require to change something in an
application, you have to change it, no matter if you use excel or access.

BTW, it takes much less time to change something in excel as compared to
access.

Mangesh

 

[aaron.kempf]

theres a TOP qualifier in Access.. no?

And just for the record; I am a diehard MSDE-ADP guy... so i use a
'real DBMS' with Access all day long.

 

[aaron.kempf]

RE:
----------------------------
AFAIK, my friends don't use Excel except perhaps for personal finance,
and Excel most definitely does make a MUCH BETTER financial calculator
than Access.

As for Excel developers, I do work with some, and it's my ongoing
project to get them to do things my way. However, they, unlike you,
know Excel is better for some things than Access (or Word or whatever),

know when it isn't, and have demonstrated some capacity for learning
how to improve their usage of Excel.
---------------------------


I disagree with both of these statements.

Access makes a better financial calculator than Excel. I mean--
seriously here if Excel is so awesome; why is it that the ATMs of the
world dont run Excel?

Just because i have everything I need via ODBC; that doesnt mean I'm
low-- I mean-- if you want to try to hit below the belt go ahead

What kindof data 'shoudn't be stored in a database'--- i beg of you...

Access does better reporting than Excel.
Access can link to 100 different databases-- Excel can't even chew on
64k rows

I mean seriously here

What makes Excel a 'better financial calculator' than Access?
Other than the fact that normal Access (MDB) and Excel share a _LOT_ of
functionality (pun intended)
Its just the point that you can either have 100 copies of your data; or
you can have 1.

You can either have to re-create the same report; week in and week
out-- or you can have a VLOOKUP NIGHTMARE.

The answer is to do simple joins in the db world-- it is leaps and
bounds more exciting, dependable and scalable.

 

[Harlan Grove]

(e-mail address removed) wrote...

theres a TOP qualifier in Access.. no?

....

OK, I screwed that up. Recently moved from Office 97 to Office XP, so
didn't check what was available in Access 2002. However, when there are
duplicate entries, it gets tricky.


Given table t as

k f
1 1
2 1
3 1
4 1
5 1
6 2
7 2
8 2
9 2
10 3
11 3
12 3
13 4
14 4
15 5


The query

SELECT TOP 5 [t].[f]
FROM [t]
ORDER BY [t].[f] DESC;


yields

f
5
4
4
3
3
3

(6 records, not 5), which returns the same result as the portable

SELECT [t1].[f]
FROM [t] As [t1]
WHERE (SELECT COUNT(*)
FROM [t] As [t2]
WHERE [t2].[f] > [t1].[f]) < 5
ORDER BY [t1].[f] DESC;


OTOH, the query

SELECT TOP 5 *
FROM (SELECT [t].[f]
FROM [t]
ORDER BY [t].[f] DESC);


yields

f
5
4
4
3
3

(5 records - the correct result).


Why doesn't the first query yield the correct result?

Do you really believe the necessity of subqueries is simple enough for
new database users to understand as readily as they can Excel formulas
like LARGE(Range,{1,2,3,4,5}) ? Are you nuts?! Well, yes, you are,
you've already proven that.

 

[Harlan Grove]

(e-mail address removed) wrote...

Access makes a better financial calculator than Excel. I mean--
seriously here if Excel is so awesome; why is it that the ATMs of the
world dont run Excel?

Because all they do is handle ADD/SUBTRACT transactions. Breathtakingly
simple calculations even the likes of you could implement without
screwing up.

It's telling that you think financial calculators do little more than
add and subtract.


Challenges:
- From months ago which you never fully answered: how would you
generate an amortization table in Access?

- The more recent challenge: how would calculate a 5-point moving
average in Access?

- How would you calculate the risk premium for a bond from an issuer
with a BB rating?

- How would you perform what-if analysis on construction project NPVs
under varying interest rates, factor costs and duration?

- How would you calculate the McCauley duration of a bond in Access?

Just because i have everything I need via ODBC; that doesnt mean I'm
low-- I mean-- if you want to try to hit below the belt go ahead

It does mean you have no contact with outside sources. Draw your own
conclusions.

What kindof data 'shoudn't be stored in a database'--- i beg of you...

If you mean as a storage subsystem, as soon as Longhorn comes out,
everything stored on a Windows PC will be stored in a database. I won't
care about that as long as I don't need SQL queries to fetch whatever I
want. As long as it stays in the background, universal database storage
is fine. If it unduly restricts what I enter where, it's bad.

Begs a question: when Longhorn does come out, will everything on disk
be available via ODBC?

Access does better reporting than Excel.

Granted, and if what you need to do is generate canned reports, Access
is a much better tool.

USE THE RIGHT TOOL FOR THE TASK.

Access can link to 100 different databases-- Excel can't even chew on
64k rows

Excel can link to ludicrously high numbers of external sources too, but
it'd be a bad idea.

As for the row limit, no big deal. Sensibly designed spreadsheets would
never use more than a few thousand rows. It's a clear sign that
spreadsheets aren't appropriate when more than a few thousand rows are
indeed needed.

The much more serious limitation in *BOTH* Excel *AND* Access is 256
columns per worksheet or fields per table. Kinda limits what sort of
crosstabs you could create in Access. While this is a limit in Excel,
there are other spreadsheets that have more columns, but that OT. So
Access can't handle 257 fields in one table, what a toy!

What makes Excel a 'better financial calculator' than Access?

See challenges above. See what it takes to solve them in Access. Each
can be done quite simply in Excel.

Its just the point that you can either have 100 copies of your data; or
you can have 1.

Already granted: as a storage subsystem, databases are just fine. The
differences arise from what it takes to do anything useful with the
data.

Granted many of the workbooks I create contain the same strings, e.g.,
"Account Name:", "Amount Due:", "Harlan Grove", and they contain many
of the same formulas. I'd bet not all the RDBMS tables you use are
fully normalized.

When I've been able to control original designs, I've avoided workbook
file storage. I've used plain text files mostly rather than databases,
but at some point I'll become a good XML citizen. But note that the
bulk of this data is hierarchical in nature, and I store it in a
hiearchical fashion (similar to overblown .INI files). So my records
are text files, and my fields are whatever makes the most sense to me
(fields may themselves be tables or hierarchies of subfields).

You can either have to re-create the same report; week in and week
out-- or you can have a VLOOKUP NIGHTMARE.

It doesn't matter whether it's VLOOKUP or SUM, if the final result were
a canned report, Excel wouldn't be the best tool. If the VLOOKUP were a
simple lookup into a sales tax table by state and county, then there's
no nightmare. If the VLOOKUP result were to be used as a piece in some
order-dependent calculation (e.g., NPV), then even a complicated
VLOOKUP in Excel would be much easier than anything you could figure
out in Access.

The answer is to do simple joins in the db world-- it is leaps and
bounds more exciting, dependable and scalable.

OP's problem didn't require any joins, just returning multiple columns
from a single VLOOKUP call.

 

[aaron.kempf]

god harlan

the thing is that Access DOESNT have a 256 field limit-- it is only MDB
that has that limit.
ADP against MDB (free SQL Server desktop engine, available a half-dozen
ways)

Challenges:
- From months ago which you never fully answered: how would you
generate an amortization table in Access?
A SIMPLE CARTESIAN AND A COUPLE OF QUERIES.


- The more recent challenge: how would calculate a 5-point moving
average in Access?
A NON-EQUIJOIN x5
select sum(t1.value) + sum(t2.value) + sum(t3.value) + sum(t4.value) +
sum(t5.value)
from myTable t1
inner join mytable t2 on t1.mydate = (t2.mydate - 1)
inner join mytable t3 on t1.mydate = (t3.mydate - 2)
inner join mytable t4 on t1.mydate = (t4.mydate - 3)
inner join mytable t5 on t1.mydate = (t5.mydate - 4)

- How would you calculate the risk premium for a bond from an issuer
with a BB rating?
HAVE A RATINGS TABLE AND A TABLE VARIOUS FACTORS

- How would you perform what-if analysis on construction project NPVs
under varying interest rates, factor costs and duration?
DATA ENTRY FORMS-- LETTING SOMEONE ENTER A 'VARYING INTEREST RATE'

- How would you calculate the McCauley duration of a bond in Access?
http://bluecollardollar.com/bonds_measuring_risk_03.html

sum of pv divided by share price you really thikn that databases can't
do math that complex?
what the HELL are you talking about HARLAN

add up a couple of numbers and divide by another number

databases are MUCH better with math than either you or Excel is

lol

 

[aaron.kempf]

re:
SELECT TOP 5 [t].[f]
FROM [t]
ORDER BY [t].[f] DESC;

this appears to be a BUG in Access MDB i hate MDB

it works correctly in ADP/MSDE

i dont agree that a half-dozen vlookups is easier to do in Excel than a
simple JOIN in Access.
I mean-- seriously-- join on common keys and get the values you want..
instead of 'trying to use excel with referential integrity'

just for the record; XML is a bigger waste of time than Excel is in the
first place-- i mean.. you're jumping from the frying pan into the
fire. RDBMS is a more efficient way to store data than XML. RDBMS
allow 2 people to edit the same file at the same time-- not even visual
source safe allows editing XML well enough in order to make it USEABLE.

screw XML and Excel with a 10-foot pole.

I just think that you're smoking crack harlan; spreadsheets are for
babies.

go play with your vlookups dork
and having 20 different worksheets to build a simple report LoL

and yes-- the OP problem DOES require a join; but he doesnt know it,
because he is smoking crack and stuck with Excel-- because he doesnt
know any better

I beg of you OP; drop the training wheels and go and take a class on
Oracle or Access or _SOMETHING_

i mean-- Excel _SUCKS_ because it isn't manageable-- it isn't a
scalable tool.
It never will be; and a sharepoint site where excel dorks can upload
files-- that doesnt spell collaboration to me.

re:
Granted, and if what you need to do is generate canned reports, Access
is a much better tool.

or-- if what you want is a flexible, dynamic reporting platform-- you
would use a database.. or better yet; a pivot Table from OWC via
Analysis Services-- this is about 100 times more powerful and about a
billion times more scalable than Excel.

and just because it's a pivotTable-- that doesn't mean it has ANYTHING
to do with Excel

 

[Harlan Grove]

(e-mail address removed) wrote...

the thing is that Access DOESNT have a 256 field limit-- it is only MDB
that has that limit. ADP against MDB (free SQL Server desktop engine,
available a half-dozen ways)

You don't get it. You say Access, but you mean Access and several other
products. Access *ALONE* is a toy, and this may come as news to you but
all employees in most large companies are *PROHIBITED* from installing
software on their own. So there may be a reasonable fraction of MSFT
Office users who have Access (I'd guess 25-30%), but almost none of
them have any of these other add-on products, and many (myself
included) work for companies that don't use SQL Server, so anything
associated with SQL Server would be prohibited even if the company were
willing to install other additional software.

So I'm make my statement more precise: Access *STANDALONE* has a 256
field per table limit.

Challenges:
- From months ago which you never fully answered: how would you
generate an amortization table in Access?
A SIMPLE CARTESIAN AND A COUPLE OF QUERIES.

You made this same mistake a few months ago.

A cartesian is a mistake since the resulting amortization table would
have 5 fields at most: period (usually month) number, loan payment
(unnecessary, could be dropped), interest portion, principal portion,
and principal balance.

Principal balance in month M is always principal balance from the
preceding month, M-1, less the principal portion in the current month,
M.

Let me help you. Given initial loan amount, A, periodic effective
interest rate, R, and loan term in periods, N, the fixed loan payment,
P, is given by PMT(R,N,-A) [you're free to gripe about the need for the
sign convention - I do]. The first column is a given: month numbers
from 0 to N. The rest of the first record is also a given: zeros for
interest and principal portions, initial loan amount, A, for principal
balance.

The calculations for the interst portion are simplest (at least in
Excel).

Interest Portion in M = Principal Balance in M-1 * ((1 + R) - 1)

Principal Portion in M = P - Interest Portion in M

Principal Balance in M = Principal Balance in M-1 - Principal Portion
in M

Getting the periods into the amortization table (at) would require
either entry of period numbers in the first field or an insert query
against an existing table (s) with a field (s) running in sequence from
0 to N. Something like

INSERT INTO [at] ( m )
SELECT s.s
FROM s LEFT JOIN [at] ON s.s = at.m
WHERE (((s.s)<=[N]));


Then set the initial (period 0) principal balance to the initial loan
amount using something like

UPDATE [at]
SET [at].pb = [A]
WHERE (((at.m)=0));

However, at that point it gets a bit difficult for SQL because all the
remaining calculations are ORDER DEPENDENT and RECURSIVE. The good news
is that there are other ways to build amortization tables by
calculating the principal portion of each level payment separately.

Principal Portion in M = P / ((1 + R) ^ (N - M + 1))

This can be done with another update query.

UPDATE [at]
SET [at].pp = [P]/((1+[R])^([N]-at.m+1))
WHERE (((at.m)>0));

Then the corresponding interest portions are just the level payment
less the principal portion. Another update query.

UPDATE [at]
SET [at].ip = [P]-at.pp
WHERE (((at.m)>0));

Finally, the principal balances require a somewhat more complicated
update query.

UPDATE [at] AS at1 INNER JOIN [at] AS at2 ON at1.m-1=at2.m
SET at1.pb = at2.pb-at1.pp;

5 queries of which of which one is an insert and the other 4 are
updates, and the final update requiring an inner join, along with the
fact that a SQL approach *REQUIRES* using the more complicated direct
calculation of principal portions rather than the much simpler direct
calculation of interest portions would only appear to your warped mind
as simpler than the Excel approach.

- The more recent challenge: how would calculate a 5-point moving
average in Access?
A NON-EQUIJOIN x5
select sum(t1.value) + sum(t2.value) + sum(t3.value) + sum(t4.value) +
sum(t5.value)
from myTable t1
inner join mytable t2 on t1.mydate = (t2.mydate - 1)
inner join mytable t3 on t1.mydate = (t3.mydate - 2)
inner join mytable t4 on t1.mydate = (t4.mydate - 3)
inner join mytable t5 on t1.mydate = (t5.mydate - 4)

Average, not sum, where's the division by 5?

Also, Access 2002 chokes on this: Syntax error (missing operator) in
query expression 't1.mydate = (t2.mydate - 1) [...] = (t5.mydate - 4)'.

If a self-proclaimed expert like you can screw this up, how could you
believe this would be simple for people who aren't database experts?

- How would you calculate the risk premium for a bond from an issuer
with a BB rating?
HAVE A RATINGS TABLE AND A TABLE VARIOUS FACTORS

Gosh! A table!

And use formulas, er, expressions to calculate the result! And just how
would the expressions in a dbms differ from the formulas in a
spreadsheet?

Also, risk premium is a function of how long you intend to hold the
bond. Make that a cell reference in a spreadsheet formula. In a dbms
approach, you could make it a query parameter, but you'd need to rerun
the query as well as change the parameter - 2 steps - as opposed to
just entering a new value in a precendent cell and letting a
spreadsheet automatically recalc the new result.

- How would you perform what-if analysis on construction project NPVs
under varying interest rates, factor costs and duration?
DATA ENTRY FORMS-- LETTING SOMEONE ENTER A 'VARYING INTEREST RATE'

And the varying factor costs and project duration? Especially with the
factor costs varying over time as an autoregressive time series, so
needing an inner join between a table and itself.

Your answer is vacuous.

Databases are pathetic for what-if analysis.

- How would you calculate the McCauley duration of a bond in Access?
http://bluecollardollar.com/bonds_measuring_risk_03.html

sum of pv divided by share price you really thikn that databases can't
do math that complex? ....
add up a couple of numbers and divide by another number

Databases are much harder to use for order-dependent calculations like
the pv calculation you mention. It's more complicated than you seem to
believe, but that's because you really don't understand these
calculations. They can be done in databases, but they're much easier in
spreadsheets. In Excel, all you'd need to do is load the Analysis
ToolPak (which, unlike all the extras you assume when you say Access,
comes on the Excel or Office CD) and use the DURATION function.

databases are MUCH better with math than either you or Excel is

Since all you seem to know is adding and occasionally subtracting,
databases may be adequate for you, but that doesn't mean they're
adequate generally for calculations.

 

[Harlan Grove]

(e-mail address removed) wrote...
....

i dont agree that a half-dozen vlookups is easier to do in Excel than a
simple JOIN in Access.

If there's a single lookup value and the intended result is multiple
fields from the table, all it takes (as others have shown way back in
this thread) is VLOOKUP(x,t,{a,b,c,d}[,0]) - using an array of column
indices to return multiple fields.

We could argue about the relative merits of spcifying the columns to
return by index or field name, but it'd be pointless - it's a matter of
taste and also a question of whether there are field names (which
aren't required in spreadsheets).

I mean-- seriously-- join on common keys and get the values you want..
instead of 'trying to use excel with referential integrity'

If any sort of join were needed, then I agree that it's fragile at best
in Excel. Gimme Lotus 123 for that. FWIW, that's when SQL.REQUEST
becomes useful.

just for the record; XML is a bigger waste of time than Excel is in the
first place-- i mean.. you're jumping from the frying pan into the
fire. RDBMS is a more efficient way to store data than XML. RDBMS
allow 2 people to edit the same file at the same time-- not even visual
source safe allows editing XML well enough in order to make it USEABLE.

You don't get it.

XML provides FLEXIBILITY. You only seem to be happy in the highly rigid
world of database tables. XML isn't intended for efficient storage,
it's intended for portability and transparency. As for what I need ,
unless I wanted to store hiearchical information as single binary
object fields in a RDBMS (so database as nothing more than storage
subsystem), I don't see RDBMS storage as anything helpful. Yes I do
know I could represent hierarchical information in multiple tables with
one-to-many and many-to-many relations, but it's a PITA.

As for 2 people editing the same file at a time, the files I mentioned
would be akin to database records. You think it'd be a good idea for
two people to edit the same record simultaneously?!

and yes-- the OP problem DOES require a join; but he doesnt know it,
because he is smoking crack and stuck with Excel-- because he doesnt
know any better

Wrong. The OP was careless in his wording. He wants to enter a single
area code and return multiple fields of information: sales rep, region,
phone number, etc.

 

[aaron.kempf]

re: You don't get it. You say Access, but you mean Access and several
other
products. Access *ALONE* is a toy, and this may come as news to you but

all employees in most large companies are *PROHIBITED* from installing
software on their own. So there may be a reasonable fraction of MSFT
Office users who have Access (I'd guess 25-30%), but almost none of
them have any of these other add-on products, and many (myself
included) work for companies that don't use SQL Server, so anything
associated with SQL Server would be prohibited even if the company were

willing to install other additional software.

So I'm make my statement more precise: Access *STANDALONE* has a 256
field per table limit.


LISTEN ASSHOLE ACCESS COMES WITH A MSDE LICENSE-- IT IS FREE WITH THE
ACCESS LICENSE; AND ADP IS A MUCH MORE RELIABLE PLATFORM THAN MDB

im sorry that 'relationships' are such a PITA to you

grow up and lose the training wheels kid

 

[aaron.kempf]

adding formulas to spreadsheets is totally passe.. i mean.. gag me with
a spoon; it is no longer 1993

you can put all your business logic in a billion different places for
all i care

centralization of business logic is not possible with excel
it is a waste of manpower.

you can either run around changing formulas in 100 different places--
or you can change your formula in one place in a database query

and btw LISTEN
MSDE IS INCLUDED ON THE CD

databases ROCK for what-if check out writeback in analysis services--
you can edit dimensions you can edit facts

it rocks and you need to grow up and stop using excel every day

 

[Harlan Grove]

(e-mail address removed) wrote...
....

. . . ACCESS COMES WITH A MSDE LICENSE-- IT IS FREE WITH THE
ACCESS LICENSE; AND ADP IS A MUCH MORE RELIABLE PLATFORM THAN MDB

It's not the license, you braindead moron, it's the SOFTWARE itself.

It's NOT installed on most business PCs, so it's NOT AVAILABLE to most
business PC users. It'd require IT departments to install it, and that
usually requires some sort of justification. "Aaron says it's kewl"
won't often suffice as justification.

To begin with, most people don't have Access, so can't get MSDE. Most
of those who do have Access won't have MSDE installed because it's not
part of their company's standard PC configuration. For the 0.001% of
business PC users who have it, they still have to learn how to use it.

It's an indicator of how removed you are from the people who generate
revenue or even the people who monitor them that you have no clue
what's available on the typical business PC.

im sorry that 'relationships' are such a PITA to you

Believe it or not, normalized tables don't produce universally minimal
storage images, nor do they necessarily produce ideal data structures
for all applications.

 

[Harlan Grove]

(e-mail address removed) wrote...

adding formulas to spreadsheets is totally passe.. i mean.. gag me with
a spoon; it is no longer 1993

....

Back in 1987 Lotus introduced Improv for NeXT machines. It was going to
kill spreadsheets as we know them because, in part, it provided more
structure and eliminated formulas in cells, replacing them with
formulas applied to dimensions creating derived dimensions and
categories. Sound familiar?

Here in 2005, Improv is dead, and spreadsheets are alive & kicking.

Databases and analytical add-ons for them have their place, but they're
NOT ideal for rapid, calculation-intensive analysis of limited data
from nonelectronic sources. You obviously don't work with that sort of
stuff.

Your prediction of the immanent extinction of spreadsheets is very
likely to prove every bit as prescient as all the other same
predicitons that have preceded yours.

 

[aaron.kempf]

sorry man
I dont believe that the ratio is anywhere CLOSE to that range.

i mean-- get real-- real companies are required to purchase office
professional licenses right?

i know that back in the day; companies would try to use small business
edition-- i dont really know or care how standard editon is effecting
this

but i think that you're crazy; and that 70% of business desktops have
Access installed.

databases _ARE_ ideal for rapid calculations

it's all about putting your data into Analysis Services and making it
more useable and flexible through 'real' pivot Tables instead of the
cheesy pivotTables that excel gives you

thanks for the info about improv.. i am going to have to research that
i just dont think that copying forumulas into 10,000 different cells is
the most efficient way to do things.

if nothing else; it wastes space.

i just think that the world is going in the wrong direction-- and i
think that the answer is more simple Access databases-- and less
spreadsheets.

i just believe that this war will get a lot more interesting in the
next few years.

-Aaron

 

[aaron.kempf]

and just for the record

the business justifaction is this:

a) dipsticks run around building reports in Crystal Reports
- the most ridiculous architecture EVER since Access comes with
office

b) companies spend $30k for each of you spreadsheet dorks-- to pound
away on the same report week in and week out

c) I can come in; at $100k/year; and I can put a half-dozen
beancounters of of work over the course of a 12-month project

that is what i call business justification

monkeys building spreadsheets week in and week out isn't efficient

 

[Harlan Grove]

(e-mail address removed) wrote...
....

i mean-- get real-- real companies are required to purchase office
professional licenses right?

Nope. While I have Access on my machine (and a lot of other nonstandard
software which I've convinced the powers that be to let me have), no
one else in the department in which I work has it. So under 5% of Pcs
here with Access.

i know that back in the day; companies would try to use small business
edition-- i dont really know or care how standard editon is effecting
this

Too bad, because many companies are using standard.

but i think that you're crazy; and that 70% of business desktops have
Access installed.

Why don't you check? As I mentioned above, it's under 5% in the
department in which I work (me only out of 22 people). Granted that's
annecdotal. But if you believe most Office installs include Access,
you're the one smoking something.

And just stop for a moment and consider what it means for your, er,
point if fewer than 1/3 of business PCs that have Excel also have
Access.

databases _ARE_ ideal for rapid calculations

While you're still writing the queries (note the plural) needed for
most order-dependent calculations, I'd already have gotten the anser.
You're confusing having the machine calculate the answer AFTER you've
finished telling it what to do with the time it takes to tell it how to
determine the answer. Writing a few formulas is much quicker than
writing the nontrivial queries needed to produce the same result.
Consult the amortization table queries I had to do since you had no
clue how to do them. If you think you could do that in only one or two
queries, prove it!

it's all about putting your data into Analysis Services and making it
more useable and flexible through 'real' pivot Tables instead of the
cheesy pivotTables that excel gives you

Pivot tables are no use whatsoever for amortization tables, moving
averages or other time series analysis, discounted cashflow analysis,
etc., all of which involve (guess I need to repeat this AGAIN)
order-dependent calculations and recursive calculations.

Pivot tables spew out aggregates, just like simple queries. You don't
realize those are lightweight calculations.

thanks for the info about improv.. i am going to have to research that
i just dont think that copying forumulas into 10,000 different cells is
the most efficient way to do things.

Good luck, because Improv died off before Mosaic came into being, so
there may not be much about it on the web.

if nothing else; it wastes space.

Depends. It may use more disk space than would be used in SQL queries,
but less space in memory if the only database equivalent requires
nontrivial joins.

i just believe that this war will get a lot more interesting in the
next few years.

Unlikely. As long as training budgets are minimal, databases are going
no where. I don't consider that a good thing, but I think you're
seriously deluded if you believe end-user database usage is going to
catch up with end-user spreadsheet usage.

There may be software that eclipses spreadsheets, but it won't be
databases.

 

[Harlan Grove]

a) dipsticks run around building reports in Crystal Reports
- the most ridiculous architecture EVER since Access comes with
office

You may have a point here. IIRC, Crystal Reports started off as a tool for
spreadsheet users to produce reports from databases. I haven't disagreed
with your point that databases are much better suited to producing reports.
I've never used Crystal Reports, so I have no idea what it's like. FWLIW,
the last time I created reports was back in the early 1990s using SAS.

b) companies spend $30k for each of you spreadsheet dorks-- to pound
away on the same report week in and week out

c) I can come in; at $100k/year; and I can put a half-dozen
beancounters of of work over the course of a 12-month project

Here you have no idea what you're talking about.

I've never heard of any *large* companies where any important periodic
general reports were produced using anything other than dbms's or COBOL/IMS.
There may be lower level programmers making no more than $30K. As for $30K
spreadsheet users, you're talking either near entry level or admin
assistants with a few years of experience. Such people wouldn't be spending
more than a fraction of their time preparing reports.

The people who'd be more likely to be producing reports in spreadsheets are
sales and marketing types who're pulling data from multiple sources, few if
any of which providing ODBC access. These people would also be spending very
little of their total work time producing these reports, and they'd also be
paid quite a lot more for doing their real jobs (selling or marketing).

I've never met anyone who spends the bulk of their work time producing
reports using spreadsheets.

monkeys building spreadsheets week in and week out isn't efficient

No doubt you'd believe people using calculators week in and week out is also
inefficient. Or people making phone calls rather than sending bulk mail.
You're so far removed from outside customers that you have no clue what's
needed when working with them or for them. All you seem to know is what to
do for IT departments.

 

[aaron.kempf]

databases are going nowhere?

you really are retarded.

do you know what makes the internet so powerful?
lets just say it isn't excel..

go play with your spreadsheets little kid

OH NO-- MY MATH IS TOO HARD... I 'HAVE TO DO IT IN EXCEL'

lol

temp tables; queries; scripts, loops

i am sorry that you work in a backwards-ass company where 'training
budgets are minimal'

you dont need a training budget to get into Access.
But if you had a real job-- you could afford to buy your own training.

i just spent $3500 on SQL Server 2005 training-- I just think that it's
funny that you think that spreadsheets are SOOOOO important

i just think that it's a total waste of time to pay some numbers dork
$30k to push numbers around in a spreadsheet.. i mean-- there is a
better way-- it is called DATABASES and REPORTS.

 

[aaron.kempf]

and XML doesnt make the problem go away-- it makes the problem WORSE.

then those spreadsheet dipshits-- and then lazy 1-dimensional
programmers-- they just leave XML in a single field in a database--
about the least efficient thing that you could do

for starters; XML is SLOWER and LARGER than CSV for example. (i prefer
tabbed for most file formats though)

sure it's trendy-- but it's also a total waste of time.
i mean-- it's slower; its' more complex; and it's not a step in the
right direction