Sub report based on Crosstab query

[KHogwood-Thompson]

I have a report called "Balance Sheet" that is based on a crosstab query,
this is the main report and contains output from a query showing Fixed
Assets. I have another report based on a crosstab query showing Current
Assets.

I need to put the "Current Assets" report in the "Balance Sheet" report as a
subreport. If I do this and run the report I get 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 report.

Before you bind the subform or subreport to a crosstab query, set the
query's ColumnHeadings property."

The two crosstab queries have the same column headings etc, just different
data.

What do I need to do to rectify this?

 

[Allen Browne]

Open the subreport's query in design view.

Open the properties box, making sure you look at the properties of the Query
(not of a particular table or field.)

List the valid values beside the Column Headings property.

More info:
http://allenbrowne.com/ser-67.html#ColHead

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[John Spencer]

You need to do what the error message said if you can.

Open the crosstab query you are using as the source for the subreport and
enter the column headings.

In the crosstab query you can specify the field name(s) using an In clause in
the PIVOT statement.

TRANSFORM ...
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
PIVOT MonthFieldNames In ("In Progress","On Time", "Late","Very Late")

In the query grid, you do this:
-- Select View properties
-- Click on the grey area above the grid, so you are looking at the query's
properties
-- Input your values in Column Headings separated by commas (or semicolons
if your separator is semi-colons)

When you do this the specified cross-tab columns will show up and ONLY those
crosstab columns will be visible. If you mistype a value, you will get a
column with that name and no data (all nulls) in that column.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[KHogwood-Thompson]

Many thanks for this both of you, I have now managed to get the subreport to
work fine.

 

[Robbie Doo]

Allen, this really worked well. However, when you list the values in Column
Headings propery it shows everything whether data is there or not. Is it
possible to make the columns disappear if data is null?

Also, how do you put the labels for column headings on the Main Report? I
cannot use the column heading labels that come with crosstab report because
they keep repeating for each data on the main report.