TINA - totalling

[Tina Marie]

Hi ... i have a summary report ... but in the summary line, it has grouped
the number of students per course and shown what the course cost was ... so
one field has been grouped, the other is not ... then at the bottom of the
columns, I want to do a grand total per course ...

e.g.


Course 1 5 students each paid $2000
course 2 3 students each paid $1500
TOTAL 8 students $3500

but the 5 and 3 students is a summary line in the report so when I try to do
the TOTAL, I get 5*2000 + 3*1500=$14500 ... I just want $3500?????

 

[Wayne-I-M]

Hi Tina

Have a look at Ken's anwer to your question a few posts below this one.

The basics are that the sum formula you used to get the "each paid" results
should be copied into the group footer.

So if you have
=sum([NumberOfStudents]*[StudentsPaid]) in the detail line then just copy
this same formula into the group footer and you get the result you're looking
for.

By putting it into the foot you will get a sum of the whole group

Good luck

 

[Tina Marie]

Hi Wayne ... I think people are still confused with my question ... what
you're saying is easy and I know how to do that ... but in my case ... its
the course cost that is the problem ... where student count is a roll up
(i.e. the hidden detail line shows say 3 students) ... the course cost is NOT
a roll up ... its simply the course cost ... so ...
when its time to 'sum' the totals for each ... sure ... for students its
just sum(student count) ... easy ... but if you say sum(course cost), it
gives you 3 students times the course cost and I don't want that ... it
wouldn't matter whether I had 3 students or 50 students, the course cost is
still say $2000 ... so when I show within a specific group (let's call it
Region 1) all of the courses taken there ... I want it to sum the course cost
once, not 3 students times course cost ...

Does this help??
--
Thanks!!

T. Marie

Hi Tina

Have a look at Ken's anwer to your question a few posts below this one.

The basics are that the sum formula you used to get the "each paid" results
should be copied into the group footer.

So if you have
=sum([NumberOfStudents]*[StudentsPaid]) in the detail line then just copy
this same formula into the group footer and you get the result you're looking
for.

By putting it into the foot you will get a sum of the whole group

Good luck

--
Wayne
Manchester, England.

Hi ... i have a summary report ... but in the summary line, it has grouped
the number of students per course and shown what the course cost was ... so
one field has been grouped, the other is not ... then at the bottom of the
columns, I want to do a grand total per course ...

e.g.


Course 1 5 students each paid $2000
course 2 3 students each paid $1500
TOTAL 8 students $3500

but the 5 and 3 students is a summary line in the report so when I try to do
the TOTAL, I get 5*2000 + 3*1500=$14500 ... I just want $3500?????

 

[Tina Marie]

Hi Wayne ... I think people are still confused with my question ... what
you're saying is easy and I know how to do that ... but in my case ... its
the course cost that is the problem ... where student count is a roll up
(i.e. the hidden detail line shows say 3 students) ... the course cost is NOT
a roll up ... its simply the course cost ... so ...
when its time to 'sum' the totals for each ... sure ... for students its
just sum(student count) ... easy ... but if you say sum(course cost), it
gives you 3 students times the course cost and I don't want that ... it
wouldn't matter whether I had 3 students or 50 students, the course cost is
still say $2000 ... so when I show within a specific group (let's call it
Region 1) all of the courses taken there ... I want it to sum the course cost
once, not 3 students times course cost ...

Does this help??

--
Thanks!!

T. Marie

Hi Tina

Have a look at Ken's anwer to your question a few posts below this one.

The basics are that the sum formula you used to get the "each paid" results
should be copied into the group footer.

So if you have
=sum([NumberOfStudents]*[StudentsPaid]) in the detail line then just copy
this same formula into the group footer and you get the result you're looking
for.

By putting it into the foot you will get a sum of the whole group

Good luck

--
Wayne
Manchester, England.

Hi ... i have a summary report ... but in the summary line, it has grouped
the number of students per course and shown what the course cost was ... so
one field has been grouped, the other is not ... then at the bottom of the
columns, I want to do a grand total per course ...

e.g.


Course 1 5 students each paid $2000
course 2 3 students each paid $1500
TOTAL 8 students $3500

but the 5 and 3 students is a summary line in the report so when I try to do
the TOTAL, I get 5*2000 + 3*1500=$14500 ... I just want $3500?????

 

[Wayne-I-M]

Hi Tina

Then you need to sum "2" controls.

Sum[Number of sudents]
Sum[Cost per course]

Or something like
="Total "&Sum[StudentsField]& " paid "&Sum[cost of course]

If you have one student per record (which I think you would have) use
Count[Stduents] and not sum.
Of course change the names to what you have on your report - but this will
do it

Good luck


his wuold give you the answer you want - on that point I don't understand
what you're trying tget but you must do, so hey.

 

[Tina Marie]

Hi Wayne ... I fixed most of the fields by doing the totals in the query
before it got to the report ... but ... one field which has to be calculated
as the report runs is the Total Cost ... problem is it has to use an 'iif'
because in one case it calcs one way, and in another case, it calcs another
way ... this is across one row (i.e. a horizontal calc) ... problem ... when
you try and total this particular column (vertical calc) ... you can't just
do a sum([calc]) ... because the row calc is a calc itself ... ????
--
Thanks!!

T. Marie

Hi Tina

Then you need to sum "2" controls.

Sum[Number of sudents]
Sum[Cost per course]

Or something like
="Total "&Sum[StudentsField]& " paid "&Sum[cost of course]

If you have one student per record (which I think you would have) use
Count[Stduents] and not sum.
Of course change the names to what you have on your report - but this will
do it

Good luck


his wuold give you the answer you want - on that point I don't understand
what you're trying tget but you must do, so hey.


--
Wayne
Manchester, England.

Hi ... i have a summary report ... but in the summary line, it has grouped
the number of students per course and shown what the course cost was ... so
one field has been grouped, the other is not ... then at the bottom of the
columns, I want to do a grand total per course ...

e.g.


Course 1 5 students each paid $2000
course 2 3 students each paid $1500
TOTAL 8 students $3500

but the 5 and 3 students is a summary line in the report so when I try to do
the TOTAL, I get 5*2000 + 3*1500=$14500 ... I just want $3500?????