[AccessD] Crosstab question

What I've done in this situation is to build an input query which makes one
value field combining the multiple values I want; then a crosstab query
selecting the First of my combined field.  The crosstab feeds into the
report, and the report 'decodes' the values.

For instance, if you want a cost of $1.00 and  a count of 25 to be shown,
then the initial query builds a field as follows:

(Assuming that the count will never be more than 1000):

myNewField = cost * 10000 + count

This results in 10025 in the 'myNewField' column.

The report parses this into two text boxes:
txCost = ccur(int(myNewField/10000))
txCount = myNewField mod 10000

This is air code, but hopefully you get the drift.

Doug Steele


On Wed, Jul 22, 2009 at 1:18 PM, Steve Erbach <erbachs at gmail.com> wrote:

> 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.
>
>  <http://www.databaseadvisors.com>
>