Total from a sub-report in report footer based on criteria

[Guest]

G'day Helpers,

I have a report from a table that contains details of advertising campaigns
we have undertaken and I am now attempting to measure the number of enquiries
received on a state by state basis.

Details of the enquiries (such as enquirers name, state of interest etc) are
entered into the "Prospect Tracker Table".

My report, as stated, comes from the advertising table and then I have a
sub-report from the Prospect Tracker Table and in the report footer of the
sub-report the following expression, =Sum(Abs([state of interest]="qld")),
provides me with a count of the number of enquires received for a particular
advert from Qld.

Now, in the footer of the main report where I list the totals for all advert
categories, such as cost etc I would like a total of all enquiries from qld.

I have attempted a variety of expressions that I thought would work, but
nothing does.

Any ideas?

 

[Allen Browne]

So you want the total from a text box in the subreport to show on the main
report?

See:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html

The article explains how to refer to the Report in the subreport control,
and handle the case where the subreport has no data.

 

[Guest]

Yesw, but no. I do know how to refer to a text box from a subreport on the
main report, in a sense what I am trying to do is total a text box (which is
a subtotal) on the main report.

In the group footer of the subreport I have a total for the number of
enquiries from each state for each advert type, so this is a summary for each
advert type.

What I want in the report footer of the main report is the total enquiries
per state, regardless of advert type.

So you want the total from a text box in the subreport to show on the main
report?

See:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html

The article explains how to refer to the Report in the subreport control,
and handle the case where the subreport has no data.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

G'day Helpers,

I have a report from a table that contains details of advertising
campaigns
we have undertaken and I am now attempting to measure the number of
enquiries
received on a state by state basis.

Details of the enquiries (such as enquirers name, state of interest etc)
are
entered into the "Prospect Tracker Table".

My report, as stated, comes from the advertising table and then I have a
sub-report from the Prospect Tracker Table and in the report footer of the
sub-report the following expression, =Sum(Abs([state of interest]="qld")),
provides me with a count of the number of enquires received for a
particular
advert from Qld.

Now, in the footer of the main report where I list the totals for all
advert
categories, such as cost etc I would like a total of all enquiries from
qld.

I have attempted a variety of expressions that I thought would work, but
nothing does.

Any ideas?

 

[Allen Browne]

Flynny, I'm not completely clear here.

