Corsstab query in subreports

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that uses multiple subreports. These subreports are based on crosstab queries. The queries pull data based on quarter. The Quarters are used as columns. Question, in the beggining of the year you only have Quarter 1, however when the next quarter comes around my subreport will not show the quarter becuase when I setup the report there was no quarter 2 field. How do I set this up so that when a new field is added based on the crosstab query that it prints my fiel

Field: Expr1: "Qtr " & Format([START DATE OF INCIDENT],"q") "This creates each of the quarter columns
Table: Group B
Crosstab: Column Heading
 
This is what is known as a "non-trivial" problem. The problem with using a
crosstab as the basis for a report is that the crosstab CREATES the column
names. Report controls must be hard-coded to specific columns (fields).
One solution is to alias the field names with generic names like Field1,
Field2 and so on. This must be done programmatically.

On my website( http://www.rogersaccesslibrary.com ) is a small sample
database called "CrossTabReport.mdb" which shows the beginnings of a
solution. It is not by any means complete. There is plenty of room for
improvement, but the idea is there. Another sample (created by Duane
Hookom) called "CrossTab.mdb" can be found here:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org


glazzaro said:
I have a report that uses multiple subreports. These subreports are based
Click to expand...
on crosstab queries. The queries pull data based on quarter. The Quarters
are used as columns. Question, in the beggining of the year you only have
Quarter 1, however when the next quarter comes around my subreport will not
show the quarter becuase when I setup the report there was no quarter 2
field. How do I set this up so that when a new field is added based on the
crosstab query that it prints my field
Field: Expr1: "Qtr " & Format([START DATE OF INCIDENT],"q") "This creates each of the quarter columns"
Table: Group By
Crosstab: Column Heading
Click to expand...
 
glazzaro said:
I have a report that uses multiple subreports. These subreports are based on crosstab queries. The queries pull data based on quarter. The Quarters are used as columns. Question, in the beggining of the year you only have Quarter 1, however when the next quarter comes around my subreport will not show the quarter becuase when I setup the report there was no quarter 2 field. How do I set this up so that when a new field is added based on the crosstab query that it prints my field

Field: Expr1: "Qtr " & Format([START DATE OF INCIDENT],"q") "This creates each of the quarter columns"
Table: Group By
Crosstab: Column Heading
Click to expand...


If you're willing to display all of the quarters in the
report, just with nulls for the quarters that don't have any
data yet, then you can set up the report with all four
quarters and use the crosstab query's Column Headings
property to Qtr1,Qtr2,Qtr3,Qtr4.

When you set the Column Headings property for a crosstab
query, you will get all the columns whether they have any
data or not.
 
Back
Top