Output auto-numbered text file with groups

[snow-raven]

I need to output a text CSV file with a consistent table format to another program. This table contains a page number column and must contain a sequential numbering column. I currently generate a row number using method #3 from Access Experts: https://accessexperts.com/blog/2015/07/28/ways-to-do-sequential-numbering-in-access/

My table looks something like:
Data__Page__Row
Data____1____1
Data____1____2
Data____1____3
Data____2____4
Data____2____5
Data____2____6

Sometimes, I want fewer items per page. The key problem is that the first row number of the next page MUST BE CONSISTENT.

WILL NOT WORK:
Data__Page__Row
Data____1____1
Data____1____2
Data____2____3
Data____2____4
Data____2____5

WOULD WORK:
Data__Page__Row
Data____1____1
Data____1____2
Data____2____4
Data____2____5
Data____2____6

WOULD ALSO WORK:
Data__Page__Row
Data____1____1
Data____1____2
Data____2____1
Data____2____2
Data____2____3

I guess I could write some sort of Append query loop to generate a temporary table to achieve this, but that seems clunky. Any suggestions for ways to maybe modify the row numbering module to break the sequence at each page number?

My module:

Option Compare Database
Option Explicit

'Module to add row numbers field to query
'From YouTube The-Good-Fox
'https://www.youtube.com/watch?v=HWbpzETe-M0
'which takes from: https://accessexperts.com/blog/2015/07/28/ways-to-do-sequential-numbering-in-access/
'#################################
Private lngRowNumber As Long
Private colPrimaryKeys As VBA.Collection
 
Public Function ResetRowNumber() As Boolean
  Set colPrimaryKeys = New VBA.Collection
  lngRowNumber = 0
  ResetRowNumber = True
End Function
 
Public Function RowNumber(UniqueKeyVariant As Variant) As Long
  Dim lngTemp As Long
 
  On Error Resume Next
  lngTemp = colPrimaryKeys(CStr(UniqueKeyVariant))
  If Err.Number Then
    lngRowNumber = lngRowNumber + 1
    colPrimaryKeys.Add lngRowNumber, CStr(UniqueKeyVariant)
    lngTemp = lngRowNumber
  End If
 
  RowNumber = lngTemp
End Function
'################################

My row-numbered query (Grad_Plot_Grp is the field I'd like to break on. The user enters 1, 2, etc. to select which page the data will plot on):

SELECT DISTINCTROW Grad_Plot_Query.Grad_Plot_Grp, Project_Info.Project_Name, Project_Info.Project_Number, Collars.Collar_ID, Collars.Collar_Num, Results_Query.Samp_From, Results_Query.Samp_To, Results_Query.Samp_ID, Results_Query.GTest_ID, Grad_Plot_Query.Grd_Plot_Path, RowNumber([Results_Query].[GTest_ID]) AS RowNum, Grad_Plot_Query.Plot_Grad
FROM Project_Info INNER JOIN ((Collars INNER JOIN Results_Query ON Collars.Collar_ID = Results_Query.Collar_ID) INNER JOIN Grad_Plot_Query ON Results_Query.GTest_ID = Grad_Plot_Query.GTest_ID) ON Project_Info.Project_ID = Collars.Project_ID
WHERE (((Grad_Plot_Query.Plot_Grad)=True) AND ((ResetRowNumber())<>False))
ORDER BY Grad_Plot_Query.Grad_Plot_Grp, Collars.Collar_ID;

 

[CJ_London]

your last example is probably the easiest to achieve. Either using the dmax+1 basis or an adaptation of the function to pass though the page as a parameter snf compare against a static variable, and reset if changed.

For reports you should not need the vba collection

something like

Public Function RowNumber(PageNum as long, UniqueKeyVariant As Variant) As Long
Dim lngTemp As Long
Static lngVal as long

On Error Resume Next
if pagenum<>lngVal then
    lngVal=PageNum
    rownumber=0
end if
rownumber=rownumber+1
…
...

 

[snow-raven]

Thanks!

I'm actually exporting a query to a text doc to import into a graphing software (as Access cracks up when I try to make these plots), so I think I need the VBA collection?

I'm still struggling with this one. I feel a bit like a little kid who was handed a pile of strings & told to play cat's cradle with my feet. However, I poked around with your idea and came up with something that worked! (Wahoo for brute force!):

Private colPrimaryKeys As VBA.Collection
 
Public Function ResetRowNumber() As Boolean
  Set colPrimaryKeys = New VBA.Collection
  lngRowNumber = 0
  ResetRowNumber = True
End Function
 
Public Function RowNumber(PageNum As Long, UniqueKeyVariant As Variant) As Long
  Dim lngTemp As Long
  Static lngVal As Long
  
  On Error Resume Next

'  MsgBox PageNum & lngVal

  lngTemp = colPrimaryKeys(CStr(UniqueKeyVariant))
  If Err.Number Then
    lngRowNumber = lngRowNumber + 1
    colPrimaryKeys.Add lngRowNumber, CStr(UniqueKeyVariant)
    lngTemp = lngRowNumber
  End If
  If PageNum <> lngVal Then
    lngVal = PageNum
'    MsgBox "Now" & lngVal
    lngTemp = 1
    lngRowNumber = 1
  End If

  RowNumber = lngTemp
End Function

Thanks so very much for your help.

 

[snow-raven]

I think it's the most beautiful thing...

 

[snow-raven]

One last thing? snf? I googled, but my google-fu failed me.

 

[CJ_London]

snf?

typo - should be and