Large Query Resultsets in Excel/MS Query 2003

[SkipVought]

There may be no resolution to this, but I'd like some discussion regarding this issue and maybe we'll get some practical suggestions.

The situation relates to MS Query resultsets that exceed the row limit of Excel. The work around options that I have been using are 1) use a PivotTable w/ External Data or 2) perform the query in MS Access.

If the former option is taken, you have to redo the query in the PivotTable wizard. Yes, you can copy 'n' paste the SQL, but there's still the time it takes to wait for the resultset to be returned. And I've been waiting 45 minutes!!! Nuthin' to do but browse & post in Tek-Tips. ;-)

It might be nice to have the ability within MS Query to "switch" the resultset to the PivotTable Wizard. Would MS ever consider adding such a feature?

Any thoughts?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DrSimon]

Good point Skip. I suspect that if MS did as you requested it would still take ages to return the data.

I think it's all a balance of what you want to get out of it and who's doing it (in terms of ability). A pivot table can be great if it analyses data in the way you want. Access has that wonderful query front-end that takes away the need to know any SQL for most situations as well as the ability to manage fairly large tables pretty efficiently. But then it's stuck in a table or query when many people want the flexibility of Excel to deal with it. I've just written an application that reads data from MS Project performs some joins and then exports it to Excel and creates pivot tables. It does the job well. I know a lot of people knock MS, but if you play to its strengths it can do a lot.
No answer of course!

Simon Rouse

 

[xlbo]

To be honest Skip, I've kinda given up on MS Query - you led me down the path of ADO and that's where I'm staying! Much more flexible and you can decide where you want to put your data too!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fneily]

the time it takes to wait for the resultset to be returned"

Could that be due to the hierarchical structure of an Excel List? If the data was normalized in Access, would the query be quicker? Don't know for your data, but that is one of the reasons Dr. Codd developed normalization and relational tables.

 

[SkipVought]

fneily,

These are Oracle tables and normalized, but HUGE.

Geoff,

Spot on!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mintjulep]

Would this help?

thread68-1509745

 

[SkipVought]

mintjulep,

Why have I not thought of that? Good suggestion!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fneily]

Can't argue with Oracle. Their software was actually built on the theories of Codd.

 

[xlbo]

...thought normalised tables were better for loading / updating but not as fast for querying?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fneily]

Take a hierarchical database of car parts. So each Excel sheet lists the individual parts for each model of Fords. I want to know what cars use oil filter XYZ. Do the query.
Take a relational database. Normalized tables of Ford cars and parts. One query with the appropriate relationship joins. Finished.

If the data has unique related fields, such as a family tree, then an hierarchical structure would be best.

 

[xlbo]

aaah yes - I was referring to non normalised summary tables so that you have no hierarchy issues - in that case I believe non normalised is quicker for returning data...and to be honest I try whenever possible not to query data OUT of excel becuase that's not what it was set up to do. Simple 1 table query from SQLServer/Oracle will be faster than the same query from an excel sheet...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question