Find the max number of columns returned from a crosstab

[joshblair]

I am trying to build a report that will accomodate a crosstab that
returns an unknown number of columns.

I'm looking to write a query that wll tell me the maximun number of
columns that could be returned by this cross tab query.

I have the following crosstab query:

PARAMETERS [Please specify Part number] Text;
TRANSFORM
Avg(IIf([tblQUOTE_VALUES].vPrice<>0,[tblQUOTE_VALUES].vPrice)) AS [The
Value]
SELECT tblQUOTECORE.qtRFQDate, tblQUOTECORE.qtQuoteNo,
tblQUOTECORE.qtSupplierID, VEID.VEND_NAME
FROM (tblQUOTECORE LEFT JOIN tblQUOTE_VALUES ON tblQUOTECORE.qtQuoteNo
= tblQUOTE_VALUES.vQuoteNo) LEFT JOIN VEID ON tblQUOTECORE.qtSupplierID
= VEID.VENDOR_ID
WHERE (((tblQUOTECORE.qtPartNo)=[Please specify Part number]))
GROUP BY tblQUOTECORE.qtRFQDate, tblQUOTECORE.qtQuoteNo,
tblQUOTECORE.qtSupplierID, VEID.VEND_NAME
ORDER BY tblQUOTECORE.qtRFQDate DESC
PIVOT tblQUOTE_VALUES.vQty;

I tried to do a count of the [tblQUOTE_VALUES].vQty and
[tblQUOTE_VALUES].vQty fields but that doesn't seem to work.

So far, I have found one part number that produces 10 dynamic columns
from the crosstab query listed above.

The reason that I need to know how many possible columns is because of
all the "dynamic crosstab report" methods that I have seen, you have to
know the maximum number of columns that can be returned so you can put
unbound labels and textboxes to accomodate these columns.

 

[Duane Hookom]

There is a solution for dynamic multi-column crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. Check the Crosstab.mdb
file.

 

[joshblair]

Duane,

Thanks for the reply.

I have studied your crosstab.mdb solution before but I don't understand
how to apply this technique to my particular problem. It may be that I
just don't understand your solution, but I don't see how I can create
the equivalent of your tblEmpCustAlias table.

Can you help me to understand this concept?

My crosstab returns columns that are quantities, like 500, 1000, 2500,
5000. These quantities differ for each part number (part number is the
criteria for my crosstab query).

When I run a the crosstab for a different part number, I may get
quantities of 1000, 5000, 10000, 25000, 250000.

Does your solution require that I create a table with every possible
quantity for every possible part number?

Sorry for being so dense.

Any info is appreciated.

Josh Blair

 

[Duane Hookom]

You don't have to create a table of every possible part quantity. The code
actually does this for you.