+ Reply to Thread
Results 1 to 101 of 101

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

  1. #1
    Registered User
    Join Date
    07-06-2005
    Posts
    3

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

    I'd like your opinion/solution to break the limit of 65k lines in a XLS sheet.

    I.E. let's say we can have 4 millions records to treat and have excel read virtually every record from this db. Is there an elegant and transparent solution for the users of my company?

  2. #2
    moi
    Guest

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

    Split the records into multiple files, write an app in C++ that does the
    dirty work considering the .xls-file folder as one huge file.
    And if one .xls-file is full, just let the app create a new one.
    U can also write a module in VBA (Access maybe) but that's not so fast.



    "taz" <[email protected]> schreef in bericht
    news:[email protected]...
    >
    > I'd like your opinion/solution to break the limit of 65k lines in a XLS
    > sheet.
    >
    > I.E. let's say we can have 4 millions records to treat and have excel
    > read virtually every record from this db. Is there an elegant and
    > transparent solution for the users of my company?
    >
    >
    > --
    > taz
    > ------------------------------------------------------------------------
    > taz's Profile:
    > http://www.excelforum.com/member.php...o&userid=24948
    > View this thread: http://www.excelforum.com/showthread...hreadid=384823
    >




  3. #3
    Bob Phillips
    Guest

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

    Most elegant? Use a database.

    --
    HTH

    Bob Phillips

    "taz" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I'd like your opinion/solution to break the limit of 65k lines in a XLS
    > sheet.
    >
    > I.E. let's say we can have 4 millions records to treat and have excel
    > read virtually every record from this db. Is there an elegant and
    > transparent solution for the users of my company?
    >
    >
    > --
    > taz
    > ------------------------------------------------------------------------
    > taz's Profile:

    http://www.excelforum.com/member.php...o&userid=24948
    > View this thread: http://www.excelforum.com/showthread...hreadid=384823
    >




  4. #4
    Registered User
    Join Date
    07-06-2005
    Posts
    3

    I try to explain...

    Let's assume that we must use EXCEL as a reporting engine / designer that pull the data from a dbms (like SQL & Oracle) but has to eat big chunks of data at a time (more than 65k rows obviously).

    I insist on excel because many people know that tool and do not depend on IT department for many aspects

    Let's think about excel like a heart that pumps in/out the data after the right transformations/calculations

  5. #5
    Bill Sharpe
    Guest

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

    Even Quattro Pro won't help you here. It allows 1,000,000 rows in one
    worksheet.

    Bob Phillips is right. Use a database. If the company has 4 million
    records it should be able to afford database programs for its users.

    I expect Aaron to pop in with his usual rant, too.

    Bill

    "taz" <[email protected]> wrote in message
    news:[email protected]...

    I'd like your opinion/solution to break the limit of 65k lines in a XLS
    sheet.

    I.E. let's say we can have 4 millions records to treat and have excel
    read virtually every record from this db. Is there an elegant and
    transparent solution for the users of my company?


    --
    taz
    ------------------------------------------------------------------------
    taz's Profile:
    http://www.excelforum.com/member.php...o&userid=24948
    View this thread:
    http://www.excelforum.com/showthread...hreadid=384823



  6. #6
    Gordon
    Guest

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

    "taz" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I'd like your opinion/solution to break the limit of 65k lines in a XLS
    > sheet.
    >
    > I.E. let's say we can have 4 millions records to treat and have excel
    > read virtually every record from this db. Is there an elegant and
    > transparent solution for the users of my company?


    Access.



  7. #7

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

    yep here i am

    Excel is for babies
    use a database.

    i reccommend MDB for newbies; and ADP for poeple with decent SQL skillz

    -aaron


  8. #8
    Bob Phillips
    Guest

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

    You should still be able to do most of any math, aggregations that you need
    on the DB and pull back much less data. How many times do you need to look
    at a million rows?

    --
    HTH

    Bob Phillips

    "taz" <[email protected]> wrote in message
    news:[email protected]...
    >
    > Let's assume that we must use EXCEL as a reporting engine / designer
    > that pull the data from a dbms (like SQL & Oracle) but has to eat big
    > chunks of data at a time (more than 65k rows obviously).
    >
    > I insist on excel because many people know that tool and do not depend
    > on IT department for many aspects
    >
    > Let's think about excel like a heart that pumps in/out the data after
    > the right transformations/calculations
    >
    >
    > --
    > taz
    > ------------------------------------------------------------------------
    > taz's Profile:

    http://www.excelforum.com/member.php...o&userid=24948
    > View this thread: http://www.excelforum.com/showthread...hreadid=384823
    >




  9. #9
    (PeteCresswell)
    Guest

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

    Per taz:
    >
    >I'd like your opinion/solution to break the limit of 65k lines in a XLS
    >sheet.


    MS Access back end.
    --
    PeteCresswell

  10. #10
    Harlan Grove
    Guest

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

    [email protected] wrote...
    ....
    >Excel is for babies


    Here you're only parading your ignorance.

    >use a database.


    Here you're right. Anything truly needing more than even 5K rows should
    be in a database.


  11. #11
    Harlan Grove
    Guest

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

    moi wrote...
    >Split the records into multiple files, write an app in C++ that does the
    >dirty work considering the .xls-file folder as one huge file.
    >And if one .xls-file is full, just let the app create a new one.
    >U can also write a module in VBA (Access maybe) but that's not so fast.

    ....

    Well if fast is the key, use Perl.


  12. #12
    Harlan Grove
    Guest

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

    Bill Sharpe wrote...
    ....
    >Bob Phillips is right. Use a database. If the company has 4 million
    >records it should be able to afford database programs for its users.


    Doesn't need more than ODBC connections to a central dbms. For those
    things Excel can do that databases can't, Excel can't handle even 65K
    data points (e.g., LINEST and LOGEST would choke on worksheet-size data
    sets). For everything else, use dbms views and queries to categorize
    and aggregate.

    >I expect Aaron to pop in with his usual rant, too.


    In this case Aaron would be dead right. Only a moron would try to
    process +4M records in a spreadsheet.


  13. #13

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

    just for the record, ACCESS shouldn't be a product of your IT
    department. It is a LOT more fun that Excel.. since you can make a
    report and print if weekly; instead of make a report and make it
    weekly.

    I mean-- it's like the 20th century instead of the 18th century (using
    Excel).
    (Just for the record, I claim that Analysis Services, MDX, etc is 21st
    century technology even though they shipped with SQL 7 in ~98-ish)

    Access isn't that difficult.

    Learn to write queries-- If you cant do it; then quit your excel job
    and go to work at McDonalds. 8 years ago, I worked as a tester; and 30
    of us testers were taught how to write Access queries in an hour.

    I mean seriously-- with a couple of queries; a couple of macros and a
    little bit of time-- there ins't anything in the world that you can't
    calculate with Microsoft access.

    I know that it's quite easy to learn. Just take baby steps and don't
    get frustrated.

    If you _REALLLLLLLLLLLLLLLLLLLLLLLLLLY_ want to process more than 64k
    rows in Excel; you can actually use the Office Web Componenets
    Spreadsheet control; I think that it is about 60% of the functionality
    of Excel; but it actually has a limit of like ~250,000 rows.

    You'd have to do some programming (VB is fun for the whole family.. i
    mean... PERL??? wtf this is excel and access not rocket science)..


  14. #14
    Harlan Grove
    Guest

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

    [email protected] wrote...
    ....
    >I mean seriously-- with a couple of queries; a couple of macros and a
    >little bit of time-- there ins't anything in the world that you can't
    >calculate with Microsoft access.

    ....

    Gosh, with a few formulas, a couple of macros and a bit of time I could
    do anything in Excel you could do in Access *WITH* *SMALL* *DATASETS*.
    I could even think of a few things that would be much easier in Excel.
    (I already have - have you made any improvements to the queries I
    proposed for amotization tables in

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

    or http://makeashorterlink.com/?S1402436B ?)

    Next, without using Excel directly, calculate the 90th pecentile from
    the Beta distribution with alpha = 0.5 and beta = 2.5. [Though some
    versions of Excel don't do such a wonderful job with stuff like this.]


  15. #15

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

    harlan

    you're friggin crazy.
    get off your high horse.. small sums and easy math like that-- it's
    easy to do on the database side.

    and then you can centralize your logic in one place; and when you need
    to change your calculation; you can do it all in one place.. instead of
    wading through 1000 different XLS formulas.. I mean..

    GAG ME WITH A SPOON

    aren't you tired of having numbers that dont match?
    the root of the problem with Excel is that you have different formulas
    in each cell-- in Access you have a different formula for each COLUMN
    (or field.. whatever nomenclature you choose)

    I just know that there is a better way for all you guys to spend your
    workdays.

    Get this-- you can build a report and then run it each week
    or you can build a report and then rebuild it each week.

    Access allows you to go to huge datasets without a problem.
    it just takes a little bit of work to get to know queries.

    BFD, Excel was passe in 1992-- I mean-- it's time to lose the training
    wheels kids

    -aaron


  16. #16

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

    and just for the record, Access ships with MSDE.. keeping your data in
    a real database-- like MSDE means that you can grow up and use MDX when
    you're ready. MDX is the 'spreadsheet language of the database world'

    SQL Server Books Online (again-- Access ships with freeware SQL Server
    engine)
    Using WITH to Create Calculated Members mentions percentile

    And here you go; these functions (except the ones with a *) are
    supported in Analysis Services
    Excel Functions
    Microsoft=AE SQL Server=99 2000 Analysis Services supports many
    functions in the Microsoft Excel worksheet library, which is
    automatically registered if installed on the computer with Analysis
    Services. Functions not supported in this release are marked by an
    asterisk in this table.

    Acos Acosh And *Application
    Asc Asin Asinh Atan2
    Atanh AveDev Average BetaDist
    BetaInv BinomDist Ceiling ChiDist
    ChiInv ChiTest Choose Clean
    Combin Confidence Correl Cosh
    Count CountA *CountBlank *CountIf
    Covar *Creator CritBinom *DAverage
    Days360 Db Dbcs *DCount
    *DCountA Ddb Degrees DevSq
    *DGet *DMax *DMin Dollar
    *DProduct *DStDev *DStDevP *DSum
    *DVar *DVarP Even ExponDist
    Fact FDist Find FindB
    FInv Fisher FisherInv Fixed
    Floor Forecast *Frequency FTest
    Fv GammaDist GammaInv GammaLn
    GeoMean *Growth HarMean *HLookup
    HypGeomDist *Index Intercept Ipmt
    Irr IsErr IsError IsLogical
    IsNA IsNonText IsNumber Ispmt
    IsText Kurt Large *LinEst
    Ln Log Log10 *LogEst
    LogInv LogNormDist *Lookup Match
    Max *MDeterm Median Min
    *MInverse MIrr *MMult Mode
    NegBinomDist NormDist NormInv NormSDist
    NormSInv NPer Npv Odd
    Or *Parent Pearson Percentile
    PercentRank Permut Pi Pmt
    Poisson Power Ppmt Prob
    Product Proper Pv Quartile
    Radians *Rank Rate Replace
    ReplaceB Rept Roman Round
    RoundDown RoundUp RSq Search
    SearchB Sinh Skew Sln
    Slope Small Standardize StDev
    StDevP StEyx Substitute *Subtotal
    Sum *SumIf SumProduct SumSq
    SumX2MY2 SumX2PY2 SumXMY2 Syd
    Tanh TDist Text TInv
    Transpose *Trend Trim TrimMean
    TTest USDollar Var VarP
    Vdb *VLookup Weekday Weibull
    ZTest

    In other words-- you guys are roadkill since I can do all of this
    'crazy analytical math' (gag me with a spoon; you guys can't even ADD--
    you guys can't JOIN-- you can't PRINT A REPORT-- you have to email a
    10mb spreadsheets

    I can do all of this on the db server side of the equation-- so I can
    do it faster than you can do it on the desktop.. and I can do it
    against BILLIONS of records with a sub-second response time. (if you
    know what you're doing-- like I do)

    -aaron


  17. #17
    Harlan Grove
    Guest

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

    [email protected] wrote...
    >and just for the record, Access ships with MSDE.. keeping your data in
    >a real database-- like MSDE means that you can grow up and use MDX when
    >you're ready. MDX is the 'spreadsheet language of the database world'


    Good for those with Office Pro. Not useful for those with other
    versions of Office that don't come with Access. And MDX is yet another
    product, and one that few if any business users outside IT departments
    would have.


    >SQL Server Books Online (again-- Access ships with freeware SQL Server
    >engine)
    >Using WITH to Create Calculated Members mentions percentile


    You don't understand. The percentile of a Beta distribution
    corresponding to probability p given parameters alpha and beta is given
    by

    BETAINV(p,alpha,beta)

    >And here you go; these functions (except the ones with a *) are
    >supported in Analysis Services
    >Excel Functions

    ....

    Yet more software the typical users won't have!

    Winnowing the chaff,

    *Application
    *CountBlank
    *CountIf
    *Creator
    *DAverage
    *DCount
    *DCountA
    *DGet
    *DMax
    *DMin
    *DProduct
    *DStDev
    *DStDevP
    *DSum
    *DVar
    *DVarP
    *Frequency
    *Growth
    *HLookup
    *Index
    *LinEst
    *LogEst
    *Lookup
    *MDeterm
    *MInverse
    *MMult
    *Parent
    *Rank
    *SumIf
    *Trend
    *VLookup

    No big surprise the so-called database functions, {|H|V}LOOKUP,
    COUNTBLANK, COUNTIF and SUMIF aren't provided since there are already
    better ways to achieve their results through queries. Odd that MATCH is
    included since it's pointless without INDEX, which isn't included, but
    there's probably some form of arbitrary indexing provided. No big deal
    about RANK since it could be implemented via queries.

    FREQUENCY wouldn't be very much fun to implement in queries, but it
    would be doable.

    Lack of multiple regression functions would make it rather difficult to
    general linear modeling.

    Still, not bad. Now if it were free and could be used with other dbms's
    than SQLServer . . .

    >In other words-- you guys are roadkill since I can do all of this
    >'crazy analytical math' (gag me with a spoon; you guys can't even ADD--
    >you guys can't JOIN-- you can't PRINT A REPORT-- you have to email a
    >10mb spreadsheets


    And you can't pitch a deal to customers. So we still make the big
    bucks, and you're permanently parked in IT-land. Think of it this way:
    the people who hire your boss's boss know spreadsheets, not databases.

    >I can do all of this on the db server side of the equation-- so I can
    >do it faster than you can do it on the desktop.. and I can do it
    >against BILLIONS of records with a sub-second response time. (if you
    >know what you're doing-- like I do)


    You're full of it. You'd get quick responses either because your dbms
    is spitting back cached results pulled in previously run queries, or
    you're benefitting from indexing used to pull the thousands of records
    you're actually selecting from the billions of records you claim to be
    processing.

    And again you're failing to understand that few business users outside
    IT departments have access to those billion records as opposed to a few
    views (just read-only access) with a few thousand records each that
    their IT departments provide (and they were forced, kicking &
    screaming, to provide even that).

    If a user lacks rights to create even temporary tables on server-side,
    just how much can that user really do?


  18. #18
    Harlan Grove
    Guest

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

    [email protected] wrote...
    ....
    >and then you can centralize your logic in one place; and when you need
    >to change your calculation; you can do it all in one place.. instead of
    >wading through 1000 different XLS formulas.. I mean..


    You don't get it. Either the formulas and workbook templates don't
    change, only the data entered changes (and IMO manual data entry is
    slightly easier in Excel than Access, though there's something to be
    said for input masks in Access; copy & paste entry from non-XLS,
    non-tabular files is much easier in Excel than Access), in which case
    don't-reinvent-the-wheel is operative; or the formulas aren't the same,
    in which case saved previous business logic isn't useful.

    >aren't you tired of having numbers that dont match?


    And you've never come across situations in which two derived dbms
    tables that should be showing the same results differ in some records?

    Reconcilliation nightmares don't entirely disappear when one uses
    dbms's.

    >the root of the problem with Excel is that you have different formulas
    >in each cell-- in Access you have a different formula for each COLUMN
    >(or field.. whatever nomenclature you choose)


    As I mentioned before, there have been lots of software products that
    provided multidimensional database-like functionality, starting with
    Lotus Improv (actually starting with the spreadsheet VP-Planner, but
    its multidimensional database was rather limited). All were going to
    kill spreadsheets as we know them. Most are not extinct and the
    spreadsheet lives on & on. Rats outlived dinosaurs, and cockroaches
    will likely outlive rats. Feel free to consider spreadsheets the
    cockroaches of the software world.

    There's a perception that the flexibility provided by spreadsheets,
    with a single formula per cell, is worth it. Having a rough idea what
    it took to tweak individual 'cell' results in Improv (it required
    creating another dimension for exceptions, then incorporating the
    exception dimension into the formulas), I can understand why such
    programs have never sold well for general use.

    >I just know that there is a better way for all you guys to spend your
    >workdays.

    ....

    You have no idea how we spend our workdays. You believe we spend our
    time in the same report creation cesspool you do. Ain't so.


  19. #19
    Registered User
    Join Date
    07-06-2005
    Posts
    3

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

    Harlan I fully agree with you, and that was the driver that made me ask the initial question.

    Everyone has to remember that the relational paradigm in DMBS is far older than spreadsheets, and it's adn will remain an IT-Related issue for most of the cases.

    The relational model can't represent reality and we see it every day through heavy customization of business apps (i mention SAP, Oracle, Peoplesoft etc...). Customers mostly aren't happy with them, so we have to switch to another paradigm closer and closer to customers needs.

    Now i ask you a question... What if excel would support billions of records???

  20. #20

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

    And seriously:

    The relational model can't represent reality and we see it every day
    through heavy customization of business apps (i mention SAP, Oracle,
    Peoplesoft etc...). Customers mostly aren't happy with them, so we have

    to switch to another paradigm closer and closer to customers needs

    YOU ACTUALLY THINK THAT THE RELATIONAL MODEL LETS YOU DOWN BECAUSE YOU
    SPEND 10 MILLION ON A CLOSED SYSTEM WRITTEN IN C++?

    ACCESS DATA PROJECTS

    all of your enterprise level apps should be written in ADP so that you
    can make changes when you need to.. buying a closed system like SAP and
    Peoplesoft.. i mean..

    use the tools that are already on your desktop instead of buying a
    billion dollars worth of 'enterprise level apps' that are closed; more
    difficult to implement/troubleshoot

    you really think that the 'relational model' is to blame because of the
    fact that all your data is housed in data silos?

    The answer is
    ACCESSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS

    rather than pulling data around; link to it.


  21. #21
    Bob Phillips
    Guest

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


    <[email protected]> wrote in message
    news:[email protected]...

    > all of your enterprise level apps should be written in ADP so that you
    > can make changes when you need to.. buying a closed system like SAP and
    > Peoplesoft.. i mean..


    You have out-done yourself with asinine comments with this one.



  22. #22
    Harlan Grove
    Guest

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

    [email protected] wrote...
    >The relational model can't represent reality and we see it every day
    >through heavy customization of business apps (i mention SAP, Oracle,
    >Peoplesoft etc...). Customers mostly aren't happy with them, so we have
    >
    >to switch to another paradigm closer and closer to customers needs
    >
    >YOU ACTUALLY THINK THAT THE RELATIONAL MODEL LETS YOU DOWN BECAUSE YOU
    >SPEND 10 MILLION ON A CLOSED SYSTEM WRITTEN IN C++?


    ??!

    The relational model, if implemented with the full relational algebra
    and calculus as spelled out in several books by E.F. Codd and C.J. Date
    can represent any arbitrarily complex relationship between atomic data.
    Some of those relationships could require rather complex many-to-many
    relations, but theoretically it can handle them (given enough storage).

    However, in the real world we have SQL. SQL isn't really standard. SQL
    isn't orthogonal (see most of the books written by C.J. Date that
    mention SQL). SQL doesn't implement the full relational algebra or
    calculus.

    The relational model may be just what everyone needs, but SQL and
    SQL-based dbms's aren't panaceas.

    >ACCESS DATA PROJECTS
    >
    >all of your enterprise level apps should be written in ADP so that you
    >can make changes when you need to.. buying a closed system like SAP and
    >Peoplesoft.. i mean..

    ....

    If you define 'app' as report generation, perhaps.

    And yet again more ignorance. The value of PeopleSoft and SAP comes
    from the pre-built relations and (e.g., withholding taxes in payroll
    systems) from pre-filled tables bundled with the product.

    Bozos like you who like reinventing the wheel (re-entering tax tables,
    recreating standard business relations, etc.) rather than buying
    packaged solutions may prefer plain dbms's. Fortunately, bozos like you
    don't make purchasing decisions.


  23. #23
    Harlan Grove
    Guest

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

    [email protected] wrote...
    >you still haven't told me how it is that you somehow dont recreate the
    >whell 10 times a month
    >
    >YOU BUILD THE SAME SPREADSHEET EVERY WEEK


    No, you just want to believe that.

    I use the same spreadsheet templates several times a day, but only the
    data changes, and the data comes from outside my company, so the only
    electronic feed is from e-mailed PDF-to-clipboard-to-Excel. (Actually,
    e-mailed PDF to pdf2txt to awk to CSV to Excel, but why quibble?)

    The new workbooks I create each day typically involve fewer than 100
    entries, of which usually fewer than half are formulas. In other words,
    I use Excel like an overgrown calculator. If you think Access is
    anywhere near as efficient as Excel for this sort of ad hoc
    calculation, you're stupider than you've already proven.

    >i mean YOU GUYS CREATE THE SAME REPORT EVERY WEEK


    No, you create the same report every week. We do real work.

    >Excel is dead; and I hope that you Excel dorks are all kicked to the
    >curb.. you guys think that you're so good with numbers.. i mean.. WAKE
    >UP TO REALITY and either learn Crystal Reports of Microsoft Access.


    What makes you think we don't already know these other systems? What
    makes you think we don't use them when *WE* believe they're more
    appropriate? We just don't use them to the exclusion of anything else,
    which seems to be your (so-called) mindset.

    >EXCEL SHOULDN'T BE USED FOR DATA ENTRY YOU DORK


    To some extent you have a point. Excel isn't ideal, and I don't use it
    all that much except for small datasets (<100 entries) or when I have
    to enter tabular data I receive in hardcopy (depressingly still not an
    infrequent situation - we have a scanner in my department, but no OCR
    software - sigh). When possible, I use awk scripts to parse text files,
    even text files generated from PDF files.

    However, most people don't know scripting languages, so they're stuck
    using the tools *THEY* find easiest to use. The world has voted: Excel
    is easier to use for data entry. Live with it!

    >Excel doesn't store data; it stores spaghetti code and millions of
    >different functions


    If used in an undisciplined way, true. If used properly, false.

    There are a lot of people who claim programming in Assembler (or C) is
    pure evil, but Assembler (and C) can do more than anything else. They
    can be used badly, but they can also be used well. It takes a lot more
    discipline to use them when than, say, VB, but once it becomes a habit,
    they're easier to use. Same with spreadsheets vs databases for the
    things the rest of us understand are easier to do in spreadsheets. You
    don't have a clue what those things are because you only seem to know
    how to generate reports. Report generation really isn't the only thing
    people use computers to do.

    >aren't you tired of changing formulas in hundreds of different cells?


    Depends. But on the other hand I'll take formulas over stored query
    results when typos or other errors are discovered in some base table
    that require hundreds of queries to be rerun.

    >there is a better way; it is called ACCESS or CRYSTAL REPORTS.


    For generating reports, agreed. For doing real work, you're full of it.


  24. #24
    Jay Petrulis
    Guest

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



    Bob Phillips wrote:
    > <[email protected]> wrote in message
    > news:[email protected]...
    >
    > > all of your enterprise level apps should be written in ADP so that you
    > > can make changes when you need to.. buying a closed system like SAP and
    > > Peoplesoft.. i mean..

    >
    > You have out-done yourself with asinine comments with this one.


    It is worse than you think.

    Search for Aaron's posts in the database groups. It appears that even
    there he is not very capable. Even when the rants are ignored, his
    replies get shot down.

    My guess is that he is nowhere near as proficient with his stated
    software preferences as he claims.

    I don't see him doing well in public relations, either.

    Bye,
    Jay


  25. #25
    Harlan Grove
    Guest

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

    Jay Petrulis wrote...
    ....
    >I don't see him doing well in public relations, either.


    Yeah, but could Aaron and I revive Point-Counterpoint?


  26. #26
    Bob Phillips
    Guest

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

    Point - Dull thud maybe <g>

    Bob

    And that is he not you sir!

    "Harlan Grove" <[email protected]> wrote in message
    news:[email protected]...
    > Jay Petrulis wrote...
    > ...
    > >I don't see him doing well in public relations, either.

    >
    > Yeah, but could Aaron and I revive Point-Counterpoint?
    >




  27. #27

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

    are you DRUNK?
    why dont you just keep your reports in a database and then you can USE
    IT in 100 different places without troubleshooting AWK and PDF

    C is easier to use than VB? You might mean easier-- like it takes you
    less effort to write the same structures week in and week out; but VB
    is the most productive environment in the world.

    you idiot.

    Report Generation is the only thing that Excel dorks do.


  28. #28
    Harlan Grove
    Guest

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

    [email protected] wrote...
    >are you DRUNK?


    No. Are you terminally stupid? Ah, well, not terminally so.

    >why dont you just keep your reports in a database and then you can USE
    >IT in 100 different places without troubleshooting AWK and PDF


    Idiot! How many times do I need to repeat that most of the data I work
    with comes from CUSTOMERS! That is, it ain't anywhere in any of my
    company's systems other than the e-mail servers to start with, then on
    my local drives when I detach files from e-mail, and it isn't even
    possible to import it into anything (even dbms's) until it's been
    parsed into the equivalent of CSV format.

    YOU! may have all the data YOU!'re allowed to use in a nice, neat
    database. Ain't so for many, many business users. But since you've
    demonstrated that you're incapable of anyone doing any tasks that
    you've never done yourself, you're incapable of understanding this.

    You live in a dream world in which all data exists on your very own
    dbms. The rest of us inhabit the real world.

    >C is easier to use than VB? You might mean easier-- like it takes you
    >less effort to write the same structures week in and week out; but VB
    >is the most productive environment in the world.


    Not if you have an existing library of reusable code. Your same
    argument was made 20 years ago that TurboPascal was easier to learn and
    more productive than C. Where's TurboPascal now? It's morphed into
    Delphi, but it hasn't taken over the world.

    Am I saying that C is the ideal language for ad hoc application
    development? No. But IMO VB isn't ideal either. I'll admit I don't
    write much UI code, and I don't have (so don't use) VB proper (though I
    did have & use VB3 way back in the early 1990s), but I prefer Perl/Tk
    when I need a graphical interface to initiate essentially batch
    processes. Unlike you, I'll admit my preference is subjective and
    reflects a mindset based on originally learning programming under Unix.

    All I'll say for VB is that it allows mediocre programmers to make
    multimegabyte GUI applications instead of multi-KB batch applications.
    If you think that's productivity, that's your problem.

    >Report Generation is the only thing that Excel dorks do.


    No, it's apparently the only thing YOU! do (and it's becoming apparent
    it may be the only thing you're capable of doing outside the bathroom).


  29. #29

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

    Use a program called Data Junction


  30. #30
    Harlan Grove
    Guest

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

    [email protected] wrote...
    >it's not complex. the relational model is the simplest thing in the
    >world.

    ....

    If one thinks in terms of table-based relations, perhaps. Most people
    don't think that way, so wrong in general.

    >The value of peoplesoft and sap is that it is DATABASE DRIVEN.
    >These apps dont run on spreadsheets.


    It's not that they're essentially database applications, their main
    value comes from the relations and tables they provide out-of-the-box.
    Now, the things most companies do with them are best done using
    databases, so no prizes for PeopleSoft (or Oracle) or SAP that they're
    implemented using dbms's.

    >Why would you use crayons to draw on pretty reports?
    >You're doing the same thing by bringing data into Excel and then you
    >sit there-- and you have a copy of the same formula in 100 different
    >places.


    I have thousands of archived e-mails with a lot of common text, too.
    Some redundancy isn't worth the effort to eliminate.

    One big advantage of that redundancy is that a given workbook can be
    opened and used on any PC or Mac or Linux or BSD box running Excel or
    OpenOffice Calc (OK, OOo Calc chokes on some XL functions and many XL
    array formulas). I don't need to haul around terabytes of dbms store,
    and I don't need a connection to the dbms. And I can write more
    formulas if needed, so XLS files are much more useful than static SNP
    or PDF files.

    Since all you do is create reports, I guess you can't even imagine
    needing different perspectives on the same data.

    >It is impossible to check all of these formulas and have any confidence
    >in your numbers.


    Maybe it's impossible for you.

    It's a matter of scale. Database queries have formulas, especially ones
    using multidimensional analysis and OLAP facilities. Are those formulas
    impossible to verify?

    A single spreadsheet formula is also easy to verify, and (I may lose
    you here because this will take some wit to understand) if one uses
    standard software engineering techniques, it's possible to print
    formula listings from spreadsheets (R1C1 addressing much better than A1
    for this) to text files then use textual pattern matching to locate
    anomalies. I've been doing this for almost 2 decades.

    Now most spreadsheet users wouldn't recognize a regular expression if
    it danced ***** in front of them (stealing shamelessly from J K
    Rowlings), but that doesn't imply it's not possible for ANY spreadsheet
    user (or developer) to verify their models. Your argument is a vacuous
    as saying that since it's difficult to fully verify source code in
    large software systems (e.g., dbms's), you can't have any confidence in
    the results generated by such systems. Untrue. There's empirical
    confidence. Has it worked well in the past? Does it handle test cases
    correctly? But you wouldn't understand that unless some dbms spit it
    out at you in a report simple enough for you to understand.

    >I can do anything with SQL Server that you can do with Excel. And I
    >can do it faster, against more than 65k records.


    OK, let's see whether (i.e., prove) you're talking out your backside.

    Use a nice outside data source like the U.S. Bureau of Labor
    Statistics.

    http://data.bls.gov/cgi-bin/surveymost?ce

    Pull 'Total Private Average Weekly Earnings, 1982 Dollars - Seasonally
    Adjusted - CES0500000051' by checking its check box and clicking on the
    'Retrieve Data' button at the bottom of the web form.

    On the next web form, click the 'More Formatting Options' link. In the
    next form, choose 'Column Format' from the list under 'Select view of
    the data' and 'Text' under 'Output type' (choose whatever plain text
    format you'd prefer - I use comma delimited). Under 'Select the time
    frame for your data', specify years 1985 to 2005. Then click the
    'Retrieve Data' button. This should give you a table with field names
    and 245 records.

    Pull that data into your dbms, but let us know in detail all the steps
    you need to do that. The following steps are all that's needed to pull
    it into Excel.

    1. Select the entire table (field names to bottommost row excluding the
    '(p)' at the end of the last field in the bottommost row) and copy it
    to the clipboard.
    2. Switch to Excel and run the menu command Edit > Paste Special, Text.
    3. Immediately after pasting, run the menu command Data > Text to
    Columns, choose Delimited and click the Next button, then check 'Comma'
    as a delimiter character and click the 'Finish' button.

    The data is now ready to use.

    Let's do something moderately tricky - pull the largest and average
    relative (percentage) increases and decreases in rolling 3 month
    average weekly wages. (Yes, you could pull the rolling 3-month figures
    from the BLS database, but let's compare Excel vs your database.)

    I imported the data into a new worksheet beginning in cell A1, so after
    parsing A1 contains 'Series ID'. One approach in Excel.

    1. Enter the formula =AVERAGE(D2:D4) in cell E4, then double click on
    the Fill Handle to fill it down into E5:E246.
    2. Enter the formula =(E5-E4)/E4 in cell F5, then double click on the
    Fill Handle to fill it down into F6:F246.
    3. Enter the array formula =MAX(IF(F5:F246>0,F5:F246)) in cell H1 to
    get the largest rolling 3 month percentage increase.
    4. Enter the array formula =MIN(IF(F5:F246<0,F5:F246)) in cell H2 to
    get the largest rolling 3 month percentage decrease.
    5. Enter the array formula =AVERAGE(IF(F5:F246>0,F5:F246)) in cell H3
    to get the average rolling 3 month percentage increase.
    6. Enter the array formula =AVERAGE(IF(F5:F246<0,F5:F246)) in cell H3
    to get the average rolling 3 month percentage decrease.

    For extra points, enter the formula =COUNTIF($F$5:$F$246,H1) in cell I1
    and fill it down into cell I2 to get the number of rolling 3 month %
    changes matching the largest increase and decrease, respectively, and
    enter =INDEX($B$2:$B$246&" "&$C$2:$C$246,MATCH(H1,$F$2:$F$246,0)) in
    cell J1 and fill it down into cell J2 to get the year and month
    corresponding to the topmost (earliest) periods experiencing the
    largest % increase or decrease, respectively.

    Now let's make it trickier. Enter 3 in cell G1. Clear columns E and F.
    Enter the following formula in E2.

    =IF(ROWS(E$2:E2)<$G$1,"",AVERAGE(OFFSET(D2,0,0,-$G$1,1)))

    Double click the Fill Handle to fill it down into E3:E246. Enter the
    following formula in cell F2.

    =IF(COUNT(E1:E2)=2,(E2-E1)/E1,"")

    Double click the Fill Handle to fill it down into F3:F246. The cells in
    H1:J4 should give the same results as they had before. Now change G1 to
    6. The formulas in J1:J4 now return rolling 6 month results.

    Try making the number of periods over which to average a simple query
    parameter.

    >AND YES, I DO MAKE PURCHASING DECISIONS.


    What kind of TP, what to have for lunch, . . .

    >You're the idiots that are ignorant.
    >
    >APPS are:
    >a) data entry
    >b) report generation


    Yup. You've confirmed the narrowness of your worldview. I'd suspected
    it all along, but it's good to have proof (that is, if someone of your,
    er, intellect is competent to make accurate self-assessments).

    >Excel can't do either.. so take your spreadsheet and screw yourself


    Difference of opinion on (a). What's easiest for manually keying data
    is purely subjective. If there would be several users updating the same
    file at the same time, then obviously databases would be better.
    However, that's not the sort of data entry I do.

    As for anything other than data entry or report generation, since your
    wee tiny brain seems incapable of imagining any such thing, no point
    sayng anything more than YOU'RE TOO STUPID TO UNDERSTAND THEM.


  31. #31

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

    just because it comes from CUSTOMERS doesn't mean it wont fit into a
    database.

    I have a quite elegant XLS datamart.. it automates importing
    information from spreadsheets-- any shape that you choose-- into a
    database.

    it's not rocket science.

    I'm just tired of wading through 100 spreadsheets to find a number.

    -aaron


  32. #32
    Harlan Grove
    Guest

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

    [email protected] wrote...
    >just because it comes from CUSTOMERS doesn't mean it wont fit into a
    >database.


    True. But when it comes from customers, it's not in one's own database
    to start with. It's a question of where to enter it initially AND how
    long to retain it. If it's needed quickly but only for a single quote,
    it's pointless to bother entering it into a database.

    >I have a quite elegant XLS datamart.. it automates importing
    >information from spreadsheets-- any shape that you choose-- into a
    >database.


    Goody. Does it automate importing from e-mails (HTML and plain text),
    PDF files, bitmapped fax images?

    Not all outside data is delivered in spreadsheet files.


  33. #33

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

    yeah you're crazy man
    so keep it in spreadsheets.. that helps lol

    i dont have any clue what you're talking about.. legal restrictions on
    shopping cart information? i mean for real


  34. #34
    Harlan Grove
    Guest

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

    [email protected] wrote...
    ....
    >i dont have any clue what you're talking about..


    Finally! A wee, tiny ray of light. You may want to generalize that
    'don't have a clue' self-assessment.

    > . . . legal restrictions on shopping cart information? i mean for real


    And if it were shopping cart information, no big deal.

    No, I mean nonpublic information that we're not sent until we send our
    customers signed nondisclosure agreements. I realize someone in your,
    er, position may not understand what this means, and I doubt it'd be
    possible to explain it so you could understand.


  35. #35

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

    screw you kid.. go and play with your spreadsheets.

    i'm sorry that your job is SOOOOOOOOOOOOOOOOOOO hard that you HAVE TO
    USE EXCEL.

    Atms dont use Excel. Amazon.com doesnt run on Excel.
    Why do you think that your math is so hard?

    Excel-- Word-- decentralized documents are a PITA since you have to go
    and change things in 100 differet places. It is better, easier and
    more productive longterm to store your DATA in a DATABASE and if you're
    reporting on DATABASES it is easier to use Crystal Reports or Access to
    make reports than Excel


  36. #36
    Harlan Grove
    Guest

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

    [email protected] wrote...
    ....
    >Atms dont use Excel. Amazon.com doesnt run on Excel.
    >Why do you think that your math is so hard?


    ATMs add and subtract. Not very difficult. Amazon.com's web interface
    doesn't run on Excel, but I wonder what their financial reporting
    deparment uses to generate the figures in their quarterly and annual
    reports?

    So I'll conclude from this you have no clue how to respond to my
    challenge in

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

    >Excel-- Word-- decentralized documents are a PITA since you have to go
    >and change things in 100 differet places. It is better, easier and
    >more productive longterm to store your DATA in a DATABASE and if you're
    >reporting on DATABASES it is easier to use Crystal Reports or Access to
    >make reports than Excel


    Excel, Word and decentralization are FLEXIBLE. They work when the
    network is fubar. They allow me to work offline and bring work home on
    a disk to use my home PC. They allow me to share files with people
    outside my company who'd better not have access to my company's
    database.

    It's clear you have no clue about real world data sharing with people
    outside your company.

    Now *IF* one needs to generate reports, then I agree Access and other
    reporting packages are usually better than Excel. Small, niggling
    little points to the contrary would include people who need to generate
    reports but who don't have Access or any report generation software but
    do have Excel. There's also the occasional report that's nothing more
    than a summary of information from hardcopy sources.

    Then there are all the other Excel users who aren't getting paid to
    produce reports but are getting paid to produce analyses. You wouldn't
    understand that. It'd require thinking.


  37. #37

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

    ALL MATH IS A SIMPLE ADD AND SUBTRACT

    get off your high horse; your math isn't 'TOO HARD' for a database and
    you will be roadkill if you dont expand your horizons

    it's time that beancounters are held to the same standard as
    developers.. thats' all you dorks do is develop Excel CRAP but you guys
    dont push yourselves anywhere near as hard as us IT people

    i urge you to learn Crystal or Access.

    Excel was passe in 1995.


  38. #38
    Harlan Grove
    Guest

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

    [email protected] wrote...
    >ALL MATH IS A SIMPLE ADD AND SUBTRACT


    So needless to say you have no clue what EXP, LN and IRR functions do,
    just to name 3 that do a bit more than add and subtract.

    All math may reduce to arithmetic when calculating cardinal and ordinal
    results, but the magic is in the algorithms, data structures and
    referencing mechanisms. In databases, there's only one data structure:
    tables; and there are only two referencing mechanisms: SELECT queries
    with and without joins.

    >get off your high horse; your math isn't 'TOO HARD' for a database and
    >you will be roadkill if you dont expand your horizons


    The calculations take place in the same CPU for Excel and Access, so
    they shouldn't differ in 'hardness'.

    The problem with databases is the relative inflexibility of tables as
    data structures and the awkwardness of queries as the only referencing
    mechanism (unless you assume the existence of tons of additional
    software to which few if any non-IT business users have access).

    We've already seen that you can't come up with anything better for
    simple amortization tables than the queries *I* proposed in

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

    which proves nothing more than you're clueless about how to make
    amortization tables. The necessary queries are a joke compared to the
    simplicity of how to do this in Excel *FROM* *SCRATCH*, to wit,

    1. Define R with the periodic interest rate, N as the number of periods
    over which loan payments would be made, A as the initial loan amount,
    and P as =PMT(R,N,-A). Since you'd also need to specify these in a
    database approach, same data entry requirements so far in Excel and
    Access.

    2. Enter the following column labels in A1:E1.

    A1: Month Number
    B1: Prior Balance
    C1: Interest
    D1: Principal
    E1: Current Balance

    3. Fill in the first record in A2:E2.

    A2: 0
    B2: 0
    C2: 0
    D2: 0
    E2: =A

    4. Fill in the template formulas for the second record in A3:E3.

    A3: =A2+1
    B3: =E2
    C3: =R*B3
    D3: =P-C3
    E3: =B3-D3

    5. Complete the table by selecting A3:E3 and filling down into A4:E362.

    Real hard. Oh so much more difficult than 5 obscure SQL queries,
    especially since calculating interest portions directly in Excel (prior
    balance times periodic rate) is so much more difficult than calculating
    the principal portion directly in Access (periodic payment divided by
    one plus periodic interest rate raised to N less current month number
    plus 1).

    You are so clueless.

    Then, just speaking for myself, since I'm having to spoon-feed you the
    queries needed to perform the tasks *YOU* say are so easy to do in
    databases but *YOU* have proven to be incapable of providing, looks
    like I have nothing to fear from you if every company I could work for
    were to ban Excel tomorrow and mandate Access use only. The difference
    between us is that I know how to use the tools you claim are the only
    ones anyone needs to use while you can't figure out the other tools I
    use.

    Who's roadkill?

    Well, neither of us. I'll have a job because I'm so clever, and you'll
    have a job because database grunts, like septic tank cleaners, will
    always be in some demand.


  39. #39

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

    and databases have a LOT more than select, for starters there is
    UPDATE, DELETE, INSERT..

    the WITH clause (of MDX and CTEs)

    I can figure out enough about Excel to say that I'm tired of keeping my
    important numbers in 300 different places.

    I keep my calculations in a database; and then i can spit them out into
    Excel, Crystal, ASP.. i can display numbers in 100 different formats.
    10,000 different formats for christ sakes.

    And you idiots are still emailign around 100mb XLS files.. I mean..
    WAKE UP AND SMELL THE COFFEE; EXCEL WAS PASSE IN 1995.


  40. #40

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

    database grunts.. ****

    i just think that it's funny that there isn't certification for Excel
    dorks.. i mean.. its obvious that you all collectively aren't smart
    enough to have a little bit of standardization.

    i think that there should be 10 different flavors of excel
    certification; because everywhere I've gone you excel dorks dont know
    what the hell you're doing


  41. #41
    Harlan Grove
    Guest

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

    [email protected] wrote...
    ....
    >unless you really think that mortgage companies use EXCEL to calculate
    >this stuff.. I'd STFU


    Mortgage companies probably use decades-old COBOL programs for this,
    accessing data via ISM-VSAM. That's for the back office stuff. For the
    point-of-sale tables mortgage brokers give their customers, YES, they
    do use spreadsheets to generate sample amortization tables.

    How ignorant are you?!

    >THIS ARMY HAS TOO MANY SOLDIERS AND NOT ENOUGH TANKS
    >
    >Databases = Tanks
    >Excel = Soldiers

    ....

    Your metaphor.

    Used by someone with an ounce of sense, these would be US tanks against
    Iraqi soldiers. Used by you, these would be soviet tanks against
    Afghani soldiers. Do you know enough recent history to know the
    difference?

    Continuing this metaphor, tanks ain't all that useful in jungles,
    mountains, amphibious operations, air drops. They're really only useful
    in flat, open country without mud or with roads. Soldiers are useful
    and needed everywhere.

    Talk about rhetorical self-immolation. Do you ever stop to think before
    writing this drivel? That begs a broader question: do you ever think?!


  42. #42
    Harlan Grove
    Guest

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

    [email protected] wrote...
    >and databases have a LOT more than select, for starters there is
    >UPDATE, DELETE, INSERT..


    *NOT* for referencing existing data they don't. These queries all
    CHANGE tables, only SELECT fetches data from tables.

    >the WITH clause (of MDX and CTEs)


    Irrelevant - again you're assuming the existence of additional
    software. If I assumed a full SAS system interfaced with a SQL
    database, could I then claim all SAS data step code was just part of
    the database interface? Well, if I were you I guess I could, but
    thinking people would have a different opinion.


  43. #43
    Harlan Grove
    Guest

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

    [email protected] wrote...
    >select is about 100 times more powerful than spreadsheets.


    If you want to select data pretty much as it comes from one table or
    several joined tables, perhaps. If you want to correspond values from
    some field but from different records, it gets difficult very quickly.

    Consider another simple example: 5 point moving averages from a table
    in which the first field is observation number and the second field is
    observation value. In Excel, real difficult: sort the table on
    observation number in ascending order, then starting with the 5th
    observation, average the first 5 values up to the current record's (5th
    observation's) value, then fill that formula down for the remaining
    records.

    What sort of gyrations does something this simple require in a SELECT
    query? Pure SQL, no assuming additional software like Analysis
    Services.

    >i mean.. storing data in one place and then pointing to it-- it is a
    >lot easier than pulling around all your data and emailling all your
    >data to your clients


    Unless one's IT department won't provide access to anyone outside the
    company, in which case, oh great genius, how does one share data with
    customers/clients? Hint: not by an ODBC or web database feed.

    >databases make the world go around


    Excretion is a necessary part of living . . . and an unavoidable aspect
    of your postings.


  44. #44
    Harlan Grove
    Guest

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

    [email protected] wrote...
    >pulling stuff from multiple tables is EASY
    >that is why they make temp tables, derived tables; views.. i mean..
    >COME ON


    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!

    You've suggested how to handle moving averages a while ago. Something
    like

    SELECT (D1.ObsVal+D2.ObsVal+D3.ObsVal+D4.ObsVal+D5.ObsVal)/5 AS MA5PT
    FROM (((D AS D1 INNER JOIN D AS D2 ON D1.ObsNum=D2.ObsNum+1)
    INNER JOIN D AS D3 ON D2.ObsNum=D3.ObsNum+1)
    INNER JOIN D AS D4 ON D3.ObsNum=D4.ObsNum+1)
    INNER JOIN D AS D5 ON D4.ObsNum=D5.ObsNum+1;

    Maybe that seems clear to you. If there were column headings in A1:B1
    and the first record in A2:B2, the Excel formula to calculate this
    would be

    C6:
    =AVERAGE(B2:B6)

    Double click the fill handle after entering this formula, and it's
    done.

    But the real strength of flexible referencing (a la Excel) comes when
    you want to vary the number of points in the moving averages. Enter the
    formula

    C2:
    =IF(ROW()>C$1,AVERAGE(OFFSET(B2,1-C$1,0,C$1,1)),"")

    and fill it down in column C. Now enter 5 to get 5-point moving
    averages, 3 to get 3 point moving averages, 10 to get 10-point moving
    averages, etc. You'd need different SELECT queries for each of these,
    and you're more deluded than usual if you believe anyone other than
    yourself would find the necessary queries anything other than tedious
    and repetitious.

    >if i want to provide REPORTS to customers I would use the crappiest
    >software ever; Adobe Acrobat. I would send them a copy of the REPORT
    >instead of a copy of the data.


    That's normal practice. It prevents later disputes or MUCHO work trying
    to figure out how they derived numbers from raw data. You're just
    putting your own ignorance on display again.

    >that is my big beef with Excel; it isn't designed for REPORTING but you
    >numnuts use it for reporting all the time


    No, *YOU* or people *YOU* work with use it for reporting. Others may
    also, but I don't.

    >you can't export an Excel sheet into a REPORT file and email it around.
    >what happens when you DONT want people to change your numbers?
    >what happens when you DONT want to email a 100mb spreadsheet?


    You send PDF files! Simple, ain't it?!

    >Databases trump spreadsheets any day of the week.


    For doing mindnumbingly repetitive stuff, which is all that you're
    likely allowed to do, you're right.

    >1) sorting and averaging; that stuff is OBVIOUSLY easier in a database.
    >For starters; when you change the SORT ORDER it doesn't break your
    >other reports. Do you seriously propose having multiple copies of the
    >DATA in a spreadsheets; sorted in different order? LOL


    Sorting is purely subjective. Some like you may prefer query predicates
    like 'ORDER BY FOO ASC', others would prefer specifying columns via
    dialogs. Agreed that Excel's limitation of just 3 sort columns is
    absurd.

    >2) starting with the 5th.. come on-- gag me with a spoon.. select top 5
    >as a subquery.. that is easy.


    You'd need to repeatedly subquery the top 5. If you have 100 records,
    you have 96 5-point moving averages.

    >3) instead of COPYING FORMULAS all over the world; you can have ONE
    >FORMULA in ONE PLACE. I mean.. what happens when you change the
    >formula?


    Except as pointed out above, you could have one Excel formula capable
    of generating N-point moving averages for any positive integer N (well,
    up to 65535). You need separate queries for each N in rdbms's.

    >(hint, it is easier to change a formula in one place than in 20,000
    >rows on 20 different worksheets).


    Hint: if you know how to use spreadsheets well, it takes *ONE* formula
    to do what it'd take thousands of different queries to do.


  45. #45

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

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

    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

    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

    -aaron


  46. #46

    Re: 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


  47. #47
    Harlan Grove
    Guest

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

    [email protected] 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.


  48. #48
    Harlan Grove
    Guest

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

    [email protected] 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!


  49. #49
    Jamie Collins
    Guest

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


    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.

    --


  50. #50
    Harlan Grove
    Guest

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

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


  51. #51
    Jamie Collins
    Guest

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

    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.

    --


  52. #52
    Harlan Grove
    Guest

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

    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.


  53. #53

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

    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.


  54. #54

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

    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.


  55. #55

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

    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


  56. #56
    Harlan Grove
    Guest

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

    [email protected] 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.


  57. #57
    Harlan Grove
    Guest

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

    [email protected] 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.


  58. #58

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

    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


  59. #59

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

    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


  60. #60
    Jamie Collins
    Guest

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


    [email protected] 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 ....


  61. #61
    Jamie Collins
    Guest

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


    [email protected] 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.

    --


  62. #62

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

    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


  63. #63
    Harlan Grove
    Guest

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

    [email protected] 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?


  64. #64
    Harlan Grove
    Guest

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

    [email protected] 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!


  65. #65
    Harlan Grove
    Guest

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

    [email protected] 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.


  66. #66
    Harlan Grove
    Guest

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

    [email protected] 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.


  67. #67
    Jamie Collins
    Guest

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


    [email protected] 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.


  68. #68

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

    that is not true, Jamie.

    MSDE 2.0 has no real hard governor; I believe.

    -Aaron


  69. #69

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

    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.


  70. #70

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

    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.


  71. #71
    Harlan Grove
    Guest

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

    [email protected] 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.


  72. #72
    Harlan Grove
    Guest

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

    [email protected] 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.


  73. #73

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

    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.


  74. #74
    Sum Yung Guy
    Guest

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

    <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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.



  75. #75
    Jay Petrulis
    Guest

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


    Sum Yung Guy wrote:
    > <[email protected]> wrote in message
    > news:[email protected]...
    > >
    > > 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


  76. #76
    Jamie Collins
    Guest

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


    [email protected] wrote:
    > that is not true, Jamie.
    >
    > MSDE 2.0 has no real hard governor; I believe.


    I told it as stright as I could: after five concurrent users MSDE
    degrades gracefully but repidly.

    You are correct not to take my word for it; I suggest you google for

    MSDE "five users" OR "5 users"

    Jamie.

    --


  77. #77
    Jamie Collins
    Guest

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


    [email protected] wrote:
    > MDB is free; but still crap-- much like Excel.


    How dare you!! Excel is *NOT* free!

    > I've been to 30 companies
    > over the past 7 years


    Kicked from pillar to post ... (sorry old bean, I could resist).

    Jamie.

    --


  78. #78

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

    MDB/ADP is free, Access runtime.. Excel doesnt have a runtime, does it
    lol

    I dont believe that the governor is quite as strong as you thought. In
    MSDE 1, it was a very rigid limit-- in MSDE 2 (SQL 2000) it is a lot
    more relaxed.

    sum young, go and play with your spreadsheets little kid.. access
    trumps excel any day of the week. it is better to place your bets on
    SQL Server than MDB.

    woud you rather make a report once and run it with different
    parameters; or cut and paste 10,000 rows around?

    i mean seirously


  79. #79
    Harlan Grove
    Guest

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

    [email protected] wrote...
    >MDB/ADP is free, Access runtime.. Excel doesnt have a runtime, does it

    ....

    And you can create MDB or ADP databases using just the Access runtime?

    >woud you rather make a report once and run it with different
    >parameters; or cut and paste 10,000 rows around?


    If one were making reports . . . but is making reports the only thing
    you believe businesses do?


  80. #80

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

    yes, making reports is all that you idiots do.

    but you guys dont even know how to do it.

    lol


  81. #81

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

    what do you do, enter data into spreadsheets?

    what a waste of time... i mean; there is no REAL validation in Excel
    multiple users can't use Excel at the same time

    what a joke.. i just cant' believe that you guys still actually use a
    program that was passe in 1995.

    -aaron


  82. #82
    Harlan Grove
    Guest

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

    [email protected] wrote...
    >yes, making reports is all that you idiots do.


    How little you know . . . or understand.


  83. #83
    Harlan Grove
    Guest

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

    [email protected] wrote...
    ....
    >i forgot.. you do all this complex math.. stuff that is WAY too complex
    >for a database lol


    OK, if it's so easy to do general math in databases, show us, Oh great
    sage, how to generate all permutations of, say, 8 distinct tokens using
    SQL queries.

    Or show us how to calculate fitted coefficients of polynomial
    regression curves.

    Or how to calculate the FFT of time series data.

    >it's time to wake up kids.. Excel is a total PITA for many reasons:
    >
    >a) 65k row limit


    Only if you're misusing Excel as a database. There *are* things Excel
    does poorly. Excel makes a much worse data store than databases. As
    I've stated before, if you're using more than a few thousand rows, you
    shouldn't be using Excel. If anyone needs data tables spanning more
    than 65,535 records (yes, 2^16-1) *and* one doesn't have a database,
    those tables should be stored in plain text files, and Excel should
    fetch the data it needs from those files using MSFT Query or
    SQL.REQUEST and the ODBC driver for text files.

    >b) only single user functionality


    Depending on what one's doing, that may be what's wanted & needed. It
    *IS* possible for multiple users to access *closed* XLS files as dumb
    data stores. If one needs to perform calculations, it's not unlikely
    different users would want to perform different calculations, so the
    need for multiple user functionality isn't obvious. At least not when
    using spreadsheets for what they're best at doing.

    There's also the quibble that separate memory images on different
    users' PCs of the same XLT or XLS file isn't all that much different
    than separate memory images on different users' PCs of the same
    temporary database tables.

    >c) lack of parameters


    Here's you're demonstrating your ignorance. Well-designed spreadsheets
    present users with screens (worksheets or user forms, aka dialogs)
    prompting for parameters. There's also defined names and values loaded
    from environment variables via Auto_Open macros or Workbook_Open
    events.

    >d) inability to have REAL data validation


    As in Data > Validation, agreed - that feature is WEAK! However,
    there's always the old fashioned way - allow any users entries but use
    formulas to validate those entries. A few defined names referring to
    formulas that check for valid entries coupled with Change, Calculate
    and Deactivate event handlers is all that's needed. It may require more
    programming effort than database validation, but 'inability' is
    incorrect.

    >e) it requires that you have formulas in hundreds of different cells


    So? That's how spreadsheets work. It's not all that difficult to check
    that all formulas in a given range have the same formula 'template',
    i.e., that they're identical in R1C1 referencing mode. Formulas also
    allow auditors to validate how every value in spreadsheet files other
    than constants is produced.

    >f) no real printing functionality


    Here you must mean reporting functionality with grouping and section
    breaks. Agreed. Excel isn't ideal as a reporting tool. However, Excel
    isn't used exclusively to generate reports even if your wee tiny brain
    can't grasp what these other tasks might be.

    >g) no real exporting functionality (Microsoft Document Imaging doesnt
    >count since they have changed standards after MDI into their 'real PDF
    >killer')


    See (f).

    >h) inability to reuse your applications.. you have to recopy your
    >spreadsheets

    ....

    Depends on what you mean by reuse. The average Excel user may reinvent
    the wheel over and over again, but more advanced Excel users have
    libraries of Excel formulas and data structures that they can reuse
    (yes, by copy & paste). There are also XLT template files. You may
    consider XLS files generated from XLT files as multiple copies, but
    sensible people would consider them necessarily multiple instances of
    the same thing, in much the same way different programs would have
    their own instances of object classes.

    It's also possible to design Excel models to use shared, read-only XLS
    files as black box back ends in conjunction with multiple instances of
    template front end workbooks. It takes some nontrivial event
    programming, but it's not impossible.

    >You guys are a dime a dozen; you dont do real math.

    ....

    You do? Adding or subtracting billions of numbers is math, but it isn't
    sophisticated, only tediously repetitious.

    Have you ever generated descriptive statistics? Do you even know what
    descriptive statistics are? Do you know how to model default or call
    risk on corporate bond portfolios? Do you know how to perform what-if
    analysis on project NPV using simulated variable factor costs and
    market demand?

    Or do you just know how to build queries like

    SELECT *
    FROM WhatAaronUnderstands;

    and work with NULL results?


  84. #84
    Harlan Grove
    Guest

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

    [email protected] wrote...
    >listen dude.. the problem with Excel is that you DO have 10 formulas
    >per calculation.


    I didn't mention formulas per calculation. Sometimes that's the case,
    but there are some types of analysis that require a lot of calculations
    to return a given result. Linear regression is one such type. One
    formula on N+K entries necessarily performs O(N^3+K) underlying
    calculations.

    >it is much more efficient to have these as set based operations--
    >instead of having hundreds and hundreds of lines of unnneeded formulas.


    If set operations would work. You don't get it about order-dependent
    and recursive algorithms. There are things set based operations can't
    do without a LOT of contortions. Generally, it's easier to make
    procedural and functional languages provide set based operations than
    to make set oriented languages like SQL provide order-dependent and
    recursive referencing.

    If you don't believe me, go on and write a permutation generator in SQL.


  85. #85
    Harlan Grove
    Guest

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

    [email protected] wrote...
    >hahahah yeah how funny.. you ***** about having to recreate the wheel
    >WHEN YOU MAKE THE EXACT SAME SPREADSHEET 3 TIMES PER WEEK WITH
    >DIFFERENT DATA

    ....

    How little you know (or are capable of learning). Are all of your
    tables completely normalized? No duplicate entries anywhere? Do you
    know the relative cost of eliminating *ALL* duplication vs allowing a
    little bit of duplication?

    If you mean I use workbooks based on the same template (all the labels
    and formulas but no data), in which other people have entered different
    customers' data then saved multiple different workbooks with different
    customer's data in each, then you're right. But, since you seem
    incapable of understanding this, that's analagous to running the same
    parametrized query with different parameter values. The workbooks
    *SHOULD* *BE* exactly the same except for the entered data AND the
    formula results.

    >all im saying is that you LAWYERS and 'ANALYSTS (GAG)' could be more
    >efficient if you built a reliable, scalable solution from the ground
    >up.

    ....

    How? (Rhetorical question, the answer is obviously 'No') Do you have
    any idea how securities lawyers create the filings necessary for equity
    or debt offerings? Do you believe every single client of theirs fits
    into a single cookie-cutter model? Same for financial analysts.

    Different clients have different characteristics. Maybe a single,
    highly parametrized approach could be developed, costing $ millions,
    but lawyers are smarter than you. They don't waste either their time or
    money on pointless generalization when tools exist to create what they
    need in hours rather than months. Even if they have to recreate the
    hours of work for each customer, that's still cheaper than building
    general systems.

    >im talking about MSDE/SQL Server-- im talking about letting you guys
    >actually DO SOMETHING instead of spinning your wheels recreating the
    >same spreadsheet 3 times per week

    ....

    To repeat yet again, for anyone who works for a company that doesn't
    use SQL Server and gives most of their employees Office Standard rather
    than Office Professional, your ranting is completely irrelevant. Those
    people won't have SQL Server and they won't have Access. Some other
    people may have Access but not SQL Server, and unless their IT
    departments either installed MSDE for them or give them Office CDs
    (both are extremely unlikely), those other people can't do squat with
    MSDE or SQL Server.

    You've deluded yourself into thinking that most business PC users have
    access to these software packages. WRONG!

    >every software in the world can interface with Access.. it's called
    >ODBC.. and it friggin ROCKS. Excel is just a poor repository for data.


    Yes, but without MSDE or SQL Server, Access only provides MDB
    databases, no? And what have you said about MDB databases? When are you
    gonna pull your head out?

    >It's not all about off the shelf products. Those never work like you
    >need them to.

    ....

    Maybe not exactly like you need them to, but close enough that they're
    ready to use AT MUCH LOWER COST than screwing around reinventing their
    functionality in an rdbms.

    >what i still dont understand.. mr Harlan KNOW IT ALL
    >is WHAT IS THE OP SUPPOSED TO DO TO GET AROUND THE 64K LIMIT?

    ....

    As I've said before, use a database. For anyone who need to manage
    thousands or records of data, spreadsheets aren't the best tool to use.
    However, you're the one claiming databases can do EVERYTHING
    spreadsheets can and more efficiently. There you're dead wrong. Large
    amounts of data with a few simple calculations (such as the ATMs you
    keep bringing up), databases are best. Small amounts of data with lots
    or complex calculations, spreadsheets are much better than databases
    (though there may be even better software to use, but few people would
    have such alternatives or know how to use them). Large amounts of data
    and lots of complex calculations, mainframes running SAS or SPSS would
    probably be best, and comparable stats packages on PC if no access to
    mainframes.

    >it's the difference between using a bicycle vs a 4x4. Databases can go
    >ANYWHERE and bicycles CAN'T GO ANYWHERE FAST ENOUGH

    ....

    Nice analogy. I suppose that makes Oracle the analog to freight trains
    and DB/2 supertankers and container ships.

    If you need to travel long distances carrying lots of stuff (and you
    don't need to maneuver much), you have access to more power. On the
    other hand, if you don't have much to carry and you want to move
    between the trees in a thick forest or travel through narrow streets
    with lots of pedestrians, the bike would be the better vehicle. Not
    everyone needs to carry tons of data to go where they want to go.

    >I can automate you out of a job in an HOUR


    Go on. Spend and hour and then show us all how a permutation generator
    can be implemented in an rdbms.

    Pure BS! You know nothing except a little bit about a handful of
    different databases. You seem to think that's more important than
    knowing how to sell product or manage people. If you could ever pull
    your head out of your backside, the fresh air might be enough to allow
    your brain to begin functioning.

    >Excel is a total waste of time; the formulas aren't powerful enough--
    >it is a dead end street.. and it's basically impossible to automate.


    No, it's only impossible for *YOU* to automate because you're incapable
    of learning how to use it efficiently.

    >A HUNDRED BILLION PAGES OF EXCEL VBA CANT BE WRONG CAN IT?
    >
    >actually.. yes-- it can be wrong. go ahead and record your macros--
    >rely on a house of cards if you want.

    ....

    And no one has ever made any mistakes using databases. ??!

    >i just know from first hand experience.. that there are TOO MANY
    >INDIANS AND NOT ENOUGH CHIEFS. Too many soldiers and not enough tanks.


    You don't understand your place in the grand scheme of things. Those of
    use with jobs that involve daily customer interaction, we're maybe the
    infantry. Your kind are rear area latrine diggers, not tank crew.
    Still, your kind performs necessary and useful tasks, but the average
    infantry soldier would prefer a collapsible shovel that he can carry
    along with weapon, ammunition and rations to trying to carry a backhoe
    on his back, and driving around in a backhow makes one more of a target
    than a soldier.

    >Learn to move data around on a schedule.. WITHOUT WRITING A SINGLE LINE
    >OF CODE

    ....

    And how does one do that if most of the data one needs comes from
    customers via e-mail?

    >YOUR MATH IS NOT TOO COMPLEX FOR DATABASES JACKASS


    If only you could PROVE that. All you seem to be able to do is just SAY
    databases can do things I've already provided detailed instructions for
    doing in Excel. Those instructions are brief because the tasks are so
    simple to do in Excel. If they're so simple to do in Excel, wouldn't
    they be even simpler to do in databases? That so, EVEN YOU could figure
    out how to do them them post the instructions here in order to show me
    how stupid I am.

    I won't hold my breath waiting for you to thoroughly humiliate me. But
    feel free to try.


  86. #86

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

    any IT person that prevents you from installing an OFFICE COMPONENT
    should be shot. I swear to god.. JUST WALK AWAY FROM ANY COMPANY THAT
    WONT GIVE YOU YOUR OWN OFFICE DISK.

    I mean-- you spend; what.. 50% of your time in outlook, excel and word?

    If you want to be able to read docs that shipped on the office cd-- you
    should be free to do that.. If you want to install an add-in for
    excel to be able to XYZ-- that should be your perogative.

    i strongly disagree with all these companies that don't give the end
    users enough FREEDOM to get their jobs done.

    who the hell are they to dictate how you do your job? I just swear to
    god.. These idiot IT people run around and won't let you have an Office
    CD-- they do a 'minimal installation' -- what happens when you need the
    analysis toolpack for Excel?

    You call HELPDESK?

    OMG i just dont know where the hell these people came up with these
    crap ideas.

    to be honest harlan; i never realized that there was an Office Standard
    Edition.. i honestly thought that all large companies HAD TO USE the
    professional edition since Small Business was only for SMB.

    But the bottom line.. is that if you work for a company that doesnt
    have Access licenses; you can use the Office Developers' edition (the
    name changes every version) to create an installer with the Access
    Runtime.. this is a FREE option (on the client).. and it's beautiful.
    It won't let you write queries-- but it'll let you run forms and
    reports-- and that combined with a good access developer; and you've
    got all you need (for typical excel dorks you can just let them RUN the
    report instead of REWRITING a different copy of it for each week/month)


    But the bottom line is that since Office Pro is only $100 more than
    Office standard; it's kindof retarded for companies to buy office
    standard.

    Do you know that Crystal Reports costs?? what, a grand for designing
    reports and $50k for having a single CPU report server?

    ACCESS is $100 per seat-- tops.. and www.rptsoftware.com has a report
    server component for Access that is something like $300 for a SERVER.

    It's all about not recreating the wheel. And that's why i dont use
    Excel, I dont use Visual Basic (for a bunch of reasons).. Access is a
    MUCH better environment. It's faster-- there are no deployment
    problems (to speak of).. and it's VERYYYYYYYYYYYYYYYYY powerful.

    for quick and dirty apps; I won't use ASP or ASP.net (dont worry i sure
    as HELL dont use Visual Studio to do it.. a bunch of Excel dorks and C#
    dorks designed that.. for sure)-- if I had a month I can build
    amazon.com in ASP--- but that's not the right tool for every project.

    The fact of the matter is that, by and large-- you guys are making the
    same reports week in and week out.. you make spreadsheet messes-- and
    when you hit the limits of Excel; you call in someone to spend a
    million dollars and turn it into a crystal report.. or a J2ee
    component.. I mean.. WTF?

    there is a better way; it is called ACCESS.

    I'm just tired of having to copy objects to a hundred different
    documents.. I'm tired of cutting and pasting info into Excel... And I'm
    tired of having dirty data from spreadsheets infecting my databases.
    If Excel was a RELIABLE STORE FOR INFORMATION-- maybe i wouldn't be so
    militantly anti-XLS... but as it is; validation needs to be about 1,000
    times STRONGER in order to make Excel a decent platform.

    And I know that you're tired of this 1-dimensional stuff also. It's
    just so 1995 to copy and paste data around like you guys do. It's
    error-prone.. it's repetitive.. it's boring..

    -Aaron


  87. #87
    Norman Jones
    Guest

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

    Hi Aaron,

    <[email protected]> wrote in message
    news:[email protected]...
    > any IT person that prevents you from installing an OFFICE COMPONENT
    > should be shot. I swear to god.. JUST WALK AWAY FROM ANY COMPANY THAT
    > WONT GIVE YOU YOUR OWN OFFICE DISK.
    >
    > I mean-- you spend; what.. 50% of your time in outlook, excel and word?
    >
    > If you want to be able to read docs that shipped on the office cd-- you
    > should be free to do that.. If you want to install an add-in for
    > excel to be able to XYZ-- that should be your perogative.
    >
    > i strongly disagree with all these companies that don't give the end
    > users enough FREEDOM to get their jobs done.
    >
    > who the hell are they to dictate how you do your job? I just swear to
    > god.. These idiot IT people run around and won't let you have an Office
    > CD-- they do a 'minimal installation' -- what happens when you need the
    > analysis toolpack for Excel?
    >
    > You call HELPDESK?
    >
    > OMG i just dont know where the hell these people came up with these
    > crap ideas.
    >
    > to be honest harlan; i never realized that there was an Office Standard
    > Edition.. i honestly thought that all large companies HAD TO USE the
    > professional edition since Small Business was only for SMB.
    >
    > But the bottom line.. is that if you work for a company that doesnt
    > have Access licenses; you can use the Office Developers' edition (the
    > name changes every version) to create an installer with the Access
    > Runtime.. this is a FREE option (on the client).. and it's beautiful.
    > It won't let you write queries-- but it'll let you run forms and
    > reports-- and that combined with a good access developer; and you've
    > got all you need (for typical excel dorks you can just let them RUN the
    > report instead of REWRITING a different copy of it for each week/month)
    >
    >
    > But the bottom line is that since Office Pro is only $100 more than
    > Office standard; it's kindof retarded for companies to buy office
    > standard.
    >
    > Do you know that Crystal Reports costs?? what, a grand for designing
    > reports and $50k for having a single CPU report server?
    >
    > ACCESS is $100 per seat-- tops.. and www.rptsoftware.com has a report
    > server component for Access that is something like $300 for a SERVER.
    >
    > It's all about not recreating the wheel. And that's why i dont use
    > Excel, I dont use Visual Basic (for a bunch of reasons).. Access is a
    > MUCH better environment. It's faster-- there are no deployment
    > problems (to speak of).. and it's VERYYYYYYYYYYYYYYYYY powerful.
    >
    > for quick and dirty apps; I won't use ASP or ASP.net (dont worry i sure
    > as HELL dont use Visual Studio to do it.. a bunch of Excel dorks and C#
    > dorks designed that.. for sure)-- if I had a month I can build
    > amazon.com in ASP--- but that's not the right tool for every project.
    >
    > The fact of the matter is that, by and large-- you guys are making the
    > same reports week in and week out.. you make spreadsheet messes-- and
    > when you hit the limits of Excel; you call in someone to spend a
    > million dollars and turn it into a crystal report.. or a J2ee
    > component.. I mean.. WTF?
    >
    > there is a better way; it is called ACCESS.
    >
    > I'm just tired of having to copy objects to a hundred different
    > documents.. I'm tired of cutting and pasting info into Excel... And I'm
    > tired of having dirty data from spreadsheets infecting my databases.
    > If Excel was a RELIABLE STORE FOR INFORMATION-- maybe i wouldn't be so
    > militantly anti-XLS... but as it is; validation needs to be about 1,000
    > times STRONGER in order to make Excel a decent platform.
    >
    > And I know that you're tired of this 1-dimensional stuff also. It's
    > just so 1995 to copy and paste data around like you guys do. It's
    > error-prone.. it's repetitive.. it's boring..
    >
    > -Aaron


    Is it possible that *you* are the same Aaron Kemp who recently posted:

    '================================
    i just wish that they would take the help for products and make it a
    seperate download.. just like books online for sql server.


    because i have a dozen problems with their help system per week.


    and i work for a company where i cant get an office disk


    -aaron
    '================================

    To refresh your memory, see the full thread at:

    http://tinyurl.com/933nc


    BTW, how many IT people have you shot in your company?

    ---
    Regards,
    Norman



  88. #88
    Registered User
    Join Date
    08-11-2005
    Location
    Netherlands Waddinxveen
    Posts
    81
    Responding on the initial Question.

    You should go for a database.
    Why:

    You can filter better/faster on a database then a excelsheet.

    If you have a low budget go for Access (DAO database)
    You do not need Access to create and maintain a Access database if you have Office 2002 or higher installed. the Microsoft Jet engine is included with or without Access so I'm told (couldn't check because I'v got Access on all stations)

    if you have money and time to spend go for a combination of VB, C++ or .net App and a proffesional database.

    The first thing I would do is make the information easier to find so you have to use a form in Excel, Access or your stand alone App anyway The advantage of a stand alone app is the use off a flexgrid to present info in a spreadsheet kind of format

    "Be your Best"


    I do not like to search 65.000 or more lines to find the information I need.

  89. #89
    Harlan Grove
    Guest

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

    <[email protected]> wrote in message...
    >any IT person that prevents you from installing an OFFICE COMPONENT
    >should be shot. I swear to god.. JUST WALK AWAY FROM ANY COMPANY THAT
    >WONT GIVE YOU YOUR OWN OFFICE DISK.


    If you don't use MSDE, you should be fired, and if your company won't let
    you install MSDE, you should quit!?

    You should get a strong dose of reality.

    >i strongly disagree with all these companies that don't give the end
    >users enough FREEDOM to get their jobs done.


    What you don't understand is that most people's jobs probably could be done,
    perhaps more slowly, without any computer at all. Business PCs are just
    tools either to simplify users lives OR simplify data entry and control.
    Users have some limited say in the former, none in the latter. With regard
    to the former, it's their choice as to what works best for them of the tools
    their employer provides.

    >who the hell are they to dictate how you do your job? . . .


    Those who pay get to call the shots. You're really unaware of this?!

    >But the bottom line is that since Office Pro is only $100 more than
    >Office standard; it's kindof retarded for companies to buy office
    >standard.

    ....

    Multiply that $100 by the number of seats AND the additional training and
    support, and even you should begin to see why some companies stick with
    Office Standard.



  90. #90
    Forum Contributor
    Join Date
    12-04-2003
    Posts
    360
    Aaron, it’s such a shame that you devote so much time to denigrating excel and not your work.

    Did you have a bad experience with excel in your childhood?
    Did it harm you in any way?

    There must be some reason for the hate.

    I believe there are professional councillors who may help you out with your problems, idiosyncrasies, delusions of grandeur.

    Get a life and get back to work.

  91. #91

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

    I LEFT THAT COMPANY BECAUSE THEY'RE A PIECE OF ****


  92. #92

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

    yes, I was assraped by a demon that ate spreadsheets

    this demon lives and thrives in each of your heads.. you sit around and
    build the same spreadsheet week in and week out

    it is not the most efficient way to do business.

    you idiots sit around AND RECREATE THE SAME REPORT WEEK IN AND WEEK
    OUT.

    YOU SIT AROUND AND MAKE INVOICES IN EXCEL.

    YOU SIT AROUND AND MAKE COMPENSATION REPORTS IN EXCEL.

    YOU SIT AROUND AND MAKE BUDGETING AND FORECASTING APPS IN EXCEL.

    YOU GUYS ARE THE BANE OF HUMANITY-- YOU SIT AROUND AND TAKE ADVANTAGE
    OF THE DATABASE PEOPLE-- YOUR DISEASED PROGRAM SPAWNS MORE AND MORE
    BEANCOUNTERS-- WHEN WHAT YOU REALLY NEED IS MORE AND MORE DATABASE
    PEOPLE.


  93. #93

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

    ACCESS DOESNT TAKE TRAINING.

    IT TAKES PEOPLE WITH DRIVE TO GO OUT AND LEARN IT ON THEIR OWN.

    YOU GUYS ARE SLOTHS; YOU AREN'T ADAPTING TO TECHNOLOGY. YOU GUYS ARE
    JERKING OFF WITH SPREADSHEETS AND YOUR FUTURE CONSISTS OF DRINKING OUT
    OF A PAPER BAG ON THE SIDE OF THE STREET.

    Harlan-- DATA ENTRY DOESNT EVER HAPPEN IN EXCEL YOU IDIOT.

    DATA ENTRY HAPPENS WITH A DATABASE. AND YOUR DISEASED COMPANY AND
    DISEASED FRIENDS NEED TO WAKE UP AND SMELL THE COFFEE. THERE ARE
    BETTER TOOLS FOR BUILDING THE SAME REPORT WEEK IN AND WEEK OUT. THERE
    ARE BETTER TOOLS FOR 'DATA ENTRY'.


  94. #94
    Registered User
    Join Date
    08-12-2005
    Posts
    33
    Hi,
    Put your data in Ms Access then import them on excel by pivot table.

  95. #95
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    Quote Originally Posted by [email protected]
    I LEFT THAT COMPANY BECAUSE THEY'RE A PIECE OF S**T
    I see Aaron is at it again. Your filth does nothing to promote your cause. It does, however, minimize any credibility you might have garnered. Your posts should be helpful and informative to the readers. This is neither.

    BTW, I'll bet you still cashed the paychecks from that company.

    Bruce
    Bruce
    The older I get, the better I used to be.
    USA

  96. #96
    Harlan Grove
    Guest

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

    [email protected] wrote...
    >ACCESS DOESNT TAKE TRAINING.
    >
    >IT TAKES PEOPLE WITH DRIVE TO GO OUT AND LEARN IT ON THEIR OWN.


    Any software can be learned by anyone with sufficient wit, sufficient
    motivation and sufficient time. It's that third item that explains why
    sensible companies spend money on training - the time saved more than
    makes up for the training cost FOR A *FEW* PEOPLE. It doesn't make
    sense for *ALL* people, and most Excel users aren't going to spend
    their weekends coming into the office to learn Access on their own, and
    they're not going to waste their weekdays learning it because they have
    their REAL JOBS to do.

    >Harlan-- DATA ENTRY DOESNT EVER HAPPEN IN EXCEL YOU IDIOT.


    Data entry happens almost exclusively in spreadsheets and two rather
    ancient Clipper apps (admittedly database, but xBase rather than SQL)
    where I work. You may know how things are done where you work (unlikely
    since you'd thoroughly demonstrated your narrowness of perspective),
    but you have no idea how things are done where I work.

    I can't call you an idiot. That'd imply there was some slight chance
    you could recognize the overwhelming stupidity of what you write.

    >DATA ENTRY HAPPENS WITH A DATABASE. AND YOUR DISEASED COMPANY AND
    >DISEASED FRIENDS NEED TO WAKE UP AND SMELL THE COFFEE. THERE ARE

    ....

    Data entry happens in forms, be they database forms, web forms,
    spreadsheet forms, whatever. Maybe most data is stored in database.
    That's fine. I'm on record as stating that databases are ideal as
    storage subsystems. They make decent reporting tools too. They're just
    not particularly flexible for analysis. Of couse if you assume several
    tens of thousands of dollars of additional software running on top of
    some database, that'd help, but most Excel users won't have such
    additional software available for their use, so assuming they do would
    only futher establish the utter stupidity of your rants.


  97. #97

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

    YOU'RE NOT IMPORTING FROM EXCEL BACK INTO ACCESS YOU MERELY RUN REPORTS
    ON THE NUMBERS

    THE DATA ISNT GOING FROM EXCEL TO ACCESS TO EXCEL

    ITS GOING FROM THE SOURCE-- TO ACCESS-- AND IT CAN BE ******** REPORTED
    ON ***************** through a pivot table.

    pivot tables aren't an excel phenomenon.

    there are a dozen differenent ways to use pivotTables without opening
    excel

    I would reccomend OFFICE WEB COMPONENTS over excel any day of the
    week.. emailing huge spreadsheets around was passe in 1995


  98. #98
    Harlan Grove
    Guest

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

    [email protected] wrote...
    >YOU'RE NOT IMPORTING FROM EXCEL BACK INTO ACCESS YOU MERELY RUN REPORTS
    >ON THE NUMBERS


    If the numbers aren't in one's company's database, e.g., any
    information fresh from customers, Access could do squat all with it.

    Possibly it could be scanned and sent through OCR. Obviously we'd
    differ on the best software to use to check the scanned/OCRed result,
    but I'd use Excel or Word EVERY TIME rather than Access in that
    situation.

    >THE DATA ISNT GOING FROM EXCEL TO ACCESS TO EXCEL

    ....

    For me, if original data entry is in Excel, it usually stays there,
    never moving on to Access or Oracle or DB2. In the rare situations it
    does move on to Access, it never comes back to Excel because it's
    already available in Excel, and I find it easier & quicker to pull such
    data from other XLS files than from Access.

    >I would reccomend OFFICE WEB COMPONENTS over excel any day of the
    >week.. emailing huge spreadsheets around was passe in 1995


    And how would one transmit a lovely OWC object to anyone else if the
    sender has no rights to create web pages on the web server, which is
    the situation of most employees in most companies whose jobs aren't in
    web development? Lemme think - convert it into an Excel file!

    When are you gonna stop being an idiot?


  99. #99
    Registered User
    Join Date
    03-08-2005
    Location
    The Netherlands
    Posts
    25

    Red face bypass the 65 K limit in Execl

    Try Corel's Quattro Pro 12, The limit is 1000000 rows (A1 to IV 100000) and Tabs from A through Z

    It's an investment but it works.

    I use it when running a 175000 records csv file and it works, just keep an eye on the virtual memory but even this is handled better with Corel.

    Hans
    Hans

  100. #100
    Registered User
    Join Date
    06-14-2006
    Posts
    2
    When I try to open a large DBF in either ACCESS or QUATTRO,
    I get the error message INDEX DOES NOT EXIST.

    I am aware that the values in Column A are not unique.

    Any solutions?

    -- Thanks Jay

  101. #101
    Forum Contributor
    Join Date
    11-14-2005
    Location
    Somewhere....out there.....
    Posts
    126
    I wonder if this Aaron idiot eats with the same mouth that the rest of his garbage comes from?

    I wonder what rock he crawled out from? Whatever one it was, he should crawl back under it.

    Incidentally, what other names does this moronic troll use to sneak into forums and newsgroups?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1