Insert a crosstab to temp table for report

  • Thread starter Thread starter inungh
  • Start date Start date
I

inungh

Because the report is very complex that it takes more than one minute
to get the report, I have to insert my crosstab queries in to a temp
table for the reports to improve performance of the report.

The report have many crosstab queries and many sub reports. I want to
prepare all data and insert in to a temp table for the report to
inprove perfromance and reduce database connections. The report
reaches maximum of connections which MS Access support.

Since the number of columns is unknown before insert the records, are
there any way to know the column counts that I can build insert SQL
statement dynamic using the number if count.

I use MS Access 2003.

Your help is great appreciated,
 
If your crosstab is like:


TRANSFORM ...
...
FROM table
...
PIVOT expression


then,


SELECT COUNT(*)
FROM (SELECT DISTINCT expression
FROM table) AS x



returns the number of columns created by the crosstab. You have to add the
columns for the GROUP as well.



Vanderghast, Access MVP
 
If your crosstab is like:

    TRANSFORM ...
    ...
    FROM table
    ...
    PIVOT  expression

then,

    SELECT COUNT(*)
    FROM (SELECT DISTINCT expression
            FROM table) AS x

returns the number of columns created by the crosstab. You have to add the
columns for the GROUP as well.

Vanderghast, Access MVP










- Show quoted text -
Click to expand...

thanks millions,

I can run the SQL to get count without adding it in the crosstab
query.
Am I right?


Thanks again,
 
Sure, you are not obliged to 'generate' the crosstab. Running the SELECT
COUNT, which is a standard query independent of the crosstab, can occur
before you ever generate the crosstab.


Vanderghast, Access MVP


If your crosstab is like:

TRANSFORM ...
...
FROM table
...
PIVOT expression

then,

SELECT COUNT(*)
FROM (SELECT DISTINCT expression
FROM table) AS x

returns the number of columns created by the crosstab. You have to add the
columns for the GROUP as well.

Vanderghast, Access MVP










- Show quoted text -
Click to expand...

thanks millions,

I can run the SQL to get count without adding it in the crosstab
query.
Am I right?


Thanks again,
 
Sure, you are not obliged to 'generate' the crosstab. Running the SELECT
COUNT, which is a standard query independent of the crosstab, can occur
before you ever generate the crosstab.

Vanderghast, Access MVP









thanks millions,

I can run the SQL to get count without adding it in the crosstab
query.
Am I right?

Thanks again,- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks millions,
 
inungh said:
Because the report is very complex that it takes more than one minute
to get the report, I have to insert my crosstab queries in to a temp
table for the reports to improve performance of the report.
Click to expand...

Sounds like you already have an answer but this might work too.

Try putting just your basic data including all the data from joins and
such in the temp table. Doing summing if appropriate. Then try
running your cross tabs against that temp table.

Tony
 
Sounds like you already have an answer but this might work too.

Try putting just your basic data including all the data from joins and
such in the temp table.  Doing summing if appropriate.  Then try
running your cross tabs against that temp table.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
Granite Fleet Managerhttp://www.granitefleet.com/
Click to expand...

Does it give me the count of columns (fields)?
Thanks again,
 
Back
Top