Strange Count problem in Report Group

  • Thread starter Thread starter Noel
  • Start date Start date
N

Noel

Hi. Im having a strange problem with a counting method in
a report. My report has three grouping levels, the first
being by SchoolID. I have set up an unbound txt box,
positioned in this group, with control source =1, Running
Sum set to overall and Visible set to No. Then another txt
box is related to this in the report footer and shows the
count of all Schools shown in the report. I thought this
was working OK until I filtered down to just one school
recently (I can do this using a reports selection process
given to me via this news group with much help from Tom
Wickerath). For this one school only (perhaps others too –
I havent checked them all individually) the count shows 2.
As I say most, if not all, others correctly show a figure
of 1. This seems completely bonkers. If I am grouping by
SchoolID and that ID is an autonumber field in the School
table, then how can the count be other than one, if only
one school is selected and shown on the report?

To make things odder, I tried messing about with the other
records which the report draws on. Each School has a
number of Mentors and each Mentor has a number of
meetings. So the three groupings are SchoolID, then
MentorID, then MeetingID, all autonumber fields. For this
particular School, I can get it to show a count of 1 if I
remove one of the Mentors from this school. I tried to
find out if it was just one Mentor causing the count to go
to 2 but at this point I gave up on this theory because,
even if I could see a pattern, there doesn’t seem any
reason why my changing the Mentor allocation should vary
the school count. Other schools have more mentors, with a
varied pattern of Meetings and they show a correct count
of 1. Weird.

Can anyone throw any light on this? Thanks, Noel
 
Noel said:
Hi. Im having a strange problem with a counting method in
a report. My report has three grouping levels, the first
being by SchoolID. I have set up an unbound txt box,
positioned in this group, with control source =1, Running
Sum set to overall and Visible set to No. Then another txt
box is related to this in the report footer and shows the
count of all Schools shown in the report. I thought this
was working OK until I filtered down to just one school
recently (I can do this using a reports selection process
given to me via this news group with much help from Tom
Wickerath). For this one school only (perhaps others too –
I havent checked them all individually) the count shows 2.
As I say most, if not all, others correctly show a figure
of 1. This seems completely bonkers. If I am grouping by
SchoolID and that ID is an autonumber field in the School
table, then how can the count be other than one, if only
one school is selected and shown on the report?

To make things odder, I tried messing about with the other
records which the report draws on. Each School has a
number of Mentors and each Mentor has a number of
meetings. So the three groupings are SchoolID, then
MentorID, then MeetingID, all autonumber fields. For this
particular School, I can get it to show a count of 1 if I
remove one of the Mentors from this school. I tried to
find out if it was just one Mentor causing the count to go
to 2 but at this point I gave up on this theory because,
even if I could see a pattern, there doesn’t seem any
reason why my changing the Mentor allocation should vary
the school count. Other schools have more mentors, with a
varied pattern of Meetings and they show a correct count
of 1.
Click to expand...


Is the running sum text box in the school head/footer or is
in the some other section. (It sounds like you have it
somewhere in the mentor group.)
 
Hi Marsh. Yes its definitely in the SchoolID Header
section. Bear in mind it shows a count of 1 for every
other occasion where I call up one School for the report -
at least all of those Ive checked. It seems its just this
one School, for some reason. I was thinking, is there any
other way to count the SchoolID Header records that I
could try, just to compare? Thanks for the reply, Noel
 
Barring some kind of corruption, I've never seen that kind
of problem. Just for kicks, try renaming the report, then
Copy/Paste it back to the original name and see what happens
with the new copy.

Depending on how you're applying criteria to the report's
record source, you might be able to use another query to
calculate the number of schoolIDs:

SELECT DISTINCT SchoolID
FROM reportquery

And the report footer text box can then display the count
with:
=DCount("*", "otherquery")
but this is clumsy at best and it's unlikely that your
report's criteria is that simple.

If anyone else has a clue what might cause this anomaly, I'd
sure like to hear it.
 
Hi Marsh. Ill give your last suggestion a try and will get
back to you some time tomorrow GMT. Thanks again for the
help, Noel
-----Original Message-----
Barring some kind of corruption, I've never seen that kind
of problem. Just for kicks, try renaming the report, then
Copy/Paste it back to the original name and see what happens
with the new copy.

Depending on how you're applying criteria to the report's
record source, you might be able to use another query to
calculate the number of schoolIDs:

SELECT DISTINCT SchoolID
FROM reportquery

And the report footer text box can then display the count
with:
=DCount("*", "otherquery")
but this is clumsy at best and it's unlikely that your
report's criteria is that simple.

If anyone else has a clue what might cause this anomaly, I'd
sure like to hear it.
--
Marsh
MVP [MS Access]


Hi Marsh. Yes its definitely in the SchoolID Header
section. Bear in mind it shows a count of 1 for every
other occasion where I call up one School for the report -
at least all of those Ive checked. It seems its just this
one School, for some reason. I was thinking, is there any
other way to count the SchoolID Header records that I
could try, just to compare? Thanks for the reply, Noel
shows
2. if
I to
go with
a or
is
Click to expand...

.
Click to expand...
 
Hi again Marsh. I hope you read this - if I dont hear
from you Ill raise another post.

Well, your suggestions didnt bear fruit Im afraid but
something I just tried may throw some light on things,
although it seems just as improbable. For some reason I
decided to set Visible = True in the txtCountofSchools
text box which is in the SchoolID Header area and now,
when I specify that same odd School name,
txtCountofSchools correctly shows 1 and the text box in
the report footer, whose Control Source is set to =
[txtCountofSchools] shows 2. I changed the name of
txtCountofSchools and the Control Source of the footer
text box to make sure I hadnt got some hidden text box
somewhere and this confirmed that theyre linked OK. I
then set up a completely new text box in the SchoolID
header, called it txtCount, set control source to =1 and
Running sum = Over All and added a second txt box in the
footer set to control source =[txtCount]. Same result. I
then moved one of the footer text boxes into the SchoolID
area and it correctly shows 1. But if I move it back to
either the report or page footer, it shows 2. Am I going
mad? - surely if the footer text boxes control source is
the txt box in the Header, it has to show the same value,
no matter where its postioned. Any further ideas??? By
the way, Ive proved that the count is accurate in many,
if not all, other instances - even when I specify a
criteria for the report that allows this particular
school to be included (e.g by specifying a particular
Type of School or County). I would be interested in any
further views or tests you might have. Perhaps I need an
exorcist. Cheers, Noel

-----Original Message-----
Barring some kind of corruption, I've never seen that kind
of problem. Just for kicks, try renaming the report, then
Copy/Paste it back to the original name and see what happens
with the new copy.

Depending on how you're applying criteria to the report's
record source, you might be able to use another query to
calculate the number of schoolIDs:

SELECT DISTINCT SchoolID
FROM reportquery

And the report footer text box can then display the count
with:
=DCount("*", "otherquery")
but this is clumsy at best and it's unlikely that your
report's criteria is that simple.

If anyone else has a clue what might cause this anomaly, I'd
sure like to hear it.
--
Marsh
MVP [MS Access]


Hi Marsh. Yes its definitely in the SchoolID Header
section. Bear in mind it shows a count of 1 for every
other occasion where I call up one School for the report -
at least all of those Ive checked. It seems its just this
one School, for some reason. I was thinking, is there any
other way to count the SchoolID Header records that I
could try, just to compare? Thanks for the reply, Noel
method
in another
txt shows
the shows
2. if
I to
go with
a or
is
Click to expand...

.
Click to expand...
 
Back
Top