Running Sum in Report Based on Unique Values (1 Viewer)

[nchawla]

I am creating reports that show student names by employee ID and course number. Some of the student names appear in multiple rows under the same group, which is intentional because I need to display multiple rows of information.

I need to add a running sum, but I want to number the rows by student name. If a student name is duplicated, I want the row number to repeat itself.

This is what I want my report to look like:

# EMPID COURSE STUDENT NAME
1 1 Math Adams
2 1 Math Baylor
2 1 Math Baylor
3 1 Math Chris
4 1 Math Davis
1 2 Math Aaron
1 2 Math Aaron
2 2 Math Bets
3 2 Math Carter

The part I'm having trouble with is the numbering. I am able to create a running sum over group but this sum is numbering each ROW instead of each UNIQUE VALUE. I've tried adding numbers to my query, with no luck. One of the challenges with numbering rows in the query itself is that I am working with multiple large datasets, some with over 1,000,000 records.

I apologize if the answer to this question is discussed elsewhere. I can't seem to find it and would appreciate any help!

Thank you!

 

[llkhoutx]

You have to create another column in your report recordsource creating a duplicate row number. Access won't do it for you.

 

[arnelgp]

put an unbound textbox on your report, say txtRowNumber. this will be your row number. copy and paste this on event of your report.
you must have a sort on Student Name field, for this to work.

Option Compare Database
Option Explicit

Private intLineNumber As Integer
Private bolSecondLoop As Boolean

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Static strResult As String
    
    ' if you do not have a report footer,
    ' comment out 'If bolsecondLoop then
    If bolSecondLoop Then
        If Me![Student Names] <> strResult Then
            strResult = Me![Student Names]
            intLineNumber = intLineNumber + 1
        End If
        Me.txtRowNumber = intLineNumber
    End If
End Sub

' If you do not have a report footer
' comment out all below subroutine
Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
    bolSecondLoop = True
End Sub