The best elegant solution to override 65k rows limit in a sheet

[aaron.kempf]

SQL Server 2000 is a superior statistical tool than Excel. Using
Analysis Services-- free with a SQL Server 2000 standard or enterprise
license-- is a much better way to calc these types of numbers.

 

[aaron.kempf]

I dont see the risk with letting people create views and sprocs. give
them their own sandbox.. shit; i'll give each person their own database
if you want.

-Aaron

 

[aaron.kempf]

re: It's the mathematical manipulation of that data that generally
isn't
well suited to rdbms's.

Access is BOTH a RDBMS and a superior tool to Excel for creating
reports.

For starters, mathematical manipulation of data is FINE using tempory
tables.. derived tables.. parameters..

you can't do jack shit in Excel with parameters.

you have IF(IF(IF(IF(IF statements-- IS THAT REALLY FRIGGIN USEABLE?

You take a copy of the data for a quarterly rollup. You take a copy of
the data for a yearly report.

You have a billion copies of the same number

and I am here to tell you that ENOUGH IS ENOUGH
take your Excel and shove it up your a$$.

For starters; with Access.. and any other database.. multiple people
can use the same file at the same time.. and we dont have a 64k limit
LOL

and we dont email files that are 200 mb.. I worked with a client that
was breaking the TWO GIGABYTE LIMIT IN ****** EXCEL *******

I told that client that they have 'abused their Excel priveleges; and I
reccomend that you uninstall Excel from every machine in the house'.

That client was one of the most successful e-commerce companies of all
time.. yet they had a hundred Excel dorks running around like drunk
chickens with their heads cut off. I mean.. ENOUGH IS ENOUGH.

that is just a ridiculously complex system when an simple, small Access
Data Project and a freeware database engine could have run circles
around that spreadsheet. It can do FLEXIBLE REPORTS with more than 6
options..

It can slice and dice data a thousand different ways.

It can actually DO things instead of spending every tuesday copying and
pasting data into a new worksheet and troubleshooting your functions.

 

[Harlan Grove]

(e-mail address removed) wrote...

sprocs and views ARE written with Access. It is called ACCESS DATA
PROJECTS. MDB is friggin dead.. SQL Server has taken over the world.

Wrong again. Take a look at DBMS market share data. Oracle and IBM are
still ahead of Microsoft, which means (in qualitative terms since I
know you're mathematically challenged) that most business users work
for companies that don't have SQL Server. ADP isn't useful for them.
Other DBMSs provide stored procedures and views, but they can't be
created using Access, though I suppose it may be possible to create
'linked tables' via ODBC to non-Microsoft DBMSs as part of MDB Access
databases.

MDB isn't dead. It's the only useful option for people in companies
that don't run SQL Server.

I still disagree with your understanding of the popularity of Access.
I dont think that there is a single company in the nation with more
than 5,000 employees that doesnt have Access installed on SOME of their
desktops.

You're probably right that in most companies there are *SOME* seats
that have Office Professional. The question would be whether any of
those seats are outside the IT department. Where I work, I have Access,
but I'm the only one out of 22 people. There are other departments in
this field office, and I'm not certain what they have, but it's
unlikely more than a small fraction of them have Access. As for the
non-IT departments in home office with which I work, there are again a
few people with Access but most without it.

It's simple economics. Access costs more. Maybe not a lot per seat, but
multiply it by a lot of seats and the costs add up.

Access isn't aimed for IT people. Access is aimed at end users.

Granted. However, the access rights needed to do any sort of
development with ADP are generally restricted to IT departments only.
Few people outside IT and/or outside home offices have anything more
than read-only access to central company databases. Maybe ADP could let
them create reports, but I doubt it's a back door to allow them to
create views and stored procedures much less their own tables.

Having end users create views and sprocs-- that is not as bad of a deal
as it sounds. I learned to write queries in Access after an hours'
worth of training. Not that big of a deal.

It's nice you believe this. All you need to do now is become a CIO
somewhere and change the IT department culture to allow outside users
to do this. After a few weeks in which naive users bring system
throughput to a crawl because of poorly constructed queries and tables,
everyone else will wake up, and you'll have the opportunity to work for
some other company.

There's a reason few companies allow this. Casual database use is
harmless as long as the tables are small and the queries simple. Casual
database development with large company tables and complex queries, on
the other hand, is begging for trouble. The only way it makes sense to
provide limited development access to company databases is to provide
such part time developers with basic database development training.
That costs $$$, so it simply isn't going to be given to more than a
handful of non-IT users.

You don't seem to understand this.

Now you and I may have learned what we know about application
development on our own (disclosure: I took a 2-day class on Paradox 18
years ago and a 12 week SAS data step programming course at night
school 15 years ago, and that represents the total post-college
classroom training I've ever had), but that's not the case for most
non-IT business users who generally don't want to do development. We're
the wierdos because we like doing it and so are self-motivated to learn
this.

You don't seem to understand this either.

I just strongly disagree with your understanding of Access on the
desktop. And even if your end users dont have Access; they can still
use the Access Runtime if ONE person at the company buys Office 2000
Developers edition.. I'm not sure of the licensing with newer versions
of office; I just dont have time to deal with companies that aren't
willing to invest in their workers.

Fortunately it's up to company managers, not you, to decide how to
expend company resources. You'd just spend, spend, spend . . .

and just for the record; cutting and pasting data between worksheets---
running macros--- that is NOT an automated manner.

Macros not automated? How do you define automated? I'd guess you mean
procedural==bad, nonprocedural==good.

I just dont see the logic in usgin Excel at all.

That's because you can't comprehend that anyone uses computers to do
anything other than generate periodic reports against company data.

 

[Harlan Grove]

(e-mail address removed) wrote...

SQL Server 2000 is a superior statistical tool than Excel. Using
Analysis Services-- free with a SQL Server 2000 standard or enterprise
license-- is a much better way to calc these types of numbers.

So how'd it be useful creating amortization tables?! Show us all how
much easier it'd make this particular task, which is intentionally
simple with few steps so there's some small chance you could figure out
how to do it.

 

[Harlan Grove]

(e-mail address removed) wrote...

re: It's the mathematical manipulation of that data that generally
isn't well suited to rdbms's.

Access is BOTH a RDBMS and a superior tool to Excel for creating
reports.

This is your chronic mistake (but by now is seems hopeless you'll ever
understand this). Reporting isn't the same as statistical analysis.
Face it, you have no conception of what exploratory data analysis is.
It's not reporting unless you take the extremely simplistic view that
any display containing characters to represent words and numbers is a
report.

For starters, mathematical manipulation of data is FINE using tempory
tables.. derived tables.. parameters..

Then show how you'd generate all permutations of a set of tokens. I'll
even give you a start with a normalized table showing all permutations
of a set of 3 tokens.

Permutation Index Token
1 1 A
1 2 B
1 3 c
2 1 A
2 2 C
2 3 B
3 1 B
3 2 A
3 3 c
4 1 B
4 2 C
4 3 A
5 1 C
5 2 A
5 3 B
6 1 C
6 2 B
6 3 A

Permutation 1 would be the initial data in the table. Show how you'd
use INSERT queries to generate permutations 2 through 6 from
permutation 1.

Now, how any rational person could believe this is a more efficient
representation than

A B C
A C B
B A C
B C A
C A B
C B A

is beyond me, but I've given up trying to figure out how you 'think'.

you can't do jack shit in Excel with parameters.

No, *YOU* can't do squat with Excel, with or without parameters. For
me, it may involve more programming to parametrize Excel workbooks, but
it can be done.

you have IF(IF(IF(IF(IF statements-- IS THAT REALLY FRIGGIN USEABLE?

If it's what's needed, it's what's needed. Are you going to say there's
never any need for multiple nested SELECT queries? Multiple nested IF
calls in spreadsheets are their analogs.

For starters; with Access.. and any other database.. multiple people
can use the same file at the same time.. and we dont have a 64k limit

....

Provided that all data one needs to use is in centrally stored tables.
That's not always the case. You don't understand that.

and we dont email files that are 200 mb.. I worked with a client that
was breaking the TWO GIGABYTE LIMIT IN ****** EXCEL *******

There are times when Excel is definitely the *WRONG* tool for the task.
There are also times when Access is the wrong tool for the task. I've
been consistent in stating that one should use the best tool for the
task, and it's been you who claims Access can do everything.

 

[aaron.kempf]

listen fucknut

oracle and ibm cost $2k per SEAT.. or about 4 times as much as SQL
Server altogether

so when you say that oracle sells more database software.. in dollar
terms, I dont disagree.

I say, and Microsoft says-- and the cold hard numbers say-- that SQL
Server is the most popular database in the world.

and MSDE is a FRRRRRRRRRRRRRRRRRREEEEEEEEEEEEEEEEEEEEEEEEEE database
you dipshit.

so go and play with your unix and jerk off with oracle and ibm all you
want.

SQL Server has taken over the world. And the best data entry and
reporting platform in the WORLD??? it is called ADP.

 

[aaron.kempf]

Excel is definitely the WRONG tool for the task in about 2/3rds of the
situations that it is used.

Access -- MDB-- is almost always the wrong tool for the task.

the thing is that Excel isn't a TOOL.. it is like SOLITAIRE-- just a
waste of everyone's time.

I'm not saying that Access should be used everywhere. I'm saying that
you and your diseased excel dorks deserve to be kicked to the curb for
not adapting.

You're not USING computers-- you are stuck in the pen and pencil era.
Since all excel is, is a glorified pen and pencil

 

[aaron.kempf]

multiple nested sql statements are READABLE.

having a thousand different cells that have 1,000 different formulas--
that each say IF(A1='MON', 'MONDAY', IF(A1='TUE', 'TUESDAY,
IF(A1='WED', 'WEDNESDAY', IF(A1='THU', 'THURSDAY', IF(A1='FRI',
'FRIDAY'), 'WEEKEND')))))))

It is MUCH easier to join to a table that can translate between these 2
things. I mean-- for christ sakes.. are you really claiming that this
is FUNCTIONAL?

-aaron

 

[aaron.kempf]

Access/JET isn't even what we're talking baout; we're talking about ADP
against MSDE.

It can scale to a lot more users than that.

Excel doesn't build SYSTEMS

Excel builds PIECE OF CRAP REPORTS THAT ARE A MAINTENANCE NIGHTMARE AND
INFLEXIBLE.

If you have 100 excel reports you need a bunch of overpaid and
underqualified beancounters.

If you have 100 access reports; you can hire someone at minimum wage to
run these reports.. instead of recreating different versions of the
same report week in and week out

Excel just doesnt have enough tools to be useable. You can't bind to
another field-- like you can in Access..

And it really is ridiculous to have a different version of the formula
for each row.. i mean-- that is just the worst maintenance nightmare
EVER

 

[aaron.kempf]

what i mean is that you fucking idiots copy and paste data around.. i
mean-- it's just the most error prone 'analysis' i've ever seen.

you guys are a bunch of babies; and someday soon; you will be held to
the same degree of standards as 'us IT/programmer folk' and you guys
won't have the mental capacity to cut it.

as it is, you dont cut it.

you guys are a waste of labor dollars; and a complete and total waste
of time. You copy and paste data around--- ISNT THERE A BETTER WAY TO
DO THINGS?

Aren't you tired of having to email a single file around and share it
with a dozen different users?

Joe says change this field, Mary changed this data, Charley changed
these formulas-- It's just impossible to COLLABORATE in Excel; since it
is inherently a single-user application.

just for the record, i dont bind to fields like that-- although that is
by far the best feature in Access (that is impossible to recreate in
Excel)

I store my variables in a TABLE and then i use subqueries.

 

[Harlan Grove]

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

oracle and ibm cost $2k per SEAT.. or about 4 times as much as SQL
Server altogether

so when you say that oracle sells more database software.. in dollar
terms, I dont disagree.

I say, and Microsoft says-- and the cold hard numbers say-- that SQL
Server is the most popular database in the world.

....

All I can say to this drivel is that work for a company that doesn't
use SQL Server, i.e., it's not installed on any computer owned, leased
or used by my company. So how would SQL Server be of any use to me in
my job?

 

[Harlan Grove]

(e-mail address removed) wrote...

multiple nested sql statements are READABLE.

having a thousand different cells that have 1,000 different formulas--
that each say IF(A1='MON', 'MONDAY', IF(A1='TUE', 'TUESDAY,
IF(A1='WED', 'WEDNESDAY', IF(A1='THU', 'THURSDAY', IF(A1='FRI',
'FRIDAY'), 'WEEKEND')))))))

Only an idiot like you would use single quotes, which are syntactically
invalid in Excel and pretty much any other spreadsheet. If you had any
understanding of how to do things efficiently in spreadsheets, you'd do
this with a lookup.

=VLOOKUP(A1,{"MON","Monday";"TUE","Tuesday";"WED","Wednesday";
"THU","Thursday";"FRI","Friday";"S*","WEEKEND"},2,0)

Then again, if the A1 cells were derived from dates in other cells
(say, X99), it'd be even better to use

=IF(WEEKDAY(X99,2)<=5,TEXT(X99,"DDDD"),"WEEKEND")

It is MUCH easier to join to a table that can translate between these 2
things. I mean-- for christ sakes.. are you really claiming that this
is FUNCTIONAL?

Any idiot can do stupid things with any software. You're living proof.

If you do things efficiently and sensibly, then IMO spreadsheet
formulas are often easier to understand. Not always, but for simple
stuff like this, yup!

 

[Harlan Grove]

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

You're not USING computers-- you are stuck in the pen and pencil era.
Since all excel is, is a glorified pen and pencil

Actually you're not too far off here. You just fail to understand that
there most definitely are times when pencil & paper are the most
appropriate tools. Other than APL in the hands of an adept,
spreadsheets are the best tools yet developed for ad hoc calculations.
The problem is that too many people use spreadsheets to build large
applications. It can be done, but very few people are able to do it
well or reliably.

 

[Harlan Grove]

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

And it really is ridiculous to have a different version of the formula
for each row.. i mean-- that is just the worst maintenance nightmare
EVER

Obviously you've never inherited someone else's APL workspaces or
BASICA code (or MVS JCL, to get exotic).

If formulas in a given range are supposed to be the 'same', i.e., be
identical in R1C1-style addressing mode, it's simple to check, and I
suspect all serious Excel developers have macros to perform that check.

 

[aaron.kempf]

that is not true, Jamie.

MSDE 2.0 has no real hard governor; I believe.

-Aaron

 

[aaron.kempf]

Harlan

I'm sorry that your jerry-rigged spreadsheet program isn't flexible
enough to use single or double quotes.. I am pretty sure that real
reporting programs-- SQL Server and Access allows these pretty much
interchangeably.

I'm so sorry that you work for a stupid company; SQL Server is WINNING
THIS WAR.

-----------------------------------

From OLAPreport.com - an independent source of BI information

-----------------------------------
http://www.olapreport.com/market.htm - MS is by far the biggest seller
of BI. This is going to mushroom with the release of Maestro--
Microsoft's real entry into the OLAP client market.

-----------------------------------

From Steve Ballmer, TechEd 2005

We have over 50 percent unit share with SQL Server today and we think
we can just keep ramping that up.
-----------------------------------
Also
-----------------------------------

From http://www.eweek.com/article2/0,1895,1839686,00.asp

-----------------------------------------------------------------
A key problem with DB2 was that they chose to follow an options pricing
model similar to Oracle's versus a bundled model that Microsoft
embraced.

This is important because IBM essentially loses its value proposition
versus Oracle when you start adding on optional features such as cube
views, OLAP and data mining.

The separate options approach is required by Oracle because it is so
dependent on its database revenue and needs opportunities to keep
coming back to existing accounts.

IBM on the other hand, is more akin to Microsoft in its revenue
diversity.

It could have bundled much more functionality at a price that undercut
Oracle significantly, but it chose not to.

It also would have presented the market with a clearly simplified
licensing model, which the market desperately wants, especially as a
counterpoint to Oracle.
.....
Windows

I don't believe there is much IBM or any other vendor can do long-term
on Windows to stem the growth of Microsoft SQL Server.

However, with Windows currently commanding 40 percent of the server
market and with estimates pointing to a greater than 50 percent share
by 2010, it is a key platform in which to remain relevant.

Certainly there will still be a significant number or organizations
that will prefer to hedge their bets by utilizing database software
that will run on multiple operating systems.

The problem for IBM is that DB2 is actually losing share on Windows
(-4.4 percent in 2004), according to Gartner.

 

[aaron.kempf]

Harlan

I think that the problem with Jr Spreadsheet Developers; is that
they're not shooting for the stars. If you shoot for the stars; who
knows, maybe you'll end up at the moon.

If you shoot for the simplest possible solution than you'll be stuck
copying and pasting and running around like a chicken with your head
cut off.

From

From http://www.fmsinc.com/tpapers/genaccess/DBOD.asp

As the most popular database product in the world, Access clearly
dominates one of the most important segments of the database ecosystem.

When formulating the database strategy of an organization, it's
helpful to think of individual databases evolving over time. Healthy
database applications are not just created once but change and grow.
Bad ones go extinct, and sometimes even good ones die because their
environment (market) changes. Meanwhile mission critical applications
sometimes appear from unexpected sources.

Millions of databases are created in Excel spreadsheets each year, but
only a tiny percentage "graduate" to the next level: Access.
Similarly, only a tiny percentage of Access applications graduate to a
more sophisticated solution. In the interim, a huge number of database
needs are solved completely by Access. Access is simply the best at
what it does.

 

[Harlan Grove]

(e-mail address removed) wrote...

I'm sorry that your jerry-rigged spreadsheet program isn't flexible
enough to use single or double quotes.. I am pretty sure that real
reporting programs-- SQL Server and Access allows these pretty much
interchangeably.

Single quotes are used to delimit different syntactic tokens than
double quotes. I'd also note in passing that the Visual Basic language
for which you've made positive comments in the past also uses only
double quotes to delimit strings (aka text constants). Some programming
languages appear to support both, but at least in the case of Perl and
Unix shells, there are differences in how backslash escape sequences
are translated.

If SQL doesn't distinguish at all between single and double quotes, it
may be the only language to do so. That is, it'd be an idiosyncracy of
SQL's, not necessarily a feature worth emulating.

I'm so sorry that you work for a stupid company; SQL Server is WINNING
THIS WAR.

Ah yes, anyone who disagrees with Aaron or any company not doing what
Aaron thinks is best is, by definition, stupid. You do know the
definition of 'childish' don't you?

http://www.olapreport.com/market.htm - MS is by far the biggest seller
of BI. This is going to mushroom with the release of Maestro--
Microsoft's real entry into the OLAP client market.

This appears to concern OLAP only, not RDBMS's in general. My favorite
quote from this page is, "Microsoft has now clearly overtaken Hyperion
Solutions to become the largest OLAP vendor, but neither could be
called 'dominant'."

Winning what war? The one taking place in the wasteland between your
ears?

We have over 50 percent unit share with SQL Server today and we think
we can just keep ramping that up.

Gosh! What an unbiased source! Not!!

And unit share means what, exactly? Of all the machines running any
RDBMS, half of them run SQL Server? Here's a bit of news you could use:
it doesn't take as many IBM mainframes running DB/2 to do the same work
as micros running SQL Server. Ditto for Oracle running on minis and
mainframes.

This claim is about as meaningful as saying Nintendo Gameboys use more
batteries than Microsoft XBox's, so the Gameboys must be more powerful!

A key problem with DB2 was that they chose to follow an options pricing
model similar to Oracle's versus a bundled model that Microsoft
embraced.

Maybe the author's conclusion, "My bottom line is that I believe the
dominant platforms for databases will be Windows and Linux by the end
of this decade." is relevant for manufacturers and some service
industries, but it just ain't so for financial services. Mainframes are
still the place where most of the real data processing gets done, and
that's where DB/2 is still dominant.

Note: I'm not making any claims about the relative strengths of any
RDBMS. I've only pointed out that I work for a company that doesn't use
SQL Server but does use Oracle (running on Sun machines) and DB/2
(running on mainframes). Unless I was odd enough to want to install my
own database server at home, SQL Server is a big irrelevance for me.

 

[Harlan Grove]

(e-mail address removed) wrote...

I think that the problem with Jr Spreadsheet Developers; is that
they're not shooting for the stars. If you shoot for the stars; who
knows, maybe you'll end up at the moon.

....

And sometimes when you shoot for the stars you blow up on liftoff.

This raises the issue of distinguishing between 'developers' and
'users'. When it comes to databases, the distinction is much clearer.
Developers are those who can create tables, views, stored procedures,
etc. Users are those who can only run SELECT queries against existing
tables and views. Most non-IT business users are 'users' not
'developers' under these functional definitions.

The distinction almost doesn't exist for spreadsheets. Anyone with
Excel can do pretty much anything Excel is capable of doing. There are
no access rights, no GRANT command. More often than you believe it's
the case, Excel is the *only* practical casual development platform
most non-IT employees have to use.

Maybe I'm way low in my estimate of Office Pro seats, but I know there
are damn few non-IT workers with sufficient access to IT databases to
allow them to do any serious development. Non-IT users may be able to
use already developed database apps, but few of them can create
database apps. You may wish that weren't so, but that won't make it so.

So, most non-IT Access users are unlikely to get much out of using
Access except by creating their own MDB databases. Those have some
advantages over Excel in some cases, but it hardly qualifies as the
cure-all you claim it to be. Maybe they can install MSDE if their IT
departments would let them, but it'd be rather difficult to share such
databases among coworkers without a nightmarish peer-to-peer jumble.