If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Strange Count problem in Report Group
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 |
#2
|
|||
|
|||
Strange Count problem in Report Group
Noel wrote:
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. 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.) -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
Strange Count problem in Report 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 -----Original Message----- Noel wrote: 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. 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.) -- Marsh MVP [MS Access] . |
#4
|
|||
|
|||
Strange Count problem in Report Group
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] Noel wrote: 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 -----Original Message----- Noel wrote: 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. 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.) |
#5
|
|||
|
|||
Strange Count problem in Report Group
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] Noel wrote: 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 -----Original Message----- Noel wrote: 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. 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.) . |
#6
|
|||
|
|||
Strange Count problem in Report Group
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] Noel wrote: 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 -----Original Message----- Noel wrote: 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. 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.) . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Strange Problem Occured Using C#&Access | Xian Xu | General Discussion | 5 | June 30th, 2004 10:12 PM |
Report Wizard Problem | vikash | Using Forms | 1 | June 16th, 2004 12:45 PM |
Problem Creating Report | [email protected] | Using Forms | 1 | June 7th, 2004 07:51 AM |
Page and total for group by report | Le Tran | Setting Up & Running Reports | 1 | June 1st, 2004 06:40 PM |
Strange Problem with SUMPRODUCT and/or SUMIF | Peo Sjoblom | Worksheet Functions | 1 | February 17th, 2004 01:44 AM |