Calculate Total based on detail controls

B

blinton25

Hello,

I have a report which I use to return results grouped by
Quarter:

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;

gives me a result set like:

regionorder Regions3 Quarter A B C
1 USA 1 128 80 606
2 CANADA 1 166 294 624
1 USA 2 124 80 606
2 CANADA 2 162 294 624

In the detail section I place text controls containing A,
B, C and the report is formatted by quarter (I have a
quarter header).

I wish to obtain a total for the entire year, based on
summing the values which are displayed in the detail
section but I am not sure how to go about this.
 
D

Duane Hookom

Have you tried adding a text box in the report footer with a control source
of:
=Sum([A])

You dont' need the " As Quarter " in any of the UNIONs:
SELECT regionorder, Regions3, 1 As Quarter, January as
A, February as B, March as C FROM
RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 2 , April, May,
June FROM RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 3 , July, August,
September FROM RegionofResidenceCrosstab
UNION ALL SELECT regionorder, Regions3, 4 ,
October, November, December FROM
RegionofResidenceCrosstab;
 
B

blinton25

Hello,

When I do that I only get a single value, where ideally I
would want a total for USA, Canada etc.

USA 814
Canada 1084
USA 810
Canada 1080


-----Original Message-----
Have you tried adding a text box in the report footer with a control source
of:
=Sum([A])

You dont' need the " As Quarter " in any of the UNIONs:
SELECT regionorder, Regions3, 1 As Quarter, January as
A, February as B, March as C FROM
RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 2 , April, May,
June FROM RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 3 , July, August,
September FROM RegionofResidenceCrosstab
UNION ALL SELECT regionorder, Regions3, 4 ,
October, November, December FROM
RegionofResidenceCrosstab;

--
Duane Hookom
MS Access MVP


blinton25 said:
Hello,

I have a report which I use to return results grouped by
Quarter:

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;

gives me a result set like:

regionorder Regions3 Quarter A B C
1 USA 1 128 80 606
2 CANADA 1 166 294 624
1 USA 2 124 80 606
2 CANADA 2 162 294 624

In the detail section I place text controls containing A,
B, C and the report is formatted by quarter (I have a
quarter header).

I wish to obtain a total for the entire year, based on
summing the values which are displayed in the detail
section but I am not sure how to go about this.
Click to expand...


.
Click to expand...
 
D

Duane Hookom

First of all, I would modify the crosstab to eliminate the UNION.
Add a new field as a row header
Quarter:Format([DateField],"q")
Set the Column Heading to
ColHead: Chr(((Month([DateField])-1) Mod 3)+65)

To get group totals in a footer in your report, you may need to create a
totals query as the record source for a subreport.

--
Duane Hookom
MS Access MVP
--

blinton25 said:
Hello,

When I do that I only get a single value, where ideally I
would want a total for USA, Canada etc.

USA 814
Canada 1084
USA 810
Canada 1080


-----Original Message-----
Have you tried adding a text box in the report footer with a control source
of:
=Sum([A])

You dont' need the " As Quarter " in any of the UNIONs:
SELECT regionorder, Regions3, 1 As Quarter, January as
A, February as B, March as C FROM
RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 2 , April, May,
June FROM RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 3 , July, August,
September FROM RegionofResidenceCrosstab
UNION ALL SELECT regionorder, Regions3, 4 ,
October, November, December FROM
RegionofResidenceCrosstab;

--
Duane Hookom
MS Access MVP


blinton25 said:
Hello,

I have a report which I use to return results grouped by
Quarter:

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;

gives me a result set like:

regionorder Regions3 Quarter A B C
1 USA 1 128 80 606
2 CANADA 1 166 294 624
1 USA 2 124 80 606
2 CANADA 2 162 294 624

In the detail section I place text controls containing A,
B, C and the report is formatted by quarter (I have a
quarter header).

I wish to obtain a total for the entire year, based on
summing the values which are displayed in the detail
section but I am not sure how to go about this.
Click to expand...


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

blinton25

Hello,

