[AccessD] Crosstab question

" My client needs rolling 12-month reports with the values broken out like a spreadsheet"

So why not use a spreadsheet and push (or pull) the data into an excel pivot table.  PT's are much more powerful than x-tabs in Access.  You can open Excel and create a PT directly linked into your query.

Best of both worlds. I do this a lot.

regards
Darryl.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Steve Erbach
Sent: Thursday, 23 July 2009 6:18 AM
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

______________________________________________________________________
This email and any attachments may contain privileged and confidential
information and are intended for the named addressee only. If you have
received this e-mail in error, please notify the sender and delete
this e-mail immediately. Any confidentiality, privilege or copyright
is not waived or lost because this e-mail has been sent to you in
error. It is your responsibility to check this e-mail and any
attachments for viruses.  No warranty is made that this material is
free from computer virus or any other defect or error.  Any
loss/damage incurred by using this material is not the sender's
responsibility.  The sender's entire liability will be limited to
resupplying the material.
______________________________________________________________________