Number Records by group in Report

  • Thread starter Thread starter gumby
  • Start date Start date
G

gumby

I would like to number or rank records in a report by grouping. The
ranking or numbering would be based on the lowest time. I have a field
of [HandleTime] in "nn:ss". The groups would be based off a
department.


Any Ideas. I have looked out numbering them in a query, but that does
not help for the groupings.

Thanks -
David
 
Add a text box to the Detail section, and give it these properties:
Control Source =1
Running Sum Over Group

This assumes the Sorting And Grouping box is sorting by the times within the
group, and you don't have to show 2 identical times as the same rank.
 
Add a text box to the Detail section, and give it these properties:
Control Source =1
Running Sum Over Group

This assumes the Sorting And Grouping box is sorting by the times within the
group, and you don't have to show 2 identical times as the same rank.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.




I would like to number or rank records in a report by grouping. The
ranking or numbering would be based on the lowest time. I have a field
of [HandleTime] in "nn:ss". The groups would be based off a
department.
Click to expand...
Any Ideas. I have looked out numbering them in a query, but that does
not help for the groupings.
Click to expand...
Thanks -
David- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks that work. Of course like you mentioned, it shows identical
times different ranks, unlike the Query. Anyway around that in the
report?
 
There are some rather inefficient solutions, such as a text box that counts
the number of higher values, e.g.:

=Nz(DCount("*", "Table1", "([HandleTime] < " &
Format([HandleTime], "\# hh\:nn\:ss\#" &
") AND ([MyGroupField] = " & [MyGroupField] & ")"),0) + 1)

That's all one line, and substitute the field you group on for MyGroupField,
and add quotes if it is a Text field (not a Number field.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

gumby said:
Add a text box to the Detail section, and give it these properties:
Control Source =1
Running Sum Over Group

This assumes the Sorting And Grouping box is sorting by the times within
the
group, and you don't have to show 2 identical times as the same rank.




I would like to number or rank records in a report by grouping. The
ranking or numbering would be based on the lowest time. I have a field
of [HandleTime] in "nn:ss". The groups would be based off a
department.
Click to expand...
Any Ideas. I have looked out numbering them in a query, but that does
not help for the groupings.
Click to expand...
Thanks -
David- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks that work. Of course like you mentioned, it shows identical
times different ranks, unlike the Query. Anyway around that in the
report?
Click to expand...
 
There are some rather inefficient solutions, such as a text box that counts
the number of higher values, e.g.:

=Nz(DCount("*", "Table1", "([HandleTime] < " &
Format([HandleTime], "\# hh\:nn\:ss\#" &
") AND ([MyGroupField] = " & [MyGroupField] & ")"),0) + 1)

That's all one line, and substitute the field you group on for MyGroupField,
and add quotes if it is a Text field (not a Number field.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.




Add a text box to the Detail section, and give it these properties:
Control Source =1
Running Sum Over Group
This assumes the Sorting And Grouping box is sorting by the times within
the
group, and you don't have to show 2 identical times as the same rank.

I would like to number or rank records in a report by grouping. The
ranking or numbering would be based on the lowest time. I have a field
of [HandleTime] in "nn:ss". The groups would be based off a
department.
Any Ideas. I have looked out numbering them in a query, but that does
not help for the groupings.
Thanks -
David- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
Thanks that work. Of course like you mentioned, it shows identical
times different ranks, unlike the Query. Anyway around that in the
report?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Allen,
Thanks. The first example you gave ended up working best. Plus I have
a 2nd [Time Field] to sort by so it is rare that I would have
identical times in both fields. Thanks a ton.

By the way, you error handling example on your web page was
excellent.

David
 
Back
Top