Year to Date Report

  • Thread starter Thread starter blinton25
  • Start date Start date
B

blinton25

Hello,

How are you today?

With the guidance of this forum I currently generate a
report looking like this:

Code: 
REGIONS3  January	 February          March
2001 2002        2001 2002       2001 2002
USA	  128 399         80  428	   606  58
CANADA	  166 336         294 267	   624  32

REGIONS3  April	           May              June
2001 2002     2001 2002        2001 2002
USA	   128 399        80  428	   606  58
CANADA	   166 336        294 267	   624  32


using the following crosstab:

RegionofResidenceCrosstab-

TRANSFORM "~" & Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)
=forms!DateRangePreviousCurrentYear!PrevYear,[Final_Clean
Tourists Query].[TRAV-KEY])) & "~" & Count(IIf(DatePart
('yyyy',[F_ARRV_DAT],1,0)=forms!
DateRangePreviousCurrentYear!CurrYear,[Final_Clean
Tourists Query].[TRAV-KEY])) AS Expr1
SELECT [Final_Clean Tourists Query].REGIONS3, [Final_Clean
Tourists Query].regionorder
FROM [Final_Clean Tourists Query]
WHERE [Final_Clean Tourists Query].REGIONS3<>''
GROUP BY [Final_Clean Tourists Query].regionorder,
[Final_Clean Tourists Query].REGIONS3
PIVOT Format([F_ARRV_DAT],"mmmm") in
("January", "February", "March", "April","May","June","July
","August","September","October","November","December");

and this union:

SELECT regionorder, Regions3, 1 As Quarter, January as A,
February as B, March as C FROM RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 2 As Quarter, April, May,
June FROM RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 3 As Quarter, July, August,
September FROM RegionofResidenceCrosstab
UNION ALL SELECT regionorder, Regions3, 4 As Quarter,
October, November, December FROM RegionofResidenceCrosstab;


I now wish to produce a YearToDate Report:

Code: 
REGIONS3  Jan-Feb	 Jan-Mar          Jan-Apr
2001 2002        2001 2002       2001 2002
USA	  1   1            2    2	   4    4
CANADA	  2   2            4    4	   8    8

REGIONS3  Jan-May	 Jan-June          Jan-July
2001 2002     2001 2002        2001 2002
USA	    8   8        16  16	           32   32
CANADA	   16  16        32  32	           64   64

using a similar approach, so I generated the following
crosstab:

TRANSFORM "~" & Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)
=forms!DateRangePreviousCurrentYear!PrevYear,[Final_Clean
Tourists Query].[TRAV-KEY])) & "~" & Count(IIf(DatePart
('yyyy',[F_ARRV_DAT],1,0)=forms!
DateRangePreviousCurrentYear!CurrYear,[Final_Clean
Tourists Query].[TRAV-KEY])) AS Expr1
SELECT YearToDate.id, YearToDate.MonthRange, Format
([F_ARRV_DAT],"mmmm") AS [Month], [Final_Clean Tourists
Query].REGIONS3, [Final_Clean Tourists Query].regionorder
FROM [Final_Clean Tourists Query] INNER JOIN YearToDate ON
[Final_Clean Tourists Query].MonthOrder =
YearToDate.MonthDigit
WHERE (((DatePart('yyyy',[F_ARRV_DAT],1,0))=forms!
DateRangePreviousCurrentYear!PrevYear Or (DatePart('yyyy',
[F_ARRV_DAT],1,0))=forms!DateRangePreviousCurrentYear!
CurrYear)) AND YearToDate.MonthMax < (select MAX
([Final_Clean Tourists Query].MonthOrder)+1 from
[Final_Clean Tourists Query]) AND [Final_Clean Tourists
Query].REGIONS3<>''
GROUP BY YearToDate.id, YearToDate.MonthRange,
[Final_Clean Tourists Query].regionorder, [Final_Clean
Tourists Query].REGIONS3, Format([F_ARRV_DAT],"mmmm")
PIVOT MonthRange in ("January - February", "January -
March", "January - April", "January - May","January -
June","January - July","January - August","January -
September","January - October","January -
November","January - December");

where the YeartoDate table looks like:

id MonthRange MonthDigit MonthMax
1 Jan- Feb 2 2
2 Jan- Mar 3 3
3 January-Apr 4 4



1. I am not getting culmative totals, instead I still get
the information broken down by month. Any idea how to
achieve this or how to generate YeartoDate Queries?
 
Hello,

Got it worked out:

TRANSFORM "~" & Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)
=forms!DateRangePreviousCurrentYear!PrevYear AND Format
([F_ARRV_DAT],"m") <= YearToDate.MonthMax,[Final_Clean
Tourists Query].[TRAV-KEY])) & "~" & Count(IIf(DatePart
('yyyy',[F_ARRV_DAT],1,0)=forms!
DateRangePreviousCurrentYear!CurrYear AND Format
([F_ARRV_DAT],"m") <= YearToDate.MonthMax,[Final_Clean
Tourists Query].[TRAV-KEY])) AS Expr1
SELECT YeartoDate.id, YearToDate.MonthRange, [Final_Clean
Tourists Query].REGIONS3, [Final_Clean Tourists
Query].regionorder
FROM [Final_Clean Tourists Query], YearToDate
WHERE (((DatePart('yyyy',[F_ARRV_DAT],1,0))=forms!
DateRangePreviousCurrentYear!PrevYear Or (DatePart('yyyy',
[F_ARRV_DAT],1,0))=forms!DateRangePreviousCurrentYear!
CurrYear)) AND [Final_Clean Tourists Query].REGIONS3<>''
GROUP BY YeartoDate.id, YearToDate.MonthRange,
[Final_Clean Tourists Query].regionorder, [Final_Clean
Tourists Query].REGIONS3
PIVOT MonthRange in ("January - February", "January -
March", "January - April", "January - May","January -
June","January - July","January - August","January -
September","January - October","January -
November","January - December");

-----Original Message-----
Hello,

How are you today?

With the guidance of this forum I currently generate a
report looking like this:

Code: 
REGIONS3  January	 February          March
2001 2002        2001 2002       2001 2002
USA	  128 399         80  428	   606  58
CANADA	  166 336         294 267	   624  32

REGIONS3  April	           May              June
2001 2002     2001 2002        2001 2002
USA	   128 399        80  428	   606  58
CANADA	   166 336        294 267	   624  32


using the following crosstab:

RegionofResidenceCrosstab-

TRANSFORM "~" & Count(IIf(DatePart('yyyy', [F_ARRV_DAT],1,0)
=forms!DateRangePreviousCurrentYear!PrevYear,[Final_Clean
Tourists Query].[TRAV-KEY])) & "~" & Count(IIf(DatePart
('yyyy',[F_ARRV_DAT],1,0)=forms!
DateRangePreviousCurrentYear!CurrYear,[Final_Clean
Tourists Query].[TRAV-KEY])) AS Expr1
SELECT [Final_Clean Tourists Query].REGIONS3, [Final_Clean
Tourists Query].regionorder
FROM [Final_Clean Tourists Query]
WHERE [Final_Clean Tourists Query].REGIONS3<>''
GROUP BY [Final_Clean Tourists Query].regionorder,
[Final_Clean Tourists Query].REGIONS3
PIVOT Format([F_ARRV_DAT],"mmmm") in
Click to expand...
("January", "February", "March", "April","May","June","July
","August","September","October","November","December");

and this union:

SELECT regionorder, Regions3, 1 As Quarter, January as A,
February as B, March as C FROM RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 2 As Quarter, April, May,
June FROM RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 3 As Quarter, July, August,
September FROM RegionofResidenceCrosstab
UNION ALL SELECT regionorder, Regions3, 4 As Quarter,
October, November, December FROM RegionofResidenceCrosstab;


I now wish to produce a YearToDate Report:

Code: 
REGIONS3  Jan-Feb	 Jan-Mar          Jan-Apr
2001 2002        2001 2002       2001 2002
USA	  1   1            2    2	   4    4
CANADA	  2   2            4    4	   8    8

REGIONS3  Jan-May	 Jan-June          Jan-July
2001 2002     2001 2002        2001 2002
USA	    8   8        16  16	           32   32
CANADA	   16  16        32  32	           64   64

using a similar approach, so I generated the following
crosstab:

TRANSFORM "~" & Count(IIf(DatePart('yyyy', [F_ARRV_DAT],1,0)
=forms!DateRangePreviousCurrentYear!PrevYear,[Final_Clean
Tourists Query].[TRAV-KEY])) & "~" & Count(IIf(DatePart
('yyyy',[F_ARRV_DAT],1,0)=forms!
DateRangePreviousCurrentYear!CurrYear,[Final_Clean
Tourists Query].[TRAV-KEY])) AS Expr1
SELECT YearToDate.id, YearToDate.MonthRange, Format
([F_ARRV_DAT],"mmmm") AS [Month], [Final_Clean Tourists
Query].REGIONS3, [Final_Clean Tourists Query].regionorder
FROM [Final_Clean Tourists Query] INNER JOIN YearToDate ON
[Final_Clean Tourists Query].MonthOrder =
YearToDate.MonthDigit
WHERE (((DatePart('yyyy',[F_ARRV_DAT],1,0))=forms!
DateRangePreviousCurrentYear!PrevYear Or (DatePart('yyyy',
[F_ARRV_DAT],1,0))=forms!DateRangePreviousCurrentYear!
CurrYear)) AND YearToDate.MonthMax < (select MAX
([Final_Clean Tourists Query].MonthOrder)+1 from
[Final_Clean Tourists Query]) AND [Final_Clean Tourists
Query].REGIONS3<>''
GROUP BY YearToDate.id, YearToDate.MonthRange,
[Final_Clean Tourists Query].regionorder, [Final_Clean
Tourists Query].REGIONS3, Format([F_ARRV_DAT],"mmmm")
PIVOT MonthRange in ("January - February", "January -
March", "January - April", "January - May","January -
June","January - July","January - August","January -
September","January - October","January -
November","January - December");

where the YeartoDate table looks like:

id MonthRange MonthDigit MonthMax
1 Jan- Feb 2 2
2 Jan- Mar 3 3
3 January-Apr 4 4



1. I am not getting culmative totals, instead I still get
the information broken down by month. Any idea how to
achieve this or how to generate YeartoDate Queries?

.
Click to expand...
 
Back
Top