Insert a crosstab to temp table for report

[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,

 

[Michel Walsh]

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

 

[inungh]

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 -

thanks millions,

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


Thanks again,

 

[Michel Walsh]

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 -

thanks millions,

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


Thanks again,

 

[inungh]

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 -

Thanks millions,

 

[Tony Toews [MVP]]

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.

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

 

[inungh]

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/

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