Count Functions in Reports

[RS]

I am trying to do a final report which includes totals from my main table.
What I want to do have in the final reports is a total for all of the people
who will be in attendance for my conference. The total attendance figure is
not a problem. I put it in the report as =Count(PersonID).

What I am having a problem with is counting the number of states where
people are coming from ... I want a count of the number of states people are
coming from, not the total amount of states .. i.e. I have 500 people coming
from 48 states. The field is labeled "State". I would also like to know how
many countries these people are coming from.

Is there a way to put a function directly into the report to do this? I
really appreciate any suggestions.

Thank you,

RS

 

[Evi]

Group the report by State using the Sorting/Grouping box and giving the
Group a Group Header. In the Group Header put a text box.. Into it, type
=1

Click on the Properties button
Click on the text box.
On the Other tab of Properties, type
txtStates next to Name

On the Properties, Data Tab, next to Running Sum, choose Over All
On the Format tab, next to Visible, Choose No


In your Report Footer, add another text box. Into it, type
=[txtStates]

Evi

 

[Duane Hookom]

If you don't want to sort by State then you could create a totals query:
== qgrpStates ===
SELECT DISTINCT State
FROM tblReportRecordSource
==============

Then another
SELECT Count(*) As NumOfStates
FROM qgrpStates

The final query will return only one record with the count of states. You
can add this query to your report's record source so you can add NumOfStates
to the report fields.
--
Duane Hookom
Microsoft Access MVP

Group the report by State using the Sorting/Grouping box and giving the
Group a Group Header. In the Group Header put a text box.. Into it, type
=1

Click on the Properties button
Click on the text box.
On the Other tab of Properties, type
txtStates next to Name

On the Properties, Data Tab, next to Running Sum, choose Over All
On the Format tab, next to Visible, Choose No


In your Report Footer, add another text box. Into it, type
=[txtStates]

Evi

I am trying to do a final report which includes totals from my main table.
What I want to do have in the final reports is a total for all of the people
who will be in attendance for my conference. The total attendance figure is
not a problem. I put it in the report as =Count(PersonID).

What I am having a problem with is counting the number of states where
people are coming from ... I want a count of the number of states people are
coming from, not the total amount of states .. i.e. I have 500 people coming
from 48 states. The field is labeled "State". I would also like to know how
many countries these people are coming from.

Is there a way to put a function directly into the report to do this? I
really appreciate any suggestions.

Thank you,

RS

 

[RS]

Thank you, Evi! It worked for State. However, now I also need to
incorporate Country as well into the report. When I try to group a second
level for country, it does not give me the needed information. Any ideas on
this second group?

Group the report by State using the Sorting/Grouping box and giving the
Group a Group Header. In the Group Header put a text box.. Into it, type
=1

Click on the Properties button
Click on the text box.
On the Other tab of Properties, type
txtStates next to Name

On the Properties, Data Tab, next to Running Sum, choose Over All
On the Format tab, next to Visible, Choose No


In your Report Footer, add another text box. Into it, type
=[txtStates]

Evi

I am trying to do a final report which includes totals from my main table.
What I want to do have in the final reports is a total for all of the people
who will be in attendance for my conference. The total attendance figure is
not a problem. I put it in the report as =Count(PersonID).

What I am having a problem with is counting the number of states where
people are coming from ... I want a count of the number of states people are
coming from, not the total amount of states .. i.e. I have 500 people coming
from 48 states. The field is labeled "State". I would also like to know how
many countries these people are coming from.

Is there a way to put a function directly into the report to do this? I
really appreciate any suggestions.

Thank you,

RS

 

[RS]

I appreciate your assistance. However, when it comes to code, I am at a
loss. Thank you, however, for your help.

If you don't want to sort by State then you could create a totals query:
== qgrpStates ===
SELECT DISTINCT State
FROM tblReportRecordSource
==============

Then another
SELECT Count(*) As NumOfStates
FROM qgrpStates

The final query will return only one record with the count of states. You
can add this query to your report's record source so you can add NumOfStates
to the report fields.
--
Duane Hookom
Microsoft Access MVP

Group the report by State using the Sorting/Grouping box and giving the
Group a Group Header. In the Group Header put a text box.. Into it, type
=1

Click on the Properties button
Click on the text box.
On the Other tab of Properties, type
txtStates next to Name

On the Properties, Data Tab, next to Running Sum, choose Over All
On the Format tab, next to Visible, Choose No


In your Report Footer, add another text box. Into it, type
=[txtStates]

Evi

I am trying to do a final report which includes totals from my main table.
What I want to do have in the final reports is a total for all of the people
who will be in attendance for my conference. The total attendance figure is
not a problem. I put it in the report as =Count(PersonID).

