Calculate number of rows in report (1 Viewer)

[lbs]

Hi guys

I am trying to build an expression for an unbound field in a report. I want to show a ratio of number of particular meetings for each consultant.
For example, the total number of meetings = 5
These meetings were done by three consultants. So I should have the number of meetings done by each consultant and next to it the ratio. It should look like this:

LGIT meeting      Total: 5
LS     2     Ratio: 1.2
AW    2     Ratio: 1.2
TW    1     Ratio:  0.6

To calculate the field ratio I need to use the formula:
Number of meetings per consultant / (Total number of meetings/number of consultants involved).
So, I need to figure out the number of consultants involved which would be effectively the number of rows in the Group Header for consultants.
How can I get it?

 

[theDBguy]

Hi. Maybe you could start with this:


Place an unbound textbox on the group header with a control source of =1. Then in the properties window, set the Running Sum property to either Over Group or try the other ones to see if you get what you want from it.

 

[lbs]

Hi. Maybe you could start with this:


Place an unbound textbox on the group header with a control source of =1. Then in the properties window, set the Running Sum property to either Over Group or try the other ones to see if you get what you want from it.

I can see now the numbering of the rows. But I can't use it for my expression. It produces a message: enter parameter.

 

[theDBguy]

I can see now the numbering of the rows. But I can't use it for my expression. It produces a message: enter parameter.

Hi. Depending on what you want out of it, you could sometimes hide the counter control and add another textbox referring to it to get the final result. You would then use that new textbox for other calculations. However, I am not sure I fully understand what you mean, so maybe you might consider posting a sample db with test data to help us understand your requirements.


With regards to the parameter prompt, did you use the name of the textbox in your expression? Can you show us the expression you used and the actual error message or parameter prompt you received? Thanks.

 

[lbs]

Hi.


With regards to the parameter prompt, did you use the name of the textbox in your expression? Can you show us the expression you used and the actual error message or parameter prompt you received? Thanks.

I created a field with the name Cons_count and gave it all you adviseed (=1 and running sum over group). Now this field shows me numbers of rows in a way:

LS     1
AW    2
TW    3
CM    4

So it numbers the rows.
Then I created a field in the group header and now I am fiddling with the expressions for that field so that it shows exactly the number of the rows in the group. I tried Count(Cons_count), tried Max (Cons_count) but each time I see a message:

Enter parameter V...
Cons_count

 

[theDBguy]

I created a field with the name Cons_count and gave it all you adviseed (=1 and running sum over group). Now this field shows me numbers of rows in a way:

LS     1
AW    2
TW    3
CM    4

So it numbers the rows.
Then I created a field in the group header and now I am fiddling with the expressions for that field so that it shows exactly the number of the rows in the group. I tried Count(Cons_count), tried Max (Cons_count) but each time I see a message:

Enter parameter V...
Cons_count

Here's another idea you could try. Leave the textbox with =1 but go to the Running Sum and set it back to No. Then, in your expression try something like: =Sum(TextboxName) to see if it adds up all the 1s.

 

[isladogs]

PMFJI
There are lots of ways of counting records in a report.
One method is to add an unbound textbox to the footer and type =Count(*) as its record source.
An even easier method is to get Access to do it for you.
Right click a field you want to count, click Total and select Count Records from the dropdown list

Access will then add the textbox in the report footer for you
You also have several other choices available if needed

For more info, I suggest you read this MS article about grouping, counts and totals in reports https://support.office.com/en-gb/article/Create-a-grouped-or-summary-report-f23301a1-3e0a-4243-9002-4a23ac0fdbf3

 

[lbs]

Here's another idea you could try. Leave the textbox with =1 but go to the Running Sum and set it back to No. Then, in your expression try something like: =Sum(TextboxName) to see if it adds up all the 1s.

The same message. If I ignore the message and press OK then it returns an emty field. If I use Count() then it returns 0

 

[theDBguy]

The same message. If I ignore the message and press OK then it returns an emty field. If I use Count() then it returns 0

Are you able to post a small demo version of your db?

 

[isladogs]

@lbs
Did you see my post?

 

[lbs]

Isladogs, I noticed your post only now.

PMFJI
There are lots of ways of counting records in a report.
One method is to add an unbound textbox to the footer and type =Count(*) as its record source.
An even easier method is to get Access to do it for you.
Right click a field you want to count, click Total and select Count Records from the dropdown list

Yes it works for counting the number of records. But unfortunately I can't use this calculated number for further calculations, for example in expression builder. It does not want to recognise this number as a proper field.

Are you able to post a small demo version of your db?

I did not manage to reduce DB to demo but I am posting the full version here. The report of my concern is r_10_mdt_cons_01

 

[theDBguy]

I did not manage to reduce DB to demo but I am posting the full version here. The report of my concern is r_10_mdt_cons_01

Hi. Thanks. Is this what you were expecting to see?

If not, please explain what each box is supposed to say instead.

 

[isladogs]

Maybe I'm missing something but try the attached.
I've removed four existing attempts and added the count to the footer
Total =97

 

[lbs]

I think I caused some confusion. Actially I am not counting the number of records. I am counting the number of rows in a group, which is not equal to number of records. The Count(*) gives me the number of records. Because one consultant may show 2 and more records then the number of rows in a group is less then number of records.

 

[theDBguy]

Hi. Or maybe this is what you meant?

PS. Sorry, this post is before I saw isladogs' post above.


PPS. And also before I saw your reply to his post as well.

 

[lbs]

Hi. Thanks. Is this what you were expecting to see?

If not, please explain what each box is supposed to say instead.

No, this is not I am after.
What I want to see:

An                     1    1
LS    1

BrRDE_post        3    2
LS       2
NC      1

BrRDE_pre          3    3
cem    1
ck       1
ls        1

BrT_post            2    2
ls      1
TW    1

BrT_pre   
AW     1           2    2
CK      1

So that in next to the number of MDTs I saw the number of consultants involved in this type of MDTs

 

[theDBguy]

No, this is not I am after.
What I want to see:

An                     1    1
LS    1

BrRDE_post        3    2
LS       2
NC      1

BrRDE_pre          3    3
cem    1
ck       1
ls        1

BrT_post            2    2
ls      1
TW    1

BrT_pre   
AW     1           2    2
CK      1

So that in next to the number of MDTs I saw the number of consultants involved in this type of MDTs

Like this?

 

[lbs]

Hi. Or maybe this is what you meant?

Yes this is what I need
I will try it now to see if I can use iot for further calculations

 

[theDBguy]

Yes this is what I need
I will try it now to see if I can use iot for further calculations

Okay. Let us know how it goes. In case you missed it, I posted your db back above.

 

[lbs]

theDBguy and isladogs,
Thank you very much,

It works and I can use this field for further calculations of Ratio what I wanted.

But, culd you explain it to me where =[number_rows] came from?