Report by Quarter-layout

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

blinton25

Hello,

How are you today?

I have the following crosstab query:

PARAMETERS PREVIOUSYear Text ( 255 ), CURRENTYear Text (
255 );
TRANSFORM "~" & Count(IIf(DatePart('yyyy',
[F_ARRV_DAT],1,0)=Previo
usYear,
[_Final_Clean Tourists Query].[TRAV-KEY]))
& "~" &
Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=Curren
tYear,
[_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");

which produces a result set looling like

REGIONS3 regionorder January February March
USA 1 ~128~399 ~80~428 ~606~58
CANADA 2 ~166~336 ~294~267 ~624~32

(I took off the Months May to December for clarity)

I wish to utilise the blank space on my report to create a
report which looks 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

I am not sure how to force the second quarter (April to
June) to appear below the first quarter information. Any
idea how to achieve this?
 
Hello,

Well not the same query but I used the technique you
illustrated to build my report. Thanks for the tip, I will
go through the example which you provided.

-----Original Message-----
Your query expression looks familar. Check out the crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. The Crosstab.mdb shows
how to "wrap" columns. The other mdb has a similar crosstab using multiple
values with the "~".

--
Duane Hookom
MS Access MVP


blinton25 said:
Hello,

How are you today?

I have the following crosstab query:

PARAMETERS PREVIOUSYear Text ( 255 ), CURRENTYear Text (
255 );
TRANSFORM "~" & Count(IIf(DatePart('yyyy',
[F_ARRV_DAT],1,0)=Previo
usYear,
[_Final_Clean Tourists Query].[TRAV-KEY]))
& "~" &
Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=Curren
tYear,
[_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");

which produces a result set looling like

REGIONS3 regionorder January February March
USA 1 ~128~399 ~80~428 ~606~58
CANADA 2 ~166~336 ~294~267 ~624~32

(I took off the Months May to December for clarity)

I wish to utilise the blank space on my report to create a
report which looks 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

I am not sure how to force the second quarter (April to
June) to appear below the first quarter information. Any
idea how to achieve this?
Click to expand...


.
Click to expand...
 
Hello,

Been reviewing the Crosstab subreport, but I am still not
sure how you achieve the column wrapping. Could you expand
on this?

-----Original Message-----
Your query expression looks familar. Check out the crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. The Crosstab.mdb shows
how to "wrap" columns. The other mdb has a similar crosstab using multiple
values with the "~".

--
Duane Hookom
MS Access MVP


blinton25 said:
Hello,

How are you today?

I have the following crosstab query:

PARAMETERS PREVIOUSYear Text ( 255 ), CURRENTYear Text (
255 );
TRANSFORM "~" & Count(IIf(DatePart('yyyy',
[F_ARRV_DAT],1,0)=Previo
usYear,
[_Final_Clean Tourists Query].[TRAV-KEY]))
& "~" &
Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=Curren
tYear,
[_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");

which produces a result set looling like

REGIONS3 regionorder January February March
USA 1 ~128~399 ~80~428 ~606~58
CANADA 2 ~166~336 ~294~267 ~624~32

(I took off the Months May to December for clarity)

I wish to utilise the blank space on my report to create a
report which looks 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

I am not sure how to force the second quarter (April to
June) to appear below the first quarter information. Any
idea how to achieve this?
Click to expand...


.
Click to expand...
 
I would add another Row Heading to your crosstab to group by quarter.
Qrtr: (Month([F_ARRV_DAT])-1)\3 + 1
Then set your Column Heading to an expression that finds which month in the
quarter
(Month(F_ARRV_DAT])-1) mod 3

--
Duane Hookom
MS Access MVP


blinton25 said:
Hello,

Been reviewing the Crosstab subreport, but I am still not
sure how you achieve the column wrapping. Could you expand
on this?

-----Original Message-----
Your query expression looks familar. Check out the crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. The Crosstab.mdb shows
how to "wrap" columns. The other mdb has a similar crosstab using multiple
values with the "~".

--
Duane Hookom
MS Access MVP


blinton25 said:
Hello,

How are you today?

I have the following crosstab query:

PARAMETERS PREVIOUSYear Text ( 255 ), CURRENTYear Text (
255 );
TRANSFORM "~" & Count(IIf(DatePart('yyyy',
[F_ARRV_DAT],1,0)=Previo
usYear,
[_Final_Clean Tourists Query].[TRAV-KEY]))
& "~" &
Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=Curren
tYear,
[_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");

which produces a result set looling like

REGIONS3 regionorder January February March
USA 1 ~128~399 ~80~428 ~606~58
CANADA 2 ~166~336 ~294~267 ~624~32

(I took off the Months May to December for clarity)

I wish to utilise the blank space on my report to create a
report which looks 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

I am not sure how to force the second quarter (April to
June) to appear below the first quarter information. Any
idea how to achieve this?
Click to expand...


.
Click to expand...
Click to expand...
 
Hi,

Thanks a lot.
-----Original Message-----
I would add another Row Heading to your crosstab to group by quarter.
Qrtr: (Month([F_ARRV_DAT])-1)\3 + 1
Then set your Column Heading to an expression that finds which month in the
quarter
(Month(F_ARRV_DAT])-1) mod 3

--
Duane Hookom
MS Access MVP


blinton25 said:
Hello,

Been reviewing the Crosstab subreport, but I am still not
sure how you achieve the column wrapping. Could you expand
on this?

-----Original Message-----
Your query expression looks familar. Check out the crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.
Click to expand...
The
Crosstab.mdb shows
how to "wrap" columns. The other mdb has a similar crosstab using multiple
values with the "~".

--
Duane Hookom
MS Access MVP


Hello,

How are you today?

I have the following crosstab query:

PARAMETERS PREVIOUSYear Text ( 255 ), CURRENTYear Text (
255 );
TRANSFORM "~" & Count(IIf(DatePart('yyyy',
[F_ARRV_DAT],1,0)=Previo
usYear,
[_Final_Clean Tourists Query].[TRAV-KEY]))
& "~" &
Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=Curren
tYear,
[_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");

which produces a result set looling like

REGIONS3 regionorder January February March
USA 1 ~128~399 ~80~428 ~606~58
CANADA 2 ~166~336 ~294~267 ~624~32

(I took off the Months May to December for clarity)

I wish to utilise the blank space on my report to create a
report which looks 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

I am not sure how to force the second quarter (April to
June) to appear below the first quarter information. Any
idea how to achieve this?





.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top