What I am having a problem with is counting the number of states where
people are coming from ... I want a count of the number of states people are
coming from, not the total amount of states .. i.e. I have 500 people coming
from 48 states. The field is labeled "State". I would also like to know how
many countries these people are coming from.

Is there a way to put a function directly into the report to do this? I
really appreciate any suggestions.

Thank you,

RS

 

[Evi]

In the Sorting Grouping box, group by Country but then , still in the SG
box, slide that row *above* the State row.
Again, choose to have a header, again add another text box, this time name
it txtCountry.
Type =1 in it.
Make it a Running Sum Over All
Again, refer to it in a text box in the Report Footer as
=txtCountry

Evi

Thank you, Evi! It worked for State. However, now I also need to
incorporate Country as well into the report. When I try to group a second
level for country, it does not give me the needed information. Any ideas on
this second group?

Group the report by State using the Sorting/Grouping box and giving the
Group a Group Header. In the Group Header put a text box.. Into it, type
=1

Click on the Properties button
Click on the text box.
On the Other tab of Properties, type
txtStates next to Name

On the Properties, Data Tab, next to Running Sum, choose Over All
On the Format tab, next to Visible, Choose No


In your Report Footer, add another text box. Into it, type
=[txtStates]

Evi

I am trying to do a final report which includes totals from my main table.
What I want to do have in the final reports is a total for all of the people
who will be in attendance for my conference. The total attendance

figure
is

not a problem. I put it in the report as =Count(PersonID).

What I am having a problem with is counting the number of states where
people are coming from ... I want a count of the number of states

people
are

coming from, not the total amount of states .. i.e. I have 500 people coming
from 48 states. The field is labeled "State". I would also like to

know
how

many countries these people are coming from.

Is there a way to put a function directly into the report to do this? I
really appreciate any suggestions.

Thank you,

RS

 

[RS]

Hi Evi,

I tried that before, but the problem is when I move the Country above the
State, then I do not get the correct answer. With country above state, I get
14 countries, but 61 states. When I do state alone, I get 49 states. I
think it is probably because of how it is arranged. I really appreciate your
help. Is there anything else you can think of that would allow me to get in
essence separate counts for these two fields?

Thanks,

In the Sorting Grouping box, group by Country but then , still in the SG
box, slide that row *above* the State row.
Again, choose to have a header, again add another text box, this time name
it txtCountry.
Type =1 in it.
Make it a Running Sum Over All
Again, refer to it in a text box in the Report Footer as
=txtCountry

Evi

Thank you, Evi! It worked for State. However, now I also need to
incorporate Country as well into the report. When I try to group a second
level for country, it does not give me the needed information. Any ideas on
this second group?

Group the report by State using the Sorting/Grouping box and giving the
Group a Group Header. In the Group Header put a text box.. Into it, type
=1

Click on the Properties button
Click on the text box.
On the Other tab of Properties, type
txtStates next to Name

On the Properties, Data Tab, next to Running Sum, choose Over All
On the Format tab, next to Visible, Choose No


In your Report Footer, add another text box. Into it, type
=[txtStates]

Evi

I am trying to do a final report which includes totals from my main table.
What I want to do have in the final reports is a total for all of the
people
who will be in attendance for my conference. The total attendance figure
is
not a problem. I put it in the report as =Count(PersonID).

What I am having a problem with is counting the number of states where
people are coming from ... I want a count of the number of states people
are
coming from, not the total amount of states .. i.e. I have 500 people
coming
from 48 states. The field is labeled "State". I would also like to know
how
many countries these people are coming from.

Is there a way to put a function directly into the report to do this? I
really appreciate any suggestions.

Thank you,

RS

 

[RS]

Thank you so much for all your help, Evi! I figured it out a different way,
but this way is good to know for other things I will be working with. I
actually the went the route of the queries and was able to get it to work.
Thank you, again, for all your assistance!! RS

In the Sorting Grouping box, group by Country but then , still in the SG
box, slide that row *above* the State row.
Again, choose to have a header, again add another text box, this time name
it txtCountry.
Type =1 in it.
Make it a Running Sum Over All
Again, refer to it in a text box in the Report Footer as
=txtCountry

Evi

Thank you, Evi! It worked for State. However, now I also need to
incorporate Country as well into the report. When I try to group a second
level for country, it does not give me the needed information. Any ideas on
this second group?

Group the report by State using the Sorting/Grouping box and giving the
Group a Group Header. In the Group Header put a text box.. Into it, type
=1

Click on the Properties button
Click on the text box.
On the Other tab of Properties, type
txtStates next to Name

On the Properties, Data Tab, next to Running Sum, choose Over All
On the Format tab, next to Visible, Choose No


In your Report Footer, add another text box. Into it, type
=[txtStates]

Evi

