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

thousands of different queries.. i mean. ****.. like that's an actual
slam on databases??

lol

i'd rather have thousands of queries than a dozen spreadsheets.. any
day of the week.
i'd be honored to have thousands of queries.

but i could do anything in one query that you can do..
and most of the formulas are even THE SAME. So you can take your
worthless Excel knowledge and turn it into something useful.. instead
of having to recreate the wheel week in and week out; you can build it
once and then it's easy to run a report for different weeks.

in excel; you have a different version of the report every week with
different numbers..

it's just a diseased way of doing business. keeping all your logic and
all your data in a dozen different workbooks.. i mean-- that is
inefficiency at it's most dangerous form.

CRYSTAL REPORTS.. or ACCESS. Sure it might cost a little bit more for
one or two users; but Excel is just a dead end street..

100 spreadsheets can't SAVE a company.. 100 database reports CAN.

-aaron

aaron.kempf@gmail.com wrote...
>you're wrong.. subqueries and temp tables can do anything like that
>that you need to... and it's a LOT more scalable than Excel..

A macro assembler can do anything I want to do on a computer too, but I
know better than to use one to reinvent wheels inefficiently.

>you can write views and sprocs-- using drag and drop.. it is just a lot
>more practical than Excel.. you keep all your data in one place; and
>you dont have to email 200mb spreadsheets.. you can email around small
>reports instead

Can you write sprocs in Access that run against MDB files? Can non-IT
users with read-only access to database servers create them? Or views?

And how, exactly, would views be all that much help? If I wanted
rolling N-period averages of values in some table, I'd need a separate
view for repeated inner joins for each possible value of N. You just
don't get it that while it's possible to generate tables with records
composed of values from different records in some source table, that
particular referencing mechanism in SQL is more complicated and less
understandable than the equivalent referencing syntax in damn near all
other software. I realize you don't believe it, but there are times
arrays and explicit indexing are superior to SELECT queries on tables.

Another thing you're failing to understand is that nearly all non-IT
dept business users *LACK* developer access to their company's database
servers. On the other hand, if they have Excel at all, they can develop
in Excel.

Keeping data in one place presupposes users can *STORE* their data
centrally. Most non-IT users with some database server access can only
*LOAD* company data from central store. Their own data is stuck on
their own machines.

Your lack of perspective prevents you from understanding this.

>or.. get this.. URLS
>if you put your logic into a database then it is easy to build it as a
>webpage.. so it's easy to share it between offices for example

If you can post anything to your company's intranet servers. Again,
most non-IT people can't.

It'd be easy enough to write an Excel workbook to pull data via
external queries, then use spreadsheet formulas to produce final
results, then e-mail the workbook. That's what most non-It people do
because that's the *ONLY* means they have to share results in a
more-or-less automated manner. Maybe some of them wouldn't know how do
this any other way, but if no other way is possible, it'd be highly
inefficient to waste time learining useless alternatives.

aaron.kempf@gmail.com wrote...
>thousands of different queries.. i mean. ****.. like that's an actual
>slam on databases??
>
>lol

Running them, sure. Writing them is a whole different issue. So some
idiot actually would pay you to write

SELECT (D1.val + D2.Val) / 2 AS MA2PT
FROM D AS D1 INNER JOIN D AS D2 ON D1.key = D2.key + 1;

SELECT (D1.val + D2.Val + D3.val) / 3 AS MA3PT
FROM (D AS D1 INNER JOIN D AS D2 ON D1.key = D2.key + 1)
INNER JOIN D AS D3 ON D2.key = D3.key + 1;

SELECT (D1.val + D2.Val + D3.val + D4.val) / 4 AS MA4PT
FROM ((D AS D1 INNER JOIN D AS D2 ON D1.key = D2.key + 1)
INNER JOIN D AS D3 ON D2.key = D3.key + 1)
INNER JOIN D AS D4 ON D3.key = D4.key + 1;

....

rather than fire you for wasting time by not using Excel when it's the
more efficient tool?

>i'd rather have thousands of queries than a dozen spreadsheets.. any
>day of the week.
>i'd be honored to have thousands of queries.

