[AccessD] Crosstab question

You can create the corsstabs queries and then use the first one as the source for a Make Table query and the other ones can be used to make append queries that will add their results to the new table.

You can even put all them all into a union query and use that as the source for one make table query.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Steve Erbach
Sent: Wednesday, July 22, 2009 4:18 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Crosstab question

Dear Group,

I need to create a report in the form of a crosstab with 12 monthly columns.  The Crosstab query capability only allows one numeric value to be summarized per column...but the client would like to see three values underneath each month's heading.

I thought that I'd construct a table for use by the report with de-normalized columns in clusters of three for Month1Quantity, Month1Calculation1, Month1Calculation2...Month2Quantity, Month2Calculation1, Month2Calculation2, etc.

I can make a crosstab query easily enough to, say, show the Quantity for each month...but the SQL TRANSFORM statement will not allow me to create a table directly with a SELECT INTO clause.  I've made two other crosstabs containing the Calculation1 and Calculation2 results by month and I want to combine them all into one table for this "triple-value" crosstab report.

I can, of course, create an empty table with the structure I want; then write a function or sub procedure that runs each of the three TRANSFORM queries and loops through the recordset and writes the values into rows in the all-in-one table.  That's the approach I've taken so far.

If I were using Microsoft SQL Server the queries would be more flexible, since I can use a subquery in a SQL FROM clause, which I can't do in Access.  I'm just curious about the methodology in Access.  My client needs rolling 12-month reports with the values broken out like a spreadsheet.
That means a CROSSTAB query...but I've run up against the limitations of Crosstab queries and I'm trying to work around them.

So, am I missing an easy way to convert the results of a Crosstab query to a table?

Regards,

Steve Erbach
Neenah, WI
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com