Control group expression for group in report?

[burnfly]

Oh, I seem to have a bunch of questions lately.

Is there a way to have an expression in the control source of a text box in a report, that re-starts or is exclusive for every group within the report?

Thanks and thanks again!

 

[Pat Hartman]

If you need breaks, use the sorting and grouping options to define them. If you want to hide duplicates, that property is available for each control. If you are talking about running totals, there is an Over Group option as well as Over All.

 

[burnfly]

Thanks for your reply Pat, I appreciate it.

I basically need something like a DCount expression over a group in a report. Ha, I think.

Currently, I am using a running sum over the report groups to show rank (place) by score number. However, I need it to display score ties within the rank (1,2,2,3,3,3,etc...). I have gotten this to work over just *one* particular sort/group within a query, but I need it for every group (hundreds) in a main report.

 

[Pat Hartman]

Add a footer for the group and add a control there to sum the column.

 

[burnfly]

Well, thanks again for the reply Pat. Hmm. I'm not sure what this would allow me to do. ?

My report sorts and groups Divisions > Category > AgeGroup > Score. There are hundreds of entries, maybe 15 Divisions, 25 Categories, and 30 AgeGroups - all with a Score. My report groups/sorts all of these to unique Div/Cat/Age and then sorts by Score and I currently show "Place" by doing a running sum of the group within the report. This only consecutively numbers the group by score (rank) and does not show if the scores are tied (1,2,3,4,5, etc...) Problem is, I need to see tie scores and it needs to display consecutively (not skip from 1,2,2, to 4 - I need 1,2,2,3,4,4,5).

I don't care what "rank" the registration is by score - I care what the scores are by rank (what is the 1st highest score, or how many tied for the 1st highest score, then what is the 2nd highest score, or how many tied for the 2nd highest score, etc...).

Perhaps trying to calculate and display this within the report is not the correct approach. But, then I assume it would have to be solved within some kind of query setup, and I don't understand how to get a query to display and group/sort that information with the proper scoring rank. ?? Dunno... Er...

 

[burnfly]

Ok, to anyone interested, I solved this. Not in the report though. I was able to build a second query that grouped (totals query) my Division/Category/AgeGroup to one field (Concatenated) and the Score to another field. I then created a third query from that and was able to do a "rank within group" by using DCount with two criteria in it.

The DCount goes something like → DCount ("Score","query","Division/Category/AgeGroup =" & [Division/Category/AgeGroup] & "And Score >" & [Score])+1

I believe the DCount is counting the distinct scores from the grouped (totals) query, and the "Division/Category/AgeGroup=" & [Division/Category/AgeGroup] (I actually used the ID numbers concatenated for a unique number) is providing a group within the query of which the scores are ranked.

Then, I created another (fourth) query which ties the first, second (totals) and third (DCount) queries together to show the consecutive score ranks with ties. They now display 1,1,2,3,3,3,4,5,5,etc... per group.

Since I now have a calculated rank from a query, I just show that as "place" in my report. No incorrect running sum needed. Thanks to all for sparking the thoughts!