If / conditions calculations in a report

[smason]

"hello! i have a report with several fields

i.e

consultant amount jobid

john 1000 j1000

john 2000 j2300

bob 2000 j2000

bob 3000 j4000



in a text box i would like calculate all the 'amount' for 'John' in one box
and bob in another like



total for john is 3300 etc

your help is always appreciated

 

[Stockwell43]

Hi Smason,

There are probably several ways to do it but the way I do it in a report is
as follows:

Click the Group/Sort button in the toolbar and do a Header and Page footer.
In the header, place the Consultant field and sort by that field.

Then in the footer (page footer not report footer), place a textbox open the
properties of the textbox and in the Control Source type =Sum([amount])
assuming the name of your field is amount. That should do it. If you want a
grand total of everything on the report, copy the textbox from the page
footer and place it in the report footer and you should be good.

Hope this helps!

 

[smason]

Hello stockwell43

not sure this is the one for me! basilcally the report is sorted by Jobid i.e

consultant amount jobid
john 1000 1
bob 2000 1
john 1000 2
bob 3000 2

bob total 5000 john total 2000 (this is how i would like it!)




and somewhere in the report (footer) i would like to calculate the total for
each consultant but keep the sort if this makes sense

Hi Smason,

There are probably several ways to do it but the way I do it in a report is
as follows:

Click the Group/Sort button in the toolbar and do a Header and Page footer.
In the header, place the Consultant field and sort by that field.

Then in the footer (page footer not report footer), place a textbox open the
properties of the textbox and in the Control Source type =Sum([amount])
assuming the name of your field is amount. That should do it. If you want a
grand total of everything on the report, copy the textbox from the page
footer and place it in the report footer and you should be good.

Hope this helps!

"hello! i have a report with several fields

i.e

consultant amount jobid

john 1000 j1000

john 2000 j2300

bob 2000 j2000

bob 3000 j4000



in a text box i would like calculate all the 'amount' for 'John' in one box
and bob in another like



total for john is 3300 etc

your help is always appreciated

 

[smason]

hello

i have tried this
=Sum(IIf([consultant]="tessa",Nz([amount],0),0))

but i am getting a type mismatch error!!!??!?!

 

[Stockwell43]

Honestly, I never did it that way before so I am not sure on how to help you.

Sorry.

hello

i have tried this
=Sum(IIf([consultant]="tessa",Nz([amount],0),0))

but i am getting a type mismatch error!!!??!?!

"hello! i have a report with several fields

i.e

consultant amount jobid

john 1000 j1000

john 2000 j2300

bob 2000 j2000

bob 3000 j4000



in a text box i would like calculate all the 'amount' for 'John' in one box
and bob in another like



total for john is 3300 etc

your help is always appreciated

 

[BruceM]

One possibility is that you could group by consultant with a group header
and footer as Stockwell described. In the group footer use =Sum([Amount])
as the control source of an unbound text box.

You could do the same thing with any grouping, such as by JobID.

Your best option may be to create separate reports, one as you have shown
and the other grouped by consultant, with the sum for each consultant in
their footer. Use this report as a subreport of the one sorted as you have
shown.

Hello stockwell43

not sure this is the one for me! basilcally the report is sorted by Jobid
i.e

consultant amount jobid
john 1000 1
bob 2000 1
john 1000 2
bob 3000 2

bob total 5000 john total 2000 (this is how i would like it!)




and somewhere in the report (footer) i would like to calculate the total
for
each consultant but keep the sort if this makes sense

Hi Smason,

There are probably several ways to do it but the way I do it in a report
is
as follows:

Click the Group/Sort button in the toolbar and do a Header and Page
footer.
In the header, place the Consultant field and sort by that field.

Then in the footer (page footer not report footer), place a textbox open
the
properties of the textbox and in the Control Source type =Sum([amount])
assuming the name of your field is amount. That should do it. If you want
a
grand total of everything on the report, copy the textbox from the page
footer and place it in the report footer and you should be good.

Hope this helps!

"hello! i have a report with several fields

i.e

consultant amount jobid

john 1000 j1000

john 2000 j2300

bob 2000 j2000

bob 3000 j4000



in a text box i would like calculate all the 'amount' for 'John' in one
box
and bob in another like



total for john is 3300 etc

your help is always appreciated

 

[Marj]

I think you can do it with the function "SUMIF". However, in your post,
there is a letter j with the amount - that will make in very complicated. To
use sumif, in the target cell, type =SUMIF(the range you have the code in,
like bob)=bob, then the range you have the values in.

 

[KARL DEWEY]

Sounds like your 'Amount' field is a DataType of Text.
--
KARL DEWEY
Build a little - Test a little

hello

i have tried this
=Sum(IIf([consultant]="tessa",Nz([amount],0),0))

but i am getting a type mismatch error!!!??!?!

"hello! i have a report with several fields

i.e

consultant amount jobid

john 1000 j1000

john 2000 j2300

bob 2000 j2000

bob 3000 j4000



in a text box i would like calculate all the 'amount' for 'John' in one box
and bob in another like



total for john is 3300 etc

your help is always appreciated

 

[KARL DEWEY]

In query design view add fields consultant and amount. Click on the Greek
symbol that looks like an 'M' on it's side. Change the GROUP BY to SUM under
the 'amount'.

If you field 'amount' is a text field then you need to edit to read like
this --
Total_Amount: Val([amount])