sum of a field in a report

[Debbie]

I've also tried to use a field with the running sum
property in the footer of the subreport and then
reference the field in the main report and I can't get
even that to work! The field in the main report keeps
giving a value of 1, regardless of the value of the field
in the sub report.

I'm sure the results we are after should be doable, I
just can't gt it to work!

Can anyone out there help us?

Thanks,
Debbie

 

[Marshall Barton]

The RunningSum text box has to be in the main report. It
would use an expression like:
=subreport.Report.txtsubtotal
to retrieve the subtotal from the subreport. If the
subreport might not have and data, you can use the
expression:
=IIf(subreport.Report.HasData,subreport.Report.txtsubtotal,0)
--
Marsh
MVP [MS Access]

I've also tried to use a field with the running sum
property in the footer of the subreport and then
reference the field in the main report and I can't get
even that to work! The field in the main report keeps
giving a value of 1, regardless of the value of the field
in the sub report.

I'm sure the results we are after should be doable, I
just can't gt it to work!

-----Original Message-----
Hi,
Is it possible to sum a field in a report that is not in the table/query of
the report?

This field was made by a calculation in the sub form. It is calculated for
each record and in the main report I put a field that reffers to that
calculated field of the sub report. I would like to put the sum of that field
in the end of the report.

I tried to write -
=Sum([fieldName]) and it didn't work
How can I do that?

 

[Guest]

I have already tried what you had suggested and had the
same problem - it was one of my earlier 400 attempts

For each record in the main report, my subreport comes up
with a list of relevant records and then determines which
one of 4 values is stored in a particular field (using an
iif statement) for each subreport record. I'm then trying
to count the number of times each of the values occurs in
the subreport. It seems not to matter whether I try to
count within the subreport and just read the value from
the main report, or count from the main report. Al the
combinations I've tried, produce a result of 1 (presuming
valid data is available) regardless of the actual count
of the values.

I'm apparently performing a fundamental blunder, but
can't see what it is.

Any ideas?

-----Original Message-----
The RunningSum text box has to be in the main report. It
would use an expression like:
=subreport.Report.txtsubtotal
to retrieve the subtotal from the subreport. If the
subreport might not have and data, you can use the
expression:
=IIf (subreport.Report.HasData,subreport.Report.txtsubtotal,0)
--
Marsh
MVP [MS Access]

I've also tried to use a field with the running sum
property in the footer of the subreport and then
reference the field in the main report and I can't get
even that to work! The field in the main report keeps
giving a value of 1, regardless of the value of the field
in the sub report.

I'm sure the results we are after should be doable, I
just can't gt it to work!

-----Original Message-----
Hi,
Is it possible to sum a field in a report that is not

in
the table/query of

the report?

This field was made by a calculation in the sub form.

It
is calculated for

each record and in the main report I put a field that reffers to that
calculated field of the sub report. I would like to

put
the sum of that field

in the end of the report.

I tried to write -
=Sum([fieldName]) and it didn't work
How can I do that?

.

 

[Marshall Barton]

First, make the subreport's footer section visible so you
can see what the total is there. Then get that to work
before worrying about accumulating it in the main report.

If you have a problem with that, please post back with more
details so I can unravel what the subreport is doing.

 

[Debbie]

I have tried this as well (nice to know I'm doing logical
things!).

So I've got a subreport that shows four columns with
a "1" in the appropriate column (dependant upon the type
of value in a given field) for each record. This works
fine.
Then I have a running sum field in the footer that
calculates the total for each of the four types (at this
stage it actually only does one of the types, no point
progressing until I can get it working). This also works
correctly.
My main report has a field that "equals" the running sum
field in the subreport and this does NOT work correctly.

So at this time, the subreport shows all the correct
figures: 1's in the correct columns and totals in the
footer. But the main report stubbornly continues to show
me "1" whenever valid data is present instead of the
actual number. Very frustrating, I must say!

Is there some other details I can provide to assist with
the analysis of the problem?

Thanks,
Debbie

-----Original Message-----
First, make the subreport's footer section visible so you
can see what the total is there. Then get that to work
before worrying about accumulating it in the main report.

If you have a problem with that, please post back with more
details so I can unravel what the subreport is doing.
--
Marsh
MVP [MS Access]

I have already tried what you had suggested and had the
same problem - it was one of my earlier 400 attempts

For each record in the main report, my subreport comes up
with a list of relevant records and then determines which
one of 4 values is stored in a particular field (using an
iif statement) for each subreport record. I'm then trying
to count the number of times each of the values occurs in
the subreport. It seems not to matter whether I try to
count within the subreport and just read the value from
the main report, or count from the main report. Al the
combinations I've tried, produce a result of 1 (presuming
valid data is available) regardless of the actual count
of the values.

I'm apparently performing a fundamental blunder, but
can't see what it is.

