Queries in Subforms

[Potat_o]

I am trying to add a crosstab query to a subform and am receiving the
following error message: "You can't use a pass-through query or a
non-fixed-column crosstab query as a record source for a subform or
subreport. Before you bind the subform or subreport to a crosstab query, set
the query's ColumnHeadings property." I've gotten this a few times before,
but am unsure of what I am supposed to do. Any help would be appreciated.

 

[ken]

You need to include an IN clause in the query so that it returns a
fixed number of columns of known names. Here's an example:

TRANSFORM Nz(Sum(Feet),0)
SELECT DrillLog.City
FROM DrillLog
GROUP BY DrillLog.City
PIVOT Format(CompleteDate,"mmm yyyy")
IN ("Oct 2005","Nov 2005","Dec 2005","Jan 2006","Feb 2006","Mar
2006");

It returns depths drilled by rigs per city each month from October
2005 to March 2006 in case you are wondering.

In query design view you'd do this by entering the value list as the
ColumnHeadings property in the query's properties sheet.

Ken Sheridan
Stafford, England

On Apr 30, 5:11 pm, Potat_o <[email protected].(donotsendspam)

 

[Potat_o]

Thank you for your help Ken. That worked perfectly!
--
Thanks,
Brian

You need to include an IN clause in the query so that it returns a
fixed number of columns of known names. Here's an example:

TRANSFORM Nz(Sum(Feet),0)
SELECT DrillLog.City
FROM DrillLog
GROUP BY DrillLog.City
PIVOT Format(CompleteDate,"mmm yyyy")
IN ("Oct 2005","Nov 2005","Dec 2005","Jan 2006","Feb 2006","Mar
2006");

It returns depths drilled by rigs per city each month from October
2005 to March 2006 in case you are wondering.

In query design view you'd do this by entering the value list as the
ColumnHeadings property in the query's properties sheet.

Ken Sheridan
Stafford, England

On Apr 30, 5:11 pm, Potat_o <[email protected].(donotsendspam)