Sub Table in Report

  • Thread starter Thread starter vanleurth
  • Start date Start date
V

vanleurth

How do I add/create a table that shows months on the first column going down
in rows (jun 2006 to july 2007) and then have diferent fiscal years in each
following colums. I need to add this to a report that has the representation
of this data in a chart form.

Thank you,

Victor
 
Search the help files for Crosstab queries. Use a crosstab as the source for
your report.
 
Thank you Arvin,
This is what I get (see below) with this Crosstab Query:
TRANSFORM Sum([tbl readings famis].METER_READING) AS SumOfMETER_READING
SELECT Format([USAGE PERIOD],"mmm") AS [Month]
FROM [tbl readings famis]
WHERE ((([tbl readings famis].METER_SITE_NO)="REC ELEC C #5") AND (([tbl
readings famis].UTILITY_CODE)="ELECTRIC"))
GROUP BY [tbl readings famis].[USAGE PERIOD]
ORDER BY [tbl readings famis].[USAGE PERIOD]
PIVOT Format([USAGE PERIOD],"yyyy");

Notice that instead of having the number for Jul 2007 show next to the 2006
number the query opens a new row at the end of the table. How can I have the
numbers under the same month but in different years so it will show in the
same row?


Month 2006 2007
Jul 25544
Aug 26389
Sep 27343
Oct 28163
Nov 28849
Dec 29692
Jan 30397
Feb 31139
Mar 31899
Apr 32748
May 33459
Jun 34183
Jul 35480

Search the help files for Crosstab queries. Use a crosstab as the source for
your report.
How do I add/create a table that shows months on the first column going
down
Click to expand...
[quoted text clipped - 7 lines]
Victor
Click to expand...
Click to expand...
 
vanleurth said:
Thank you Arvin,
This is what I get (see below) with this Crosstab Query:
TRANSFORM Sum([tbl readings famis].METER_READING) AS SumOfMETER_READING
SELECT Format([USAGE PERIOD],"mmm") AS [Month]
FROM [tbl readings famis]
WHERE ((([tbl readings famis].METER_SITE_NO)="REC ELEC C #5") AND (([tbl
readings famis].UTILITY_CODE)="ELECTRIC"))
GROUP BY [tbl readings famis].[USAGE PERIOD]
ORDER BY [tbl readings famis].[USAGE PERIOD]
PIVOT Format([USAGE PERIOD],"yyyy");

Notice that instead of having the number for Jul 2007 show next to the 2006
number the query opens a new row at the end of the table. How can I have the
numbers under the same month but in different years so it will show in the
same row?

Month 2006 2007
Jul 25544
Aug 26389
Sep 27343
Oct 28163
Nov 28849
Dec 29692
Jan 30397
Feb 31139
Mar 31899
Apr 32748
May 33459
Jun 34183
Jul 35480
Search the help files for Crosstab queries. Use a crosstab as the source for
your report.
Click to expand...
[quoted text clipped - 3 lines]
Click to expand...
 
Back
Top