Variable # of columns being output by a Crosstab query

Re: Variable # of columns being output by a Crosstab query

scott.k.fraley@ gmail.com wrote in
news:1137804856 .162749.18440@f 14g2000cwb.goog legroups.com:
[color=blue]
> OR, how should I deal with this? I was thinking of trying to
> force the missing columns in there somehow with 0's or
> something but I just can't seem to wrap my head around this
> one.
>[/color]
from the Access help file on the Crosstab Query .ColumnHeadings
property
:
"If you include a column heading in the ColumnHeadings property
setting, the column is always displayed in query Datasheet view,
even if the column contains no data. This is useful for a report
based on a crosstab query, for example, when you always want to
display the same column headings in the report."

[color=blue]
> Thanks for listening,
> -S
>
>[/color]



--
Bob Quintal

PA is y I've altered my email address.

Re: Variable # of columns being output by a Crosstab query


scott.k.fraley@ gmail.com wrote:[color=blue]
> ...and the SELECT thats trying to pull from said Query doesn't like it
> one bit! ;)
>
> I'm working on this project (in Access 2002) and there is a report
> who's RecordSource is the following SELECT;
>
> SELECT
> xTab.FirstOfPRI ORITZTN_STATUS_ NAME,
> xTab.[Total Of CountofITEM_ID],
> iif(IsNull( xTab.[FY06 Q1] ), 0, xTab.[FY06 Q1]) AS [FY06 Q1],
> iif(IsNull( xTab.[FY06 Q2] ), 0, xTab.[FY06 Q1]) AS [FY06 Q2],
> iif(IsNull( xTab.[FY06 Q3] ), 0, xTab.[FY06 Q1]) AS [FY06 Q3],
> iif(IsNull( xTab.[FY06 Q4] ), 0, xTab.[FY06 Q1]) AS [FY06 Q4],
> iif(IsNull( xTab.[FY07 Q1] ), 0, xTab.[FY06 Q1]) AS [FY07 Q1],
> iif(IsNull( xTab.[FY07 Q2] ), 0, xTab.[FY06 Q1]) AS [FY07 Q2],
> IsNull( xTab.[<>], 0 ) AS [<>]
> FROM
> qryAllProjectsE ventsPrioritztn Deployment3_Cro sstab xTab
> WHERE
> (qryAllProjects EventsPrioritzt nDeployment3_Cr osstab
> .FirstOfPRIORIT ZTN_STATUS_NAME = "Enterprise Approved")
> Or
> (qryAllProjects EventsPrioritzt nDeployment3_Cr osstab
> .FirstOfPRIORIT ZTN_STATUS_NAME = "BU/Specialty Group Approved")
> Or
> (qryAllProjects EventsPrioritzt nDeployment3_Cr osstab
> .FirstOfPRIORIT ZTN_STATUS_NAME = "Internal Only")
> Or
> (qryAllProjects EventsPrioritzt nDeployment3_Cr osstab
> .FirstOfPRIORIT ZTN_STATUS_NAME ="Event Only");
>
> Now, as soon as I try to run the report (Print Preview), I get the "The
> Microsoft Jet database engine does not recognize 'xTab.[FY06 Q4]' as a
> valid field name or expression."
>
> I've seen a lot of talk on the 'Net about how others who have gotten
> this error need to specify the data type(s) of their parameters. Well,
> this query doesn't take parameters, it pulls its data from another
> query.
>
> After looking at the output of the crosstab query, I can see that
> 'xTab.[FY06 Q4]' is simply not being output by the query as there is no
> data for that column (and the other 2 that follow).
>
> My question is, is there any way to dynamically find out that those
> columns are not being output, and deal with it properly so that I can
> run this report and not have it crashing?
>
> OR, how should I deal with this? I was thinking of trying to force the
> missing columns in there somehow with 0's or something but I just can't
> seem to wrap my head around this one.
>
> Thanks for listening,
> -S[/color]

Bob's answer is good if you know ahead of time what columns you are
going to want in the crosstab. However, it is more usual for the
crosstab to be dynamicly creating different columns over time. This
means that the report's query and the report itself need to be capable
of figuring this out and dealing with it.

I have done this before and it is a bit complicated. Here's an overview
of what I do:
- In the report design I create empty label controls (caption=blank) for
each potential column named in a consistant way (label01, label02, etc)
- In the detail section I create unbound textbox controls also named in
a consistant way (Data01, Data02, etc) and set visible=no.
- In the Open event of the report I open a recordset based on a query of
the same base as the crosstab to get a record per crosstab column
- I then do a test to make sure there isn't more records than the no of
columns I presetup in the report design
- Then loop through the recordset and change the Caption of the Labels
and the controlsourse of the Textboxes and make them visible

air code:
i=1
Do Until rs.EOF
Me("Data0" & i).Visible = True
Me("Data0" & i).ControlSourc e = rs!AATCAT
Me("Label0" & i).Caption = rs!AATCAT
i = i + 1
Loop

Like I said, its a bit complicated and there may be a better way to do
it, but this has worked very well for me so far.

--
Bri