And how long would it take you to write them?

>but i could do anything in one query that you can do..

OK, show hou to create an amortization table in *ONE* query.

>in excel; you have a different version of the report every week with
>different numbers..

No, *YOU* generate weekly reports. If that's all you're good for, you
should be using a database.

>it's just a diseased way of doing business. keeping all your logic and
>all your data in a dozen different workbooks.. i mean-- that is
>inefficiency at it's most dangerous form.

Sometimes it is inefficient, but it's flexible. Far more users have
Excel and shared .XLS files than have database server access rights
needed to do anything more than run SELECT queries against company
data, not their own customer's data.

>100 spreadsheets can't SAVE a company.. 100 database reports CAN.

Reports don't save companies. The discipline needed to keep a clean,
accurate set of books does, and that could be done in databases,
spreadsheets or even paper & pencil (gosh, there were companies that
made money before there were computers?!). I'll bet Enron produced lots
of DBMS-generated reports, fat lot of good it did their shareholders
and employees!

Harlan Grove wrote:
> Pulling things from *MULTIPLE* tables is easy. Pulling related
> information from the *SAME* table but from different records is
> *DIFFICULT*. If I'm wrong, prove it! Show us how to do it elegantly!

Here's an Access/Jet stored proc to calculate as per your moving
average example:

CREATE PROCEDURE harlen (:arg INTEGER = 1) AS
SELECT T1.row_ID,
(SELECT AVG(T2.data_col)
FROM Test AS T2
WHERE T2.row_ID BETWEEN T1.row_ID
AND T1.row_ID - (:arg - 1))
AS moving_avgerage
FROM Test AS T1;

:arg replaces your value in cell C$1 i.e. a parameter used to vary the
number of points in the moving average.

>From the 10 minutes I spent on it I can't be not sure this is 100%, but
you can see that the solution *is* a simple (elegant?) subquery and
certainly does not require an extra join for each number of points.

Dare I suggest your 'spreadsheet mentality' prevent you from seeing
this <g>?

Jamie.

--

Jamie Collins wrote...
....
>Here's an Access/Jet stored proc to calculate as per your moving
>average example:
>
>CREATE PROCEDURE harlen (:arg INTEGER = 1) AS
>SELECT T1.row_ID,
>(SELECT AVG(T2.data_col)
>FROM Test AS T2
>WHERE T2.row_ID BETWEEN T1.row_ID
>AND T1.row_ID - (:arg - 1))
>AS moving_avgerage
>FROM Test AS T1;
>
>:arg replaces your value in cell C$1 i.e. a parameter used to vary the
>number of points in the moving average.

Is row_ID necessary in the result table?

>From the 10 minutes I spent on it I can't be not sure this is 100%, but
>you can see that the solution *is* a simple (elegant?) subquery and
>certainly does not require an extra join for each number of points.
>
>Dare I suggest your 'spreadsheet mentality' prevent you from seeing
>this <g>?

Possibly. Now you get to deal with the remaining abstractions that
spreadsheets can provide:

=AVERAGE(OFFSET(INDIRECT("'"&WorksheetName&"'!"&DataColumnRange),
ROW()+K,0,N,1))

That is, how would you go about making the table and field names
parameters as well?

But nice that someone rose to at least one challenge. I figured it was
beyond Aaron's grasp. So moving along to another, wanna try the one in

http://groups-beta.google.com/group/...e=source&hl=en

