Output auto-numbered text file with groups (1 Viewer)

snow-raven

Registered User.
Local time
Today, 08:08
Joined
Apr 12, 2018
Messages
48
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:
Code:
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):
Code:
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

Super Moderator
Staff member
Local time
Today, 15:08
Joined
Feb 19, 2013
Messages
16,553
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

Code:
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

Registered User.
Local time
Today, 08:08
Joined
Apr 12, 2018
Messages
48
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!):

Code:
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

Registered User.
Local time
Today, 08:08
Joined
Apr 12, 2018
Messages
48
I think it's the most beautiful thing...
 

Attachments

  • Capture1.JPG
    Capture1.JPG
    18.3 KB · Views: 213

snow-raven

Registered User.
Local time
Today, 08:08
Joined
Apr 12, 2018
Messages
48
One last thing? snf? I googled, but my google-fu failed me.
 

Users who are viewing this thread

Top Bottom