CrossTab Query

[Brian]

Hello all, ok I have an issue here.. I have a cross tab query that has
to be used in a report and the columns will increase or decrease
depending on the selection criteria... is this a dynamic report? any
tricks to this?

Here is the crosstab if that helps any this is what I want but I can't
just call it as a query for preview cause I need some header stuff on
the report as well...

TRANSFORM Sum(tblTest.Stock) AS [The Value]
SELECT tblTest.DBH, Sum(tblTest.Stock) AS [Total Of Stock]
FROM tblTest
GROUP BY tblTest.DBH
PIVOT tblTest.SPC;

Many Thanx

 

[Guest]

Would it be acceptable to have a permanent list of columns?
You can do that by adding to your query the following --
PIVOT tblTest.SPC IN("Set 1", "Set 2", "Set 3", "Set 4");
Just predefine all of your columns.

 

[Brian]

Yeah I looked at that option however there could be up to like 30
possibilites which could get pretty messy and of the non-visible type
just wondering if there was another option. thanx anyways

Would it be acceptable to have a permanent list of columns?
You can do that by adding to your query the following --
PIVOT tblTest.SPC IN("Set 1", "Set 2", "Set 3", "Set 4");
Just predefine all of your columns.

Hello all, ok I have an issue here.. I have a cross tab query that has
to be used in a report and the columns will increase or decrease
depending on the selection criteria... is this a dynamic report? any
tricks to this?

Here is the crosstab if that helps any this is what I want but I can't
just call it as a query for preview cause I need some header stuff on
the report as well...

TRANSFORM Sum(tblTest.Stock) AS [The Value]
SELECT tblTest.DBH, Sum(tblTest.Stock) AS [Total Of Stock]
FROM tblTest
GROUP BY tblTest.DBH
PIVOT PIVOT tblTest.SPC IN("Set 1", "Set 2", "Set 3", "Set 4");

Many Thanx

 

[Duane Hookom]

There is a fairly flexible and efficient solution in the Crosstab download
at http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP

Yeah I looked at that option however there could be up to like 30
possibilites which could get pretty messy and of the non-visible type
just wondering if there was another option. thanx anyways

Would it be acceptable to have a permanent list of columns?
You can do that by adding to your query the following --
PIVOT tblTest.SPC IN("Set 1", "Set 2", "Set 3", "Set 4");
Just predefine all of your columns.

Hello all, ok I have an issue here.. I have a cross tab query that has
to be used in a report and the columns will increase or decrease
depending on the selection criteria... is this a dynamic report? any
tricks to this?

Here is the crosstab if that helps any this is what I want but I can't
just call it as a query for preview cause I need some header stuff on
the report as well...

TRANSFORM Sum(tblTest.Stock) AS [The Value]
SELECT tblTest.DBH, Sum(tblTest.Stock) AS [Total Of Stock]
FROM tblTest
GROUP BY tblTest.DBH
PIVOT PIVOT tblTest.SPC IN("Set 1", "Set 2", "Set 3", "Set 4");

Many Thanx