Grouping for report

  • Thread starter Thread starter rob p
  • Start date Start date
R

rob p

I posted in queries previously but it never showed up. Anyway, one column in
table / query is earning code. Next column is amount. Sample of codes could
be like 01, 02, 03, and 04. All have different amounts in column two.

How can I on a report show the sum of the different codes instead of a sum
of the whole amount?

Example again: I would want sum of 01. I would want sum of 02 and 03. I
would want sum of 04.

Thanks.
 
One method is to use hard-coded expressions in your footers:
=Abs( Sum( [EarningCode]="01" * [Amount] ) )
=Abs( Sum( [EarningCode]="02" * [Amount] ) )
etc

If you may add more EarningCodes in the future, you should consider a
subreport based on a totals query that groups by EarningCode and totals
Amount.
 
Thanks very much Duane. This really helps.
rob

Duane Hookom said:
One method is to use hard-coded expressions in your footers:
=Abs( Sum( [EarningCode]="01" * [Amount] ) )
=Abs( Sum( [EarningCode]="02" * [Amount] ) )
etc

If you may add more EarningCodes in the future, you should consider a
subreport based on a totals query that groups by EarningCode and totals
Amount.

--
Duane Hookom
MS Access MVP


rob p said:
I posted in queries previously but it never showed up. Anyway, one
Click to expand...
column
in
table / query is earning code. Next column is amount. Sample of codes could
be like 01, 02, 03, and 04. All have different amounts in column two.

How can I on a report show the sum of the different codes instead of a sum
of the whole amount?

Example again: I would want sum of 01. I would want sum of 02 and 03. I
would want sum of 04.

Thanks.
Click to expand...
Click to expand...
 
One additional question: Where is there documentation on Abs? Help has next
to nothing (in access?). Any examples?
thanks.

rob p said:
Thanks very much Duane. This really helps.
rob

Duane Hookom said:
One method is to use hard-coded expressions in your footers:
=Abs( Sum( [EarningCode]="01" * [Amount] ) )
=Abs( Sum( [EarningCode]="02" * [Amount] ) )
etc

If you may add more EarningCodes in the future, you should consider a
subreport based on a totals query that groups by EarningCode and totals
Amount.

--
Duane Hookom
MS Access MVP


rob p said:
I posted in queries previously but it never showed up. Anyway, one
Click to expand...
column
in
table / query is earning code. Next column is amount. Sample of codes could
be like 01, 02, 03, and 04. All have different amounts in column two.

How can I on a report show the sum of the different codes instead of a sum
of the whole amount?

Example again: I would want sum of 01. I would want sum of 02 and 03. I
would want sum of 04.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
rob p said:
One additional question: Where is there documentation on Abs? Help has next
to nothing (in access?). Any examples?
thanks.
Click to expand...

From Access 97 Help file.
********************

Abs Function
**********

Returns a value of the same type that is passed to it specifying the absolute value
of a number.

Syntax

Abs(number)

The required number argument can be any valid numeric expression. If number contains
Null, Null is returned; if it is an uninitialized variable, zero is returned.

Remarks

The absolute value of a number is its unsigned magnitude. For example, ABS(-1) and
ABS(1) both return 1.
 
New Reply: It looked to me like it would work but I only get zeros. I do
have a field EarningCode and also Amount. The earning code is "01". Help
doesn't show sum with options like your sample. So I then have the question
of what exactly the statement is doing? The "*" after "01" is ????
thanks much.
rob

rob p said:
Thanks very much Duane. This really helps.
rob

Duane Hookom said:
One method is to use hard-coded expressions in your footers:
=Abs( Sum( [EarningCode]="01" * [Amount] ) )
=Abs( Sum( [EarningCode]="02" * [Amount] ) )
etc

If you may add more EarningCodes in the future, you should consider a
subreport based on a totals query that groups by EarningCode and totals
Amount.

--
Duane Hookom
MS Access MVP


rob p said:
I posted in queries previously but it never showed up. Anyway, one
Click to expand...
column
in
table / query is earning code. Next column is amount. Sample of codes could
be like 01, 02, 03, and 04. All have different amounts in column two.

How can I on a report show the sum of the different codes instead of a sum
of the whole amount?

Example again: I would want sum of 01. I would want sum of 02 and 03. I
would want sum of 04.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top