I am trying to do a final report which includes totals from my main table.
What I want to do have in the final reports is a total for all of the
people
who will be in attendance for my conference. The total attendance figure
is
not a problem. I put it in the report as =Count(PersonID).

What I am having a problem with is counting the number of states where
people are coming from ... I want a count of the number of states people
are
coming from, not the total amount of states .. i.e. I have 500 people
coming
from 48 states. The field is labeled "State". I would also like to know
how
many countries these people are coming from.

Is there a way to put a function directly into the report to do this? I
really appreciate any suggestions.

Thank you,

RS

 

[RS]

Thank you, Duane. I was able to get the queries to work and I now have my
report. Thanks to you and Evi for your assistance. I just needed to get a
second opinion and it allowed me to work it out. Thanks! RS

If you don't want to sort by State then you could create a totals query:
== qgrpStates ===
SELECT DISTINCT State
FROM tblReportRecordSource
==============

Then another
SELECT Count(*) As NumOfStates
FROM qgrpStates

The final query will return only one record with the count of states. You
can add this query to your report's record source so you can add NumOfStates
to the report fields.
--
Duane Hookom
Microsoft Access MVP

Group the report by State using the Sorting/Grouping box and giving the
Group a Group Header. In the Group Header put a text box.. Into it, type
=1

Click on the Properties button
Click on the text box.
On the Other tab of Properties, type
txtStates next to Name

On the Properties, Data Tab, next to Running Sum, choose Over All
On the Format tab, next to Visible, Choose No


In your Report Footer, add another text box. Into it, type
=[txtStates]

Evi

I am trying to do a final report which includes totals from my main table.
What I want to do have in the final reports is a total for all of the people
who will be in attendance for my conference. The total attendance figure is
not a problem. I put it in the report as =Count(PersonID).

What I am having a problem with is counting the number of states where
people are coming from ... I want a count of the number of states people are
coming from, not the total amount of states .. i.e. I have 500 people coming
from 48 states. The field is labeled "State". I would also like to know how
many countries these people are coming from.

Is there a way to put a function directly into the report to do this? I
really appreciate any suggestions.

Thank you,

RS

 

[Evi]

That shouldn't be happening. There is something you aren't telling me.
Have you, by any chance set Repeat This Section to Yes
on either the State or Country Headers?
I'll just check this.
You moved Country above State in the SortingGrouping box, and choose to have
a heading for Country (you already have a heading for State). In the report,
you would put the fields about Country (eg Country Name) into the Country
header. You would also put in your Running Sum text box named txtCountry
into the Country Header. (Just for now, make this text box Visible and use
the Font Colour button to make it Red). And Running Sum has been set to Over
All in both of these Running Sum text boxes.
You put your State controls (eg State Name) into the State Header and also
txtState - make this Blue for now.

You've put the other text boxes which say
=txtCountry (colour this red)

and

=txtState (colour this blue

into the Report Footer.
Is this what you did?
Check this visually

What do you see?

Evi

Hi Evi,

I tried that before, but the problem is when I move the Country above the
State, then I do not get the correct answer. With country above state, I get
14 countries, but 61 states. When I do state alone, I get 49 states. I
think it is probably because of how it is arranged. I really appreciate your
help. Is there anything else you can think of that would allow me to get in
essence separate counts for these two fields?

Thanks,

In the Sorting Grouping box, group by Country but then , still in the SG
box, slide that row *above* the State row.
Again, choose to have a header, again add another text box, this time name
it txtCountry.
Type =1 in it.
Make it a Running Sum Over All
Again, refer to it in a text box in the Report Footer as
=txtCountry

Evi

Thank you, Evi! It worked for State. However, now I also need to
incorporate Country as well into the report. When I try to group a second
level for country, it does not give me the needed information. Any

ideas
on

this second group?

:

Group the report by State using the Sorting/Grouping box and giving the
Group a Group Header. In the Group Header put a text box.. Into it, type
=1

Click on the Properties button
Click on the text box.
On the Other tab of Properties, type
txtStates next to Name

On the Properties, Data Tab, next to Running Sum, choose Over All
On the Format tab, next to Visible, Choose No


In your Report Footer, add another text box. Into it, type
=[txtStates]

Evi

I am trying to do a final report which includes totals from my

main
table.

What I want to do have in the final reports is a total for all of the
people
who will be in attendance for my conference. The total attendance figure
is
not a problem. I put it in the report as =Count(PersonID).

What I am having a problem with is counting the number of states where
people are coming from ... I want a count of the number of states people
are
coming from, not the total amount of states .. i.e. I have 500 people
coming
from 48 states. The field is labeled "State". I would also like

to
know

how
many countries these people are coming from.

Is there a way to put a function directly into the report to do

this?
I

really appreciate any suggestions.

Thank you,

RS