Number Records by group in Report

[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

 

[Allen Browne]

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.

 

[gumby]

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.

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 -

Thanks that work. Of course like you mentioned, it shows identical
times different ranks, unlike the Query. Anyway around that in the
report?

 

[Allen Browne]

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.

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 -

Thanks that work. Of course like you mentioned, it shows identical
times different ranks, unlike the Query. Anyway around that in the
report?

 

[gumby]

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 -

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 -

- Show quoted text -

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