If you want a count for all categories (not as limited in the subreport or
the report's filter), you could use DCount() to get the number directly from
the source talbe. The Control Source of the text box would be something like
this:
=DCount("*", "Table1")

If you have a subreport that show up once for each state, and you want the
total for all states at the end (perhaps limited to whatever is in the
various subreports are), you could put the text box in the same section as
the subreport, set its control source as suggested, and set its Running Sum
property to Over All so it accumulates the totals from all states. Then in
the Report Footer section, you could display this total by referrring to the
(hidden) running subm text box:
=[MyRunningSumTextBox]

Post back if that's still not what you need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yesw, but no. I do know how to refer to a text box from a subreport on the
main report, in a sense what I am trying to do is total a text box (which
is
a subtotal) on the main report.

In the group footer of the subreport I have a total for the number of
enquiries from each state for each advert type, so this is a summary for
each
advert type.

What I want in the report footer of the main report is the total enquiries
per state, regardless of advert type.

So you want the total from a text box in the subreport to show on the
main
report?

See:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html

The article explains how to refer to the Report in the subreport control,
and handle the case where the subreport has no data.

G'day Helpers,

I have a report from a table that contains details of advertising
campaigns
we have undertaken and I am now attempting to measure the number of
enquiries
received on a state by state basis.

Details of the enquiries (such as enquirers name, state of interest
etc)
are
entered into the "Prospect Tracker Table".

My report, as stated, comes from the advertising table and then I have
a
sub-report from the Prospect Tracker Table and in the report footer of
the
sub-report the following expression, =Sum(Abs([state of
interest]="qld")),
provides me with a count of the number of enquires received for a
particular
advert from Qld.

Now, in the footer of the main report where I list the totals for all
advert
categories, such as cost etc I would like a total of all enquiries from
qld.

I have attempted a variety of expressions that I thought would work,
but
nothing does.

 

[Guest]

G'day Allen,

Thanks for the help thus far.

I think the problem stems from the fact that I am wishing to count a text
value within a particular column (field). So I have a column 'state of
interest' in my Prospect Tracker Table. Entries in this field can be any one
of all states, e.g. qld, vic etc.

In order to get the subtotals in the subreport I use this expression in a
text box: =Sum(Abs([state of interest]="qld")). Dcount is something I did
try, but if I use Dcount("[state of interest]","[prospect tracker]","[state
of interest]="qld"") it doesnt work due to the quotation marks. Placing the
criteria part of the expression in brackets () doesnt help either.

Thoughts?

Flynny, I'm not completely clear here.

If you want a count for all categories (not as limited in the subreport or
the report's filter), you could use DCount() to get the number directly from
the source talbe. The Control Source of the text box would be something like
this:
=DCount("*", "Table1")

If you have a subreport that show up once for each state, and you want the
total for all states at the end (perhaps limited to whatever is in the
various subreports are), you could put the text box in the same section as
the subreport, set its control source as suggested, and set its Running Sum
property to Over All so it accumulates the totals from all states. Then in
the Report Footer section, you could display this total by referrring to the
(hidden) running subm text box:
=[MyRunningSumTextBox]

Post back if that's still not what you need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yesw, but no. I do know how to refer to a text box from a subreport on the
main report, in a sense what I am trying to do is total a text box (which
is
a subtotal) on the main report.

In the group footer of the subreport I have a total for the number of
enquiries from each state for each advert type, so this is a summary for
each
advert type.

What I want in the report footer of the main report is the total enquiries
per state, regardless of advert type.

So you want the total from a text box in the subreport to show on the
main
report?

See:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html

The article explains how to refer to the Report in the subreport control,
and handle the case where the subreport has no data.

G'day Helpers,

I have a report from a table that contains details of advertising
campaigns
we have undertaken and I am now attempting to measure the number of
enquiries
received on a state by state basis.

Details of the enquiries (such as enquirers name, state of interest
etc)
are
entered into the "Prospect Tracker Table".

My report, as stated, comes from the advertising table and then I have
a
sub-report from the Prospect Tracker Table and in the report footer of
the
sub-report the following expression, =Sum(Abs([state of
interest]="qld")),
provides me with a count of the number of enquires received for a
particular
advert from Qld.

Now, in the footer of the main report where I list the totals for all
advert
categories, such as cost etc I would like a total of all enquiries from
qld.

I have attempted a variety of expressions that I thought would work,
but
nothing does.

 

[Allen Browne]

If the quotes are the problem, try:
=Dcount("[state of interest]","[prospect tracker]","[state of
interest]=""qld""")
or even:
=Dcount("[state of interest]","[prospect tracker]","[state of
interest]='qld'")

You should be able to use:
=Dcount("*","[prospect tracker]","[state of interest]=""qld""")

Explanation:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

G'day Allen,

Thanks for the help thus far.

I think the problem stems from the fact that I am wishing to count a text
value within a particular column (field). So I have a column 'state of
interest' in my Prospect Tracker Table. Entries in this field can be any
one
of all states, e.g. qld, vic etc.

In order to get the subtotals in the subreport I use this expression in a
text box: =Sum(Abs([state of interest]="qld")). Dcount is something I did
try, but if I use Dcount("[state of interest]","[prospect
tracker]","[state
of interest]="qld"") it doesnt work due to the quotation marks. Placing
the
criteria part of the expression in brackets () doesnt help either.

Thoughts?

Flynny, I'm not completely clear here.

If you want a count for all categories (not as limited in the subreport
or
the report's filter), you could use DCount() to get the number directly
from
the source talbe. The Control Source of the text box would be something
like
this:
=DCount("*", "Table1")

If you have a subreport that show up once for each state, and you want
the
total for all states at the end (perhaps limited to whatever is in the
various subreports are), you could put the text box in the same section
as
the subreport, set its control source as suggested, and set its Running
Sum
property to Over All so it accumulates the totals from all states. Then
in
the Report Footer section, you could display this total by referrring to
the
(hidden) running subm text box:
=[MyRunningSumTextBox]

Post back if that's still not what you need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yesw, but no. I do know how to refer to a text box from a subreport on
the
main report, in a sense what I am trying to do is total a text box
(which
is
a subtotal) on the main report.

In the group footer of the subreport I have a total for the number of
enquiries from each state for each advert type, so this is a summary
for
each
advert type.

What I want in the report footer of the main report is the total
enquiries
per state, regardless of advert type.



:

So you want the total from a text box in the subreport to show on the
main
report?

See:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html

The article explains how to refer to the Report in the subreport
control,
and handle the case where the subreport has no data.

G'day Helpers,

I have a report from a table that contains details of advertising
campaigns
we have undertaken and I am now attempting to measure the number of
enquiries
received on a state by state basis.

Details of the enquiries (such as enquirers name, state of interest
etc)
are
entered into the "Prospect Tracker Table".

My report, as stated, comes from the advertising table and then I
have
a
sub-report from the Prospect Tracker Table and in the report footer
of
the
sub-report the following expression, =Sum(Abs([state of
interest]="qld")),
provides me with a count of the number of enquires received for a
particular
advert from Qld.

Now, in the footer of the main report where I list the totals for
all
advert
categories, such as cost etc I would like a total of all enquiries
from
qld.

I have attempted a variety of expressions that I thought would work,
but
nothing does.

 

[Guest]

G'day Allen,

Yes, you f-ing beauty, the quotes within quotes was the problem and you have
fixed it for me. Thank you fellow Sandgroper.

Cheers,

If the quotes are the problem, try:
=Dcount("[state of interest]","[prospect tracker]","[state of
interest]=""qld""")
or even:
=Dcount("[state of interest]","[prospect tracker]","[state of
interest]='qld'")

You should be able to use:
=Dcount("*","[prospect tracker]","[state of interest]=""qld""")

Explanation:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

G'day Allen,

Thanks for the help thus far.

I think the problem stems from the fact that I am wishing to count a text
value within a particular column (field). So I have a column 'state of
interest' in my Prospect Tracker Table. Entries in this field can be any
one
of all states, e.g. qld, vic etc.

In order to get the subtotals in the subreport I use this expression in a
text box: =Sum(Abs([state of interest]="qld")). Dcount is something I did
try, but if I use Dcount("[state of interest]","[prospect
tracker]","[state
of interest]="qld"") it doesnt work due to the quotation marks. Placing
the
criteria part of the expression in brackets () doesnt help either.

Thoughts?

Flynny, I'm not completely clear here.

If you want a count for all categories (not as limited in the subreport
or
the report's filter), you could use DCount() to get the number directly
from
the source talbe. The Control Source of the text box would be something
like
this:
=DCount("*", "Table1")

If you have a subreport that show up once for each state, and you want
the
total for all states at the end (perhaps limited to whatever is in the
various subreports are), you could put the text box in the same section
as
the subreport, set its control source as suggested, and set its Running
Sum
property to Over All so it accumulates the totals from all states. Then
in
the Report Footer section, you could display this total by referrring to
the
(hidden) running subm text box:
=[MyRunningSumTextBox]

Post back if that's still not what you need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yesw, but no. I do know how to refer to a text box from a subreport on
the
main report, in a sense what I am trying to do is total a text box
(which
is
a subtotal) on the main report.

In the group footer of the subreport I have a total for the number of
enquiries from each state for each advert type, so this is a summary
for
each
advert type.

What I want in the report footer of the main report is the total
enquiries
per state, regardless of advert type.



:

So you want the total from a text box in the subreport to show on the
main
report?

See:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html

The article explains how to refer to the Report in the subreport
control,
and handle the case where the subreport has no data.

G'day Helpers,

I have a report from a table that contains details of advertising
campaigns
we have undertaken and I am now attempting to measure the number of
enquiries
received on a state by state basis.

Details of the enquiries (such as enquirers name, state of interest
etc)
are
entered into the "Prospect Tracker Table".

My report, as stated, comes from the advertising table and then I
have
a
sub-report from the Prospect Tracker Table and in the report footer
of
the
sub-report the following expression, =Sum(Abs([state of
interest]="qld")),
provides me with a count of the number of enquires received for a
particular
advert from Qld.

Now, in the footer of the main report where I list the totals for
all
advert
categories, such as cost etc I would like a total of all enquiries
from
qld.

I have attempted a variety of expressions that I thought would work,
but
nothing does.