MS Access numbering report group sections

MS Access.  I have a report with a number of defined sorting and grouping sections. For one particular section there may be 1 or 10 occurrences for the report being printed.

I'd like to be able to add a counter for this section  1 / 6  for example, in the instance that for this report there are 6 entries for that sorting group and this is the first one.

For example, if the group was Last Name I want the following to appear in the group header:

Jones  1/6
Peters  2/6
Edwards  3/6
Jenkins 4/6
Smith  5/6
Bond  6/6
[edit - the order is not important]

Thanks
LVL 2
hgj1357Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
add an unbound textbox to the section of the report you want the counter in (group header, detail, group footer), give it a ControlSource:=1, then set the RunningSum property to: Over Group or Over All
Jeffrey CoachmanMIS LiasonCommented:
try this:
Database39.accdb
Jeffrey CoachmanMIS LiasonCommented:
will look like this:
sample

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

hgj1357Author Commented:
Looks like EXACTLY what I want.  However, I'm using MDB access. Can you posst the VBA code to achieve this?  I can't open a accdb database as I still live in the '90s
PatHartmanCommented:
Does that mean that Bill Clinton is still your president LOL
hgj1357Author Commented:
Dale's comment "add an unbound textbox to the section of the report you want the counter in (group header, detail, group footer), give it a ControlSource:=1, then set the RunningSum property to: Over Group or Over All "  oddly didn't show up until a few hours after it was posted (according to the post stamp)

Can I use    =max([ControlSource]) to get the "6" in 2 of 6  ?
hgj1357Author Commented:
Can I use    =max([ControlSource]) to get the "6" in 2 of 6  ?     NO
hgj1357Author Commented:
OK.  Here's how you do it.  
Clone the query behind the report as "Q2"
Q2   keep all filters and links, but remove all unecessary fields and group on LastName
In Report header MyCounter = DCount([LastName], "Q2")
use Counter = 1 , running sum group in each section
Also  MyField: =trim([Counter] & " of " & [MyCounter])
Hamed NasrRetired IT ProfessionalCommented:
Upload a sample of your mdb database.
In the LastName group header,
Option Compare Database
Public lnCount As Integer ' for extra sub grouping
Public lnSeq As Integer

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
    If lnSeq = 0 Then
        lnCount = DCount("[LastName]", "[YourTable]", "[LastName]='" & Me!LastName & "'")
        lnSeq = 0
    End If
    lnSeq = lnSeq + 1
    txtHeading = lnSeq & "/" & lnCount
End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
    lnCount = 0
    lnSeq = 0
End Sub

Open in new window

Jeffrey CoachmanMIS LiasonCommented:
OK,
Try this mdb version
Database391.mdb
Hamed NasrRetired IT ProfessionalCommented:
Trying to check with mdb uploaded by Jeffrey (thanks).
Hamed NasrRetired IT ProfessionalCommented:
Try this using Jeffrey's database and adding other 3 names from your question.
Database391-c.mdb
hgj1357Author Commented:
I can't open the MDB files as my version of access is old.

However, the issue is resolved.

Clone the query behind the report as "Q2"
Q2   keep all filters and links, but remove all unecessary fields and group on LastName
In Report header MyCounter = DCount([LastName], "Q2")
use Counter = 1 , running sum group in each section
Also  MyField: =trim([Counter] & " of " & [MyCounter])
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.