Any ideas?

(subreport.Report.HasData,subreport.Report.txtsubtotal,0) not
in form.
It

.

 

[Marshall Barton]

So I've got a subreport that shows four columns with
a "1" in the appropriate column (dependant upon the type
of value in a given field) for each record. This works
fine.
Then I have a running sum field in the footer that
calculates the total for each of the four types (at this
stage it actually only does one of the types, no point
progressing until I can get it working). This also works
correctly.
My main report has a field that "equals" the running sum
field in the subreport and this does NOT work correctly.

So at this time, the subreport shows all the correct
figures: 1's in the correct columns and totals in the
footer. But the main report stubbornly continues to show
me "1" whenever valid data is present instead of the
actual number. Very frustrating, I must say!

Is there some other details I can provide to assist with
the analysis of the problem?

I seem to remember that a main report grabs the subreport's
running sum total value before the subreport has calculated
its result. Let's try to find another way to calculate the
subreport totals. I think you can get rid of the running
sum columns in the subreport and just use an expression in
the subreport footer text box. I don't know how you
determine the 1 in some of the records, but maybe this is
close?
=Sum(IIf(typefield = "A", 1, 0)

If you have a lot(?) of types, then you might be better off
creating a Totals type query to calculate all of the totals
and then display them in a separate summary subreport. For
example:

SELECT Type, Count(*) As CountOfType
FROM reportquery
GROUP BY Type

 

[Debbie]

In the footer of the main report, I can use a statement
like:

=IIf(typefield = "A", 1, 0)

However when I put sum() around the expression:

=Sum(IIf(typefield = "A", 1, 0))

and run the report, it prompts me to enter the parameter
for the field name.


I'm reluctant to use a query as you've shown as if I ever
get this report working I hope to be able to run it iwith
date parameters etc and I think there's a fair chance
that I'll botch it up and the query will not take thee
other parameters into account!

Am I trying to do this the hard way? I would have thought
that it would be a reasonably simple thing to calculate
totals within a subreport and transfer them to a main
report? Is there some really simple way to do this that
I've missed?

Marsh, thanks for your advice so far.....I've been
stuffing about with this for weeks, now I feel like it's
progressing.

 

[Marshall Barton]

Comments inline below
--
Marsh
MVP [MS Access]

In the footer of the main report, I can use a statement
like:

=IIf(typefield = "A", 1, 0)

However when I put sum() around the expression:

=Sum(IIf(typefield = "A", 1, 0))

I thought the total was for data in the subreport??

I had suggested earlier that that expression should be used
in a text box in the subreport's footer section, did I
misunderstand what you're doing?

and run the report, it prompts me to enter the parameter
for the field name.

This probably means that the main report has a control (text
box?) named typefield, but the main report's recordsource
table/query does not have a field named typefield. The
aggregate functions (Count, Sum, etc) only operate on fields
in the recordsource of the form in which the function
appears. As I understood it from your previous posts, the
type field is in your subreport's recordsource query.

I'm reluctant to use a query as you've shown as if I ever
get this report working I hope to be able to run it iwith
date parameters etc and I think there's a fair chance
that I'll botch it up and the query will not take thee
other parameters into account!

Fear is no excuse ;-)

There are several ways to get the queries to use the same
parameters. You should note that I used your original query
in the From clause for my example query, just in case you
did have a complex set of criteria.

Am I trying to do this the hard way? I would have thought
that it would be a reasonably simple thing to calculate
totals within a subreport and transfer them to a main
report? Is there some really simple way to do this that
I've missed?

I think you are missing the easy way, but it's only a matter
of me explaining it so that you can get a grasp on how it's
done. Let me try another way.

Main report:
Subreport:
txtType: Type
Subreport Footer:
txtCountA: =Sum(IIf(Type = "A", 1, 0))
MainReport Footer:
txtTotalA: =subreport.Report.txtCountA

I hope this helps clarify how yhis sort of thing works.

 

[Debie]

Ahhhhhhh, the fog clears.......