(or http://makeashorterlink.com/?G1082238B ).

Harlan Grove wrote:
> Now you get to deal with the remaining abstractions that
> spreadsheets can provide:
>
> That is, how would you go about making the table and field names
> parameters as well?

I'm not falling for that one <g>. A SQL table represents a set of one
or more entities and their attributes, not a dumb data store. A
procedure that operates on a table, any table, regardless of what it
represents in the data model make no sense. Do a Google search using
"dynamic SQL".

I could post a procedure that takes a delimited string of dumb values,
parses it into rows and columns and puts the values into a generic
table and point my earlier procedure at this generic table, but what
would that prove? It does illustrate an interesting point though: just
because something is easier to do in Excel (because it is more
flexible) does it should be done? I've seen (and done) all sorts of
strange things in Excel for a multitude of reasons (for the challenge,
for fun, like Everest "because it's there") which made no 'business'
sense at all, and some of these in a business scenario (but mainly in
****'s blog <g>).

> But nice that someone rose to at least one challenge. I figured it was
> beyond Aaron's grasp. So moving along to another, wanna try <<snip>>

And I decline the amortization table in SQL challenge, too <g>. SQL
isn't a good statistical tool: SQL implementations, such as Jet
(Access) have relatively few mathematical functions and standard SQL
has only basic ones e.g. doesn't even have MOD.

The things SQL does best are as follows:

1. Data management
2. Erm ...
3. That's it!

The point is, SQL does data management so well that it's hard to
justify using anything else.

There's a MVP and regular in these groups who I respect highly but
admits 'doesn't do SQL', who will post dozens of lines of
complex-looking VBA code to loop through a range, filtering for certain
values, etc. This is how code used to be written, I understand, before
SQL was invented; indeed was the catalyst for SQL being invented i.e.
needing a simple, standard way of saying SELECT mycolumn FROM mytable
WHERE anothercolumn = value.

It flies in the face of nearly 40 years of data management development
when verbose proprietary procedural code is chosen in preference to
simple SQL. I know because when I've posted a single line of SQL in the
same thread as a VBA loop solution, the OP has thanked Pete Daverson
(name changed to protect the innocent).

I'm not bitter; in fact, I find it amusing. The gurus in the Excel
newsgroups prefer VBA loops because it is more palatable to the newbies
and VBA dabblers (procedural mindset, difficult to think in sets, etc).
The same gurus probably know and use SQL in their professional capacity
but they, like aaron, also know the truth: if SQL is best for data
management then Excel is not the tool for data management.

Among all the 'noise', aaron makes a few good points very badly.

Jamie.

--

Jamie Collins wrote...
....
>The point is, SQL does data management so well that it's hard to
>justify using anything else.
....

Fine. If one needs to perform data management, as distinguished from
data entry (so managing data already entered in some in-house computer
system), then I agree rdbms's are usually best.

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

>I'm not bitter; in fact, I find it amusing. The gurus in the Excel
>newsgroups prefer VBA loops because it is more palatable to the newbies
>and VBA dabblers (procedural mindset, difficult to think in sets, etc).
>The same gurus probably know and use SQL in their professional capacity
>but they, like aaron, also know the truth: if SQL is best for data
>management then Excel is not the tool for data management.

Agreed. However, management isn't the only thing one does with data.

There's also the question whether OPs have access to Access. Excel is
on lots more PCs than Access, and even if one has Access doesn't mean
one can do anything more than run SELECT queries against company
databases. So, if OPs post in Excel newsgroups, respondents are on
solid ground assuming they have Excel. It's much less safe to assume
they have Access, and giving Access-only replies is at best off-topic.

That said, I agree about the overuse of VBA and procedural code in all
the Excel newsgroups other than .programming. Myself, I try to stick to
formulas to the greatest extent possible and resort to VBA only to
extend formulas for the most part. I've made a few naive plugs for
using SQL.REQUEST instead of complex array formula, but have stopped
doing so after learning of memory leak problems with it in some
versions.

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

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.

Over the past 7 years; I have been at a half dozen companies with over
ONE HUNDRED access databases.

That is a lot of reports...

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

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.

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.

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

basing your reports on queries that you store on the SERVER --- as is
FREE with MSDE and Access Data Projects-- this is the best way to run a
business. Since you can change a sproc in one place instead of
changing 100 MDB queries.. or instead of changing 1,000 spreadsheets.

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

It is absolutely a disease. You guys are a disgrace to your companies;
modernize, or be kicked to the curb.

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.

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

-Aaron

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

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

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

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

I can't agree with you on that one.

Did you notice how Harlan can argue from both sides of the fence? This
is why I can see right through you ....