Group by Quarter and Page Break

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

blinton25

Hello,

How are you today?

I have the following crosstab query:


Code:
--------------------

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


Code:
--------------------

REGIONS3 regionorder January February March April
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)

and I build a report which looks like this:


Code:
--------------------

REGIONS3 January February March April
2001 2002 2001 2002 2001 2002 2001 2002

USA 128 399 80 428 606 58 0 0
CANADA 166 336 294 267 624 32 0 0

--------------------


1. I wish to print only three months on each page of my report (Jan -
Mar, Apr-June etc) but because Access only allows me a horizontal
report size of 22" I can't fit all of my fields on the report (I get as
far as October and then run out of space). How do I force a page break?
I should be able to use a Group by Interval but does that mean I have
to change my query since I can only group on maximum four items?

Feel free to respond with any questions or requests for clarifications.
 
Hello,

How are you today?

A better way of doing this may be to utilise the blank space on my
report to place the information for the second and fourth quarter. So
for example:


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
 
blinton25 said:
A better way of doing this may be to utilise the blank space on my
report to place the information for the second and fourth quarter. So
for example:


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

Any idea how to achieve this?
Click to expand...

This looks like it might be easy, at least compared to your
original question about trying to get horizontal page breaks
(which AFAIK is not feasible).

Try using your crosstab query as the source for another
query that identifies the quarter:

SELECT Region, 1 As Quarter, January As A,
February As B, March As C
FROM thecrosstab
UNION ALL
SELECT Region, 2, April, May, June
FROM thecrosstab
UNION ALL
SELECT Region, 3, July, August, September
FROM thecrosstab
UNION ALL
SELECT Region, 4, October, November, December
FROM thecrosstab
UNION ALL

With the Quarter field in the reports record source query,
the report can group on it to split the data the way you
want.

The Group Header would have an invisible bound text box for
the Quarter and three visible unbound text boxes for the
month names. Some code in the group header's Format event
can fill in the month names based on the quarter field:

Select Case txtQuarter
Case 1
Me.txtMonthA = "January"
Me.txtMonthB = "February"
Me.txtMonthC = "March"
Case 2
Me.txtMonthA = "April"
Me.txtMonthB = "May"
Me.txtMonthC = "June"
Case 3
. . .
End Select

(I'm not sure where the years fit into this??)

The detail section would then have a bound text box for the
region and three more text boxes bound to the A, B and C
fields respectively.
 
Hello,

Thanks, that worked great.

A question, how do I calculate row and column totals, for
example:

REGIONS3 July August September
2001 2002 2001 2002 2001 2002
USA 128 399 80 428 606 58
CANADA 166 336 294 267 624 32
Total 294 735 374 695 1230 90

REGIONS3 October November December
2001 2002 2001 2002 2001 2002
USA 128 399 80 428 606 58
CANADA 166 336 294 267 624 32
Total 294 735 374 695 1230 90

Total
2001 2002
1628 1770
2168 1270
 
blinton25 said:
A question, how do I calculate row and column totals, for
example:

REGIONS3 July August September
2001 2002 2001 2002 2001 2002
USA 128 399 80 428 606 58
CANADA 166 336 294 267 624 32
Total 294 735 374 695 1230 90

REGIONS3 October November December
2001 2002 2001 2002 2001 2002
USA 128 399 80 428 606 58
CANADA 166 336 294 267 624 32
Total 294 735 374 695 1230 90

Total
2001 2002
1628 1770
2168 1270
Click to expand...

The row totals would just be a text box with an expression
like =A + B + C

The column totals would go in text boxes in the quarter
group footer using expressions like =Sum(A)

I can't see how you're getting the year totals, but if those
numbers are just samples instead of the actual totals, then
I will guess the expression could be =Sum(A + B + C)
 
Hello,

The column totals are coming out ok, thanks.

Because the information is grouped by quarter if I do a
text box for the row (=A + B + C) then a total appears at
the end of each row, which is the total for that quarter,
which I don't require.


To clarify, for the row, what I need is a situation where
the total for all the months is displayed. So Jan to Dec
are displayed, and then the total is shown for each
country.
 
Hello,

Don't know if this is possible, but a text box could be
created whose visibility would be set to false.

Global variables would keep track of the total for each
row USArowTotal=(A+B+C+A+B+C+A+B+C+A+B+C) and then the
hidden field be made visible and set to the value of the
calculated row value.

Where would this calculation go, PageHeader?
 
blinton25 said:
The column totals are coming out ok, thanks.
Click to expand...

Glad we got something working ;-)

Because the information is grouped by quarter if I do a
text box for the row (=A + B + C) then a total appears at
the end of each row, which is the total for that quarter,
which I don't require.

To clarify, for the row, what I need is a situation where
the total for all the months is displayed. So Jan to Dec
are displayed, and then the total is shown for each
country.
Click to expand...


I suggest that you create a subreport based on a Totals type
query to present the annual totals for each country.
 
blinton25 said:
Don't know if this is possible, but a text box could be
created whose visibility would be set to false.

Global variables would keep track of the total for each
row USArowTotal=(A+B+C+A+B+C+A+B+C+A+B+C) and then the
hidden field be made visible and set to the value of the
calculated row value.

Where would this calculation go, PageHeader?
Click to expand...


No, that approach will not work. The data in a report is
not guaranteed to be processed in a sequential manner so
calculations done with code in an event procedure will
sometimes include the same values two or more times. Even
if you got it to work for the current version of the report,
an inconsequential change in a future version may shift
things around enough so the calculations no longer work
properly.
 
blinton25 said:
Sorry, I don't understand your suggested solution, could
you expand on it?
Click to expand...


Create a new query that only calculates the year totals per
country (it will either be a Totals or a Crosstab query).
Once you get the query calculating the appropriate values,
create a simple report that displays those values. Then
include this new report as a subreport in the original
report's footer section.
--
Marsh
MVP [MS Access]


 
Hello,

Thanks. I took the original report which already had the
totals on it and used that as the subreport.

One thing, as I move from page to page I am prompted for
the parameters (PreviousYear,CurrentYear). I tried linking
the MasterField, ChildFields but these are not listed.
This only happens when I add the Subreport.

Any idea how to resolve this?
-----Original Message-----
blinton25 said:
Sorry, I don't understand your suggested solution, could
you expand on it?
Click to expand...


Create a new query that only calculates the year totals per
country (it will either be a Totals or a Crosstab query).
Once you get the query calculating the appropriate values,
create a simple report that displays those values. Then
include this new report as a subreport in the original
report's footer section.
--
Marsh
MVP [MS Access]


country.
.
Click to expand...
 
One thing, as I move from page to page
I am prompted for the parameters
(PreviousYear,CurrentYear). I tried linking
the MasterField, ChildFields but these are
not listed. This only happens when I add
the Subreport.
Click to expand...

What do you mean by "I tried linking the MasterField, ChildFields but these
are not listed." They are properties of the Subreport Control, in the Data
tab of the Property Sheet.

Larry Linson
Microsoft Access MVP
 
Hello,

I meant that the parameters PreviousYear and CurrentYear
weren't listed as either a MasterField or Childfield.
Which makes sense given my query doesn't select them, they
are parameters used to specify the desired result.
 
Hello,

I think I need to ask this question in a different way.
There isn't a linkage per se between the Main Report and
Subreport, the link between them is the use of the same
parameters. So can I have the parameters from the main
report being used by the subreport without the subreport
prompting for the parameters again?
 
blinton25 said:
Thanks. I took the original report which already had the
totals on it and used that as the subreport.
Click to expand...

What??? You have a report that includes itself as a
subreport? That makes no sense to me.
One thing, as I move from page to page I am prompted for
the parameters (PreviousYear,CurrentYear). I tried linking
the MasterField, ChildFields but these are not listed.
This only happens when I add the Subreport.
Click to expand...

I thought my subreport suggestion was supposed to provide
your grand totals for each country in the report footer.
Where did you place it? If that's what you wanted, then the
Link Master/Child properties would not come into play.

Even if you did what I thought you were going to do, you
would still be prompted for the query parameters, but only
one additional time. The only way that I can think of to
eliminate the prompts is to enter the the two values on a
form so both queries can get to them without any prompts.

Since I seem to be confused about what you're trying to do,
could you please post back with a sample of how the report
is supposed to look, what values you're having trouble with,
the SQL of the queries involved, and how you go about
opening the report.
--
Marsh
MVP [MS Access].


-----Original Message-----
blinton25 said:
Sorry, I don't understand your suggested solution, could
you expand on it?
Click to expand...


Create a new query that only calculates the year totals per
country (it will either be a Totals or a Crosstab query).
Once you get the query calculating the appropriate values,
create a simple report that displays those values. Then
include this new report as a subreport in the original
report's footer section.
--
Marsh
MVP [MS Access]


-----Original Message-----
blinton25 wrote:

The column totals are coming out ok, thanks.

Glad we got something working ;-)


Because the information is grouped by quarter if I do a
text box for the row (=A + B + C) then a total appears
at the end of each row, which is the total for that
quarter, which I don't require.

To clarify, for the row, what I need is a situation where
the total for all the months is displayed. So Jan to Dec
are displayed, and then the total is shown for each
country.


I suggest that you create a subreport based on a Totals
type query to present the annual totals for each
Click to expand...
country.
.
Click to expand...
Click to expand...
 
Back
Top