Using Count in a One to Many sourced Report

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

Noel

Hi, I have a report which draws on records in One to Many
relationships and Ive used Grouping (in Sorting and
Grouping) to ensure that each One side shows just once.
The problem is, when I use Count (*) in the report, it
counts all of the One sides. Eg if each One side has four
Many related records and there are 10 One sides, Count(*)
produces a value of 40, rather than 4. Is there a way to
fix this? Thanks, Noel
 
Noel said:
Hi, I have a report which draws on records in One to Many
relationships and Ive used Grouping (in Sorting and
Grouping) to ensure that each One side shows just once.
The problem is, when I use Count (*) in the report, it
counts all of the One sides. Eg if each One side has four
Many related records and there are 10 One sides, Count(*)
produces a value of 40, rather than 4. Is there a way to
fix this? Thanks, Noel
Click to expand...


That's what Count(*) does, count the number of details.
What do you want it to be, 10?

If so, then add an invisble text box named txtCount to the
Group Header section. Set its expression to =1 and its
RunningSum property to Over All.

Then, the text box in the Report Footer can use the
expression =txtCount (instead of =Count(*))
 
Thanks for the reply, Marsh. Yes, in the example I gave I
would want the total to be 10 (not 4 as I incorrectly
said). Ive done exactly as you said but my total comes to
1 in every case, so somethings wrong. I presume when you
say, Set its expression to =1, you mean set the control
source to 1. Surely I will always get 1 as the result,
rather than tracking the number of One sides? Where am I
going wrong? Thanks again, Noel
 
Hi again Marsh. Here is text I sent to another post, just
a few lines above this one.

Hi, Am I going mad or what? I posted a near identical
question to this one a little earlier. Marsh answered both
mine and this one and in both cases we cant get the
suggestion to work. What are the chances of this
happening? Marsh, if you read this, I assure you I have
not double posted, although I will repeat this reply to my
original post. Cheers, Noel
 
Hi, just after posting that last message, I realised what
I was doing wrong. I was stupidly putting the txtCount
text box in the report header rather than in the group
header - duhh. Anyway, Im still amazed about that other
near duplicate post - it wasnt me honest. Thanks for the
help, Noel PS will send this also to other post.
 
Hi, just after posting that last message, I realised what
I was doing wrong. I was stupidly putting the txtCount
text box in the report header rather than in the group
header - duhh. Anyway, Im still amazed about this near
duplicate post - it wasnt me honest. Thanks for the help,
Noel PS will send this also to my own post.
 
Noel said:
Hi, just after posting that last message, I realised what
I was doing wrong. I was stupidly putting the txtCount
text box in the report header rather than in the group
header - duhh.
Click to expand...

Ahhh, that sort of thing happens. Glad you got it working.

Anyway, Im still amazed about this near
duplicate post - it wasnt me honest. Thanks for the help,
Noel PS will send this also to my own post.
Click to expand...

OK, that happens sometimes too.
--
Marsh
MVP [MS Access]


 
Back
Top