Varying Number of Columns in Crosstab Query

[Darrell]

I am using a crosstab query as the record source for a report and my
problem is that I don't know how many columns the query will actually
return because the number of different values in the column used for the
crosstab will vary depending on the parameter values passed to the
query. Clearly, designing a report for a shifting number of columns is
problematic. How does one handle this situation?

I know there was a similar post on 4/9 regarding number of work days,
but being fairly new to crosstab queries, I didn't follow all the sql of
the response.

The current SQL statement, simplified, is below. It's the Allocation
field which contains the data in question. Often, depending on the
parameter values, only 3 of the 6 possible values will actually be in
the record set returned.

PARAMETERS BeginningDate DateTime, EndingDate DateTime;
TRANSFORM First(PaymentAmount) AS FirstOfPaymentAmount
SELECT ID, [LastName] & (", "+[FirstName]) AS Name, PaymentDate,
ReceiptNumber, PaymentMethod
FROM tblPaymentsReceived
WHERE PaymentDate)>=BeginningDate And PaymentDate)<=EndingDate) AND
ReceiptNumber Is Not Null
GROUP BY ID, [LastName] & (", "+[FirstName]), PaymentDate,
ReceiptNumber, PaymentMethod
ORDER BY ReceiptNumber
PIVOT Allocation;

Any help would be greatly appreciated.

Darrell

 

[Rob Parker]

Hi Darrell,

If you simply want to ensure that all the possible Allocation values appear
as columns in your output, use the IN clause after the PIVOT statement, and
include the possible values as a list (in brackets) of quoted values
separated by commas, such as:
...
PIVOT Allocation IN ("value 1", "value 2", ...);

You can do this in the query design grid, via the property sheet for the
query itself (click in the top section of the design grid), by entering the
values in the Column Heading property - and that will also add the quotes.

The column headings here will always appear, and in the order in which you
enter them - very useful for months of the year ;-)

HTH,

Rob

 

[Darrell]

Cool! Thank you very much! That does it exactly as I wanted it. Now, I'm sure
additional levels of sophistication would be helpful in the future as the
possible number of values increases with each additional possible value being
stored in a table. But for now, that will work perfectly! Thank you!

Darrell

Hi Darrell,

If you simply want to ensure that all the possible Allocation values appear
as columns in your output, use the IN clause after the PIVOT statement, and
include the possible values as a list (in brackets) of quoted values
separated by commas, such as:
...
PIVOT Allocation IN ("value 1", "value 2", ...);

You can do this in the query design grid, via the property sheet for the
query itself (click in the top section of the design grid), by entering the
values in the Column Heading property - and that will also add the quotes.

The column headings here will always appear, and in the order in which you
enter them - very useful for months of the year ;-)

HTH,

Rob

I am using a crosstab query as the record source for a report and my
problem is that I don't know how many columns the query will actually
return because the number of different values in the column used for the
crosstab will vary depending on the parameter values passed to the query.
Clearly, designing a report for a shifting number of columns is
problematic. How does one handle this situation?

I know there was a similar post on 4/9 regarding number of work days, but
being fairly new to crosstab queries, I didn't follow all the sql of the
response.

The current SQL statement, simplified, is below. It's the Allocation field
which contains the data in question. Often, depending on the parameter
values, only 3 of the 6 possible values will actually be in the record set
returned.

PARAMETERS BeginningDate DateTime, EndingDate DateTime;
TRANSFORM First(PaymentAmount) AS FirstOfPaymentAmount
SELECT ID, [LastName] & (", "+[FirstName]) AS Name, PaymentDate,
ReceiptNumber, PaymentMethod
FROM tblPaymentsReceived
WHERE PaymentDate)>=BeginningDate And PaymentDate)<=EndingDate) AND
ReceiptNumber Is Not Null
GROUP BY ID, [LastName] & (", "+[FirstName]), PaymentDate, ReceiptNumber,
PaymentMethod
ORDER BY ReceiptNumber
PIVOT Allocation;

Any help would be greatly appreciated.

Darrell