Numbering records within groups

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I have a report that lists residents at their addresses in a town.

The report formats the information using two grouping levels:
Neighbourhood Name, Street Name, then sorted by Civic Number, then
Resident's Name

Basically you would see:
EAST AREA
MAIN STREET
Civic #1 John Doe
Civic #1 Jane Doe
Civic #2 Bill Smith
Civic #5 Brian Depow
SECOND STREET
Civic #1 David Harty
Civic #1 June Harty
etc

WEST AREA
KING STREET
Civic #1 ...etc

On the report I need to number the people's names sequentially, but reset
the number to ONE (1) when each neighbourhood group changes. (The names
listed in WEST AREA would start at 1)

I tried a running sum over group, but this resets to 0 with every street,
and a running sum over all works well for the first neighbourhood, but
continues into the remaining ones as well. I have tried using VB to reset
the running sum to 0, but I get an error. Possibly my syntax is wrong, but
this should be fairly easy, no?

How can I number these names with a reset at each neighbourhod group level?

Any help is appreciated.
 
Dave said:
I have a report that lists residents at their addresses in a town.

The report formats the information using two grouping levels:
Neighbourhood Name, Street Name, then sorted by Civic Number, then
Resident's Name

Basically you would see:
EAST AREA
MAIN STREET
Civic #1 John Doe
Civic #1 Jane Doe
Civic #2 Bill Smith
Civic #5 Brian Depow
SECOND STREET
Civic #1 David Harty
Civic #1 June Harty
etc

WEST AREA
KING STREET
Civic #1 ...etc

On the report I need to number the people's names sequentially, but reset
the number to ONE (1) when each neighbourhood group changes. (The names
listed in WEST AREA would start at 1)

I tried a running sum over group, but this resets to 0 with every street,
and a running sum over all works well for the first neighbourhood, but
continues into the remaining ones as well. I have tried using VB to reset
the running sum to 0, but I get an error. Possibly my syntax is wrong, but
this should be fairly easy, no?

How can I number these names with a reset at each neighbourhod group level?
Click to expand...


Gee, this is tricky. I hope I can explain it so you can
understand.

First, create a text box named txtDtlCnt in the detail
section. Set its control source expression to =1 and
RunningSum to Over Group.

Next, add text box to the neighborhood header section. Name
this one txtStreet and leave its control source blank. Also
add a line of code to the neighborhood header section's
Format event procedure:
Me.txtStreet = 0

Now, create a text box named txtGrpCnt in the street group
footer section. Set its control source expression to
=txtDtlCnt and RunningSum to Over Group. Add a line of code
to the street group footer section's Format event procedure:
Me.txtStreet = Me.txtGrpCnt

Finally, add still one more text box to the detail section
and set its control source expression to
=txtStreet + txtDtlCnt
 
Awsome!!! Thanks Marsh.
Your solution worked like a charm. You've saved me some torn-out hair!
Dave
 
Back
Top