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

aaron.kempf@gmail.com wrote:
> Access is BOTH a RDBMS and a superior tool to Excel for creating
> reports.

Access is great for a certain type of static report, a 'dumb' report if
you will. Excel is demanded by actuaries, auditors, etc for
'intelligent' reports i.e. show how the sums were worked out, can vary
the data to show different projections, etc.

> It can do FLEXIBLE REPORTS with more than 6
> options..

Six isn't all that many, is it?

> It can slice and dice data a thousand different ways.

But each must be 'hard-coded' rather than being 'user-variable'. I
guess that's why you pick six out of the available thousands. Excel
allows e.g. the actuary to write the report herself rather than try to
explain complex notions to a code monkey.

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

If by 'fine' you mean 'just about OK' then I agree. BTW use of
temporary tables suggests a kludge. For 'parameters', Access/Jet is a
little limited because you can only write a one-line procedure.

> you have IF(IF(IF(IF(IF statements-- IS THAT REALLY <<snip>> USEABLE?

It's a genuine maintenance issue, yes.

> For starters; with Access.. multiple people
> can use the same file at the same time..

Yes, with Jet/Access the practical limit is about six concurrent users.

> we dont have a 64k limit

But you have a lot of tables that fall well short of 64K rows, don't
you.

> and we dont email files that are 200 mb

An .mdb file is a great way of persisting and sharing a relational view
data, don't you think?

> I told that client that they have 'abused their Excel priveleges

In your experience, have you ever encountered a situation where someone
has 'abused their Access priveleges'? (I warn you if you say 'No' I'm
going to accuse you of being biased.) I know I have, most notably a
complete absence of separation between the data access (small 'a') tier
and the front end application e.g. Access (bug 'A') Forms' code with
the control name hard-coded into the SQL DML ('SELECT * FROM tblOrders
WHERE ClientID = [Me]![txtClientID]'), tables 'bound' to controls, etc.


> 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.

Bad (Excel) systems can be hard to dismantle e.g. it costs real money.

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

I think you lose a lot pf people here. We don't do that and don't quite
see what you mean anyhow.

Jamie.

--

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@gmail.com 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?

aaron.kempf@gmail.com 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!

aaron.kempf@gmail.com 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.

aaron.kempf@gmail.com 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@gmail.com wrote:
> Access/JET isn't even what we're talking baout; we're talking about ADP
> against MSDE.

Correction then: you are limited to five concurrent users before
graceful but rapid degradation starts.

that is not true, Jamie.

MSDE 2.0 has no real hard governor; I believe.

-Aaron

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.

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.

aaron.kempf@gmail.com 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?

>>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.

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!

>>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.

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.

aaron.kempf@gmail.com 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.

you're crazy man.. go play with your BILLION DOLLAR DATABASES if you
want.

MDB is free; but still crap-- much like Excel.

MSDE/ADP is a free stack for all practical purposes. It has a FREE
front end called the Access Runtime-- it is a dream platform. And it
scales a LOT better than MDB.. I am tired of MDB; it is too slow; and
it really shoudlnt' ever be used from a mapped network drive--
performance is just too slow to do that.

That's what drives me crazy about MDB.. performance is worse than most
excel spreadsheets.. but it is a lot more powerful.

MSDE is the most powerful database in the WORLD.

Steve Ballmer says that 50% of the seats in the WORLD are SQL Server.
I know for a friggin fact it's true, dude.. I've been to 30 companies
over the past 7 years; I've seen 1 AS/400 and a half dozen oracle
boxes.. and HUNDREDS of SQL Server or MSDE boxes.

MSDE is an AWESOME stack for workgroups. I love it-- I just know that
this is a better choice than MDB at this point. MDB-- MS hasn't made
any exciting innovations in Access MDB for 10 years; and it pisses me
off.. They could start by making more drivers native.

There are 2 practical options these days-- mySql and SQL Server..
Oracle and IBM are bleeding red right now; IBM is keeling over like
we've never seen.. Oracle finally realizes that they're screwed on the
server front-- their products aren't UNBREAKABLE like they claim.

And SQL Server-- Analysis Services against Office Web Components-- it
makes beancounters like you irrelevant.

And the point of the matter--- IT SHOULDN'T OWN DATABASES. IT CAN OWN
THE SERVERS; DEPARTMENTS SHOULD OWN THE DATABASES.

Databases are a free and lovely and wonderfully flexible solution--
it's just that IT doesn't know whats best for databases... You design
databases in your department; and then you hand them off to IT.

I'm sorry that you work for a stodgy company.

if you use MDB; dont ever do anything across a mapped network drive--
performance drives me absolutely batty.

And SQL Server is the most popular db IN THE WORLD DUDE.

<aaron.kempf@gmail.com> wrote in message
news:1123112988.316749.293370@o13g2000cwo.googlegroups.com...
>
> And SQL Server is the most popular db IN THE WORLD DUDE.
>

Why don't you two take this to email?

And Aaron, if you don't want to help people (telling them to just use Access
isn't help,) just get out of this group.

Just my 2 cents.

Sum Yung Guy wrote:
> <aaron.kempf@gmail.com> wrote in message
> news:1123112988.316749.293370@o13g2000cwo.googlegroups.com...
> >
> > And SQL Server is the most popular db IN THE WORLD DUDE.
> >
>
> Why don't you two take this to email?
>
> And Aaron, if you don't want to help people (telling them to just use Access
> isn't help,) just get out of this group.
>
> Just my 2 cents.

Noooo!!!! I agree with you 100% that Aaron's replies don't help, but
this is a trainwreck that happens over and over. Who would want to
miss that? Not I. Aaron posts garbage in other groups, too.
He is not around to help at all.

It is a good thing that Aaron doesn't use his real name, or else people
might think he is a kook. I think his real name is McArthur Wheeler...

http://www.phule.net/mirrors/unskilled-and-unaware.html