By way of clarification:
My database is trying to track consultations performed by
a medical professional.
A patient may have more than one service performed during
a given consultation.
So the relvant tables contain information relating to:
1) Item (Item#, Description ,Type etc)
2) ConsultItems (Consult#, Item#, Cost etc)
3) A consultation (Consult#, Patient#, Doctor#, Date etc)
4) A patient (Patient#, Name, address etc)


What I'm trying to do is provide a summary of services
rendered.

My main report has a record for each consultation. The
subreport (linked on Consult#) provides records for each
service performed during that consultation. My client
wants to see four columns with a "1" in the relevant one
for the type of service provided - of which there are
four (naturally).

My problem seems to lie with adding up values that are
derived from a field not in the recordsource.

My subreport uses ConsultItems as it's recordsource, but
the Type field is in Items. So in my subreport I've set
Item# up as a combo box and put my required fields in the
rowsource. Then when I refer to one of these fields I do
so like: [Item #].column(2)

So my txt box that works out whether or not this
particular service is of type "General" looks like this:
=IIf([Item #].column(2)="General",1,Null)

So, even in my subreport, I'm unable to perform the sum()
calculation that you've recommended. And it's apparently
all to do with the types of fields that I've used.

The question now is "so how do I fix it all?"

Some more background info:
My Items table contains ref data for all of the items
including a series of costs for each item depending on
the variety of client (eg full paying - concession etc).
It also contains the description of the item.

The ConsultItems tables is the keeper of info relating to
service items for a given consult. So it has links to
both Item# and Consult#. It also contains a field for
which Side of the body the service was performed on
(can't be stored in Items, because each Item# must relate
to only one service and these are dictated by the health
system), and another for Cost (calculated depending on
Item# and Patient variety).

Do I need to rethink my tables or can you see another way
around the reporting issue?

We're really moving now, you've identified the actual
cause of the problem!

Thanks again,
Debbie

-----Original Message-----
Comments inline below
--
Marsh
MVP [MS Access]

In the footer of the main report, I can use a statement
like:

=IIf(typefield = "A", 1, 0)

However when I put sum() around the expression:

=Sum(IIf(typefield = "A", 1, 0))

I thought the total was for data in the subreport??

I had suggested earlier that that expression should be used
in a text box in the subreport's footer section, did I
misunderstand what you're doing?

and run the report, it prompts me to enter the parameter
for the field name.

This probably means that the main report has a control (text
box?) named typefield, but the main report's recordsource
table/query does not have a field named typefield. The
aggregate functions (Count, Sum, etc) only operate on fields
in the recordsource of the form in which the function
appears. As I understood it from your previous posts, the
type field is in your subreport's recordsource query.

I'm reluctant to use a query as you've shown as if I ever
get this report working I hope to be able to run it iwith
date parameters etc and I think there's a fair chance
that I'll botch it up and the query will not take thee
other parameters into account!

Fear is no excuse ;-)

There are several ways to get the queries to use the same
parameters. You should note that I used your original query
in the From clause for my example query, just in case you
did have a complex set of criteria.

Am I trying to do this the hard way? I would have thought
that it would be a reasonably simple thing to calculate
totals within a subreport and transfer them to a main
report? Is there some really simple way to do this that
I've missed?

I think you are missing the easy way, but it's only a matter
of me explaining it so that you can get a grasp on how it's
done. Let me try another way.

Main report:
Subreport:
txtType: Type
Subreport Footer:
txtCountA: =Sum(IIf(Type = "A", 1, 0))
MainReport Footer:
txtTotalA: =subreport.Report.txtCountA

I hope this helps clarify how yhis sort of thing works.

Marsh, thanks for your advice so far.....I've been
stuffing about with this for weeks, now I feel like it's
progressing.
calculate
the expression
in better
off subreport.
For

.

 

[Marshall Barton]

Ahhhhhhh, the fog clears.......

By way of clarification:
My database is trying to track consultations performed by
a medical professional.
A patient may have more than one service performed during
a given consultation.
So the relvant tables contain information relating to:
1) Item (Item#, Description ,Type etc)
2) ConsultItems (Consult#, Item#, Cost etc)
3) A consultation (Consult#, Patient#, Doctor#, Date etc)
4) A patient (Patient#, Name, address etc)


What I'm trying to do is provide a summary of services
rendered.

My main report has a record for each consultation. The
subreport (linked on Consult#) provides records for each
service performed during that consultation. My client
wants to see four columns with a "1" in the relevant one
for the type of service provided - of which there are
four (naturally).

My problem seems to lie with adding up values that are
derived from a field not in the recordsource.

My subreport uses ConsultItems as it's recordsource, but
the Type field is in Items. So in my subreport I've set
Item# up as a combo box and put my required fields in the
rowsource. Then when I refer to one of these fields I do
so like: [Item #].column(2)

So my txt box that works out whether or not this
particular service is of type "General" looks like this:
=IIf([Item #].column(2)="General",1,Null)

So, even in my subreport, I'm unable to perform the sum()
calculation that you've recommended. And it's apparently
all to do with the types of fields that I've used.

The question now is "so how do I fix it all?"

Some more background info:
My Items table contains ref data for all of the items
including a series of costs for each item depending on
the variety of client (eg full paying - concession etc).
It also contains the description of the item.

The ConsultItems tables is the keeper of info relating to
service items for a given consult. So it has links to
both Item# and Consult#. It also contains a field for
which Side of the body the service was performed on
(can't be stored in Items, because each Item# must relate
to only one service and these are dictated by the health
system), and another for Cost (calculated depending on
Item# and Patient variety).

Do I need to rethink my tables or can you see another way
around the reporting issue?

This explanation definitely clears the air ;-)

The problem here is that you're using a combo box to get the
item info. This may (or may not) be appropriate in a data
entry form, but not in a report.

The way to get the needed data into the report is to use a
query for the subreport's record source. The query would
Join the Items table to the ConsultItem table so the fields
in both tables will available in the report.

SELECT ConsultItems.*, Item.*
FROM ConsultItems INNER JOIN Item
ON ConsultItems.[Item#] = Item.[Item#]

The detail text boxes in the four columns can then use the
expression:
=IIf(Type = "General",1,Null)
and the total text boxes in the subreport's footer can use:
=Sum(IIf(Type = "General",1,Null))

Now the main report can refer to the subreport footer text
box to get the total.

I'm not feeling particularly comfortable using expressions
that include literal values such as "General". I think you
should explore using a crosstab query for the subreport.
This would then provide you with the four columns so you
would not need to use IIf. Try playing around with crosstab
queries based on the query I posted above and see if it
would be useful in your situation.

 

[Debbie]

Woo Hoo, we have liftoff!!!

I changed the subreports recordsource to the query as you
suggested. Then changed the combo box to a straight text
box, and all the relevant fields were then able to
directly reference their source. So I could use my "sum
of iif" statements in the subreport footer to calculate
the totals. Then in my main report I used runningsum text
fields in the detail section to refer to the values in
the subreport. The totals for the footer were
just "equals" each of the runningsum fields.

And voila.....it works!!

Point taken about avoiding using literals wherever
possible. I'll have a little play with a crosstab query
when I get a moment and see if I can find a more flexible
approach.

I really appreciate your help with this issue. I was at
my wits end and was becoming seriously frustrated with
the whole deal (as was my client!).

Many thanks once again, for prompt replies and
professional advice.

Regards,
Debbie

-----Original Message-----

Ahhhhhhh, the fog clears.......

By way of clarification:
My database is trying to track consultations performed by
a medical professional.
A patient may have more than one service performed during
a given consultation.
So the relvant tables contain information relating to:
1) Item (Item#, Description ,Type etc)
2) ConsultItems (Consult#, Item#, Cost etc)
3) A consultation (Consult#, Patient#, Doctor#, Date etc)
4) A patient (Patient#, Name, address etc)


What I'm trying to do is provide a summary of services
rendered.

My main report has a record for each consultation. The
subreport (linked on Consult#) provides records for each
service performed during that consultation. My client
wants to see four columns with a "1" in the relevant one
for the type of service provided - of which there are
four (naturally).

My problem seems to lie with adding up values that are
derived from a field not in the recordsource.

My subreport uses ConsultItems as it's recordsource, but
the Type field is in Items. So in my subreport I've set
Item# up as a combo box and put my required fields in the
rowsource. Then when I refer to one of these fields I do
so like: [Item #].column(2)

So my txt box that works out whether or not this
particular service is of type "General" looks like this:
=IIf([Item #].column(2)="General",1,Null)

So, even in my subreport, I'm unable to perform the sum ()
calculation that you've recommended. And it's apparently
all to do with the types of fields that I've used.

The question now is "so how do I fix it all?"

Some more background info:
My Items table contains ref data for all of the items
including a series of costs for each item depending on
the variety of client (eg full paying - concession etc).
It also contains the description of the item.

The ConsultItems tables is the keeper of info relating to
service items for a given consult. So it has links to
both Item# and Consult#. It also contains a field for
which Side of the body the service was performed on
(can't be stored in Items, because each Item# must relate
to only one service and these are dictated by the health
system), and another for Cost (calculated depending on
Item# and Patient variety).

Do I need to rethink my tables or can you see another way
around the reporting issue?

This explanation definitely clears the air ;-)

The problem here is that you're using a combo box to get the
item info. This may (or may not) be appropriate in a data
entry form, but not in a report.

The way to get the needed data into the report is to use a
query for the subreport's record source. The query would
Join the Items table to the ConsultItem table so the fields
in both tables will available in the report.

SELECT ConsultItems.*, Item.*
FROM ConsultItems INNER JOIN Item
ON ConsultItems.[Item#] = Item.[Item#]

The detail text boxes in the four columns can then use the
expression:
=IIf(Type = "General",1,Null)
and the total text boxes in the subreport's footer can use:
=Sum(IIf(Type = "General",1,Null))

Now the main report can refer to the subreport footer text
box to get the total.

I'm not feeling particularly comfortable using expressions
that include literal values such as "General". I think you
should explore using a crosstab query for the subreport.
This would then provide you with the four columns so you
would not need to use IIf. Try playing around with crosstab
queries based on the query I posted above and see if it
would be useful in your situation.