I currently use a totals query with a subreport to
achieve the totals. The issue is the performance, in
effect I wind up running the same query twice.



use a Subreport to
-----Original Message-----
First of all, I would modify the crosstab to eliminate the UNION.
Add a new field as a row header
Quarter:Format([DateField],"q")
Set the Column Heading to
ColHead: Chr(((Month([DateField])-1) Mod 3)+65)

To get group totals in a footer in your report, you may need to create a
totals query as the record source for a subreport.

--
Duane Hookom
MS Access MVP
--

blinton25 said:
Hello,

When I do that I only get a single value, where ideally I
would want a total for USA, Canada etc.

USA 814
Canada 1084
USA 810
Canada 1080


-----Original Message-----
Have you tried adding a text box in the report footer with a control source
of:
=Sum([A])

You dont' need the " As Quarter " in any of the UNIONs:
SELECT regionorder, Regions3, 1 As Quarter, January as
A, February as B, March as C FROM
RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 2 , April, May,
June FROM RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 3 , July, August,
September FROM RegionofResidenceCrosstab
UNION ALL SELECT regionorder, Regions3, 4 ,
October, November, December FROM
RegionofResidenceCrosstab;

--
Duane Hookom
MS Access MVP


Hello,

I have a report which I use to return results
Click to expand...
grouped
by
Quarter:

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;

gives me a result set like:

regionorder Regions3 Quarter A B C
1 USA 1 128 80 606
2 CANADA 1 166 294 624
1 USA 2 124 80 606
2 CANADA 2 162 294 624

In the detail section I place text controls
Click to expand...
containing
A,
B, C and the report is formatted by quarter (I have a
quarter header).

I wish to obtain a total for the entire year, based on
summing the values which are displayed in the detail
section but I am not sure how to go about this.


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


.
Click to expand...
 
D

Duane Hookom

Are you still needing assistance?

--
Duane Hookom
MS Access MVP


blinton25 said:
Hello,

I currently use a totals query with a subreport to
achieve the totals. The issue is the performance, in
effect I wind up running the same query twice.



use a Subreport to
-----Original Message-----
First of all, I would modify the crosstab to eliminate the UNION.
Add a new field as a row header
Quarter:Format([DateField],"q")
Set the Column Heading to
ColHead: Chr(((Month([DateField])-1) Mod 3)+65)

To get group totals in a footer in your report, you may need to create a
totals query as the record source for a subreport.

--
Duane Hookom
MS Access MVP
--

blinton25 said:
Hello,

When I do that I only get a single value, where ideally I
would want a total for USA, Canada etc.

USA 814
Canada 1084
USA 810
Canada 1080



-----Original Message-----
Have you tried adding a text box in the report footer
with a control source
of:
=Sum([A])

You dont' need the " As Quarter " in any of the UNIONs:
SELECT regionorder, Regions3, 1 As Quarter, January as
A, February as B, March as C FROM
RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 2 , April, May,
June FROM RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 3 , July, August,
September FROM RegionofResidenceCrosstab
UNION ALL SELECT regionorder, Regions3, 4 ,
October, November, December FROM
RegionofResidenceCrosstab;

--
Duane Hookom
MS Access MVP


Hello,

I have a report which I use to return results grouped
by
Quarter:

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;

gives me a result set like:

regionorder Regions3 Quarter A B C
1 USA 1 128 80 606
2 CANADA 1 166 294 624
1 USA 2 124 80 606
2 CANADA 2 162 294 624

In the detail section I place text controls containing
A,
B, C and the report is formatted by quarter (I have a
quarter header).

I wish to obtain a total for the entire year, based on
summing the values which are displayed in the detail
section but I am not sure how to go about this.


.
Click to expand...


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Year to Date Report 1
Reference Main Report Crosstab Query from Subreport 0
Report by Quarter-layout 5
Group by Quarter and Page Break 15
Function for returning quarter based on current date? 4
Change Sort Order; Assign Number to Specific Records. 2
Township Range map in Excel 3
Query summing on one line 4

Top