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?
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 FyeCommented:
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
0
Jeffrey CoachmanMIS LiasonCommented:
try this:
Database39.accdb
0
Jeffrey CoachmanMIS LiasonCommented:
will look like this:
sample
0

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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
0
PatHartmanCommented:
Does that mean that Bill Clinton is still your president LOL
1
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  ?
0
hgj1357Author Commented:
Can I use    =max([ControlSource]) to get the "6" in 2 of 6  ?     NO
0
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])
0
hnasrCommented:
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

0
Jeffrey CoachmanMIS LiasonCommented:
OK,
Try this mdb version
Database391.mdb
0
hnasrCommented:
Trying to check with mdb uploaded by Jeffrey (thanks).
0
hnasrCommented:
Try this using Jeffrey's database and adding other 3 names from your question.
Database391-c.mdb
0
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])
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.