If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sub report based on Crosstab query
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? -- K Hogwood-Thompson |
#2
|
|||
|
|||
Sub report based on Crosstab query
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "KHogwood-Thompson" wrote in message ... 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? -- K Hogwood-Thompson |
#3
|
|||
|
|||
Sub report based on Crosstab query
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 wrote: 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? |
#4
|
|||
|
|||
Sub report based on Crosstab query
Many thanks for this both of you, I have now managed to get the subreport to
work fine. -- K Hogwood-Thompson "Allen Browne" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "KHogwood-Thompson" wrote in message ... 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? -- K Hogwood-Thompson |
#5
|
|||
|
|||
Sub report based on Crosstab query
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. "Allen Browne" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "KHogwood-Thompson" wrote in message ... 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? -- K Hogwood-Thompson |
Thread Tools | |
Display Modes | |
|
|