Adding sequential number to each group of data in query

[billmark]

I have a query 2000+ records in an access 2003 database. Each record is assigned a category in a field called “Fund” (for example, “SCUAD” or “4579”)



What I want is to add an incremental count to each record for each Fund. For example, if there were 25 records with Fund = SCUAD, the first record with that code would be numbered 1, and the last would have the number 25. Or, if there were 70 records with Fund = 4579, each record in that group would need a sequence number starting with 1 and ending with 70.

Please help how to code using vba or just using another query.

 

[BlueIshDan]

This would require some VBA, is that ok for you?

 

[BlueIshDan]

Private Sub SetIncrements()
    
    Dim rs As Recordset: Set rs = CurrentDb.OpenRecordset("tblData")
    
    Dim dict_scuad_variants As New Dictionary '// Reference: Microsoft Scripting Runtime
    Dim tmp_str As String
    
    While Not rs.EOF
    
        tmp_str = CStr(rs!scuad)
        
        If Not dict_scuad_variants.Exists(tmp_str) Then: dict_scuad_variants.Add tmp_str, 0
        
        dict_scuad_variants(tmp_str) = dict_scuad_variants(tmp_str) + 1
        
        rs.Edit
            rs!inc = dict_scuad_variants(tmp_str)
        rs.Update
        rs.MoveNext
        
    Wend
    
    rs.Close
    
End Sub

 

[CJ_London]

the concept of first/last in databases (any database) is meaningless without some context of order - perhaps date order or an autonumber order

So to get a sequence you need an order on a field which is unique or at least unique within a subset (Fund in this case) - for the purposes of this example, I'll assume you have an autonumber field called ID

 SELECT *, (SELECT Count(*) FROM myTable as T WHERE Fund=myTable.Fund and ID<=myTable.ID) AS Sequence
 FROM myTable
 ORDER BY Fund, ID

 

[BlueIshDan]

This depends on the source and how your getting this data.
If the data comes from an external source that you're not able to alter its generation, you're going to have to run the function routinely.

If you're able to alter the generation of the data, you can run the function I provided once, then with the generation of each record do a Count(*) + 1 where scuad match. Or separate each into their own tables and do a join later on. Depends if you're really going for performance or not.

Btw, what is scuad? lol

 

[billmark]

Hi
I tried the vba code but got the error message as it stopped in the statement:
Dim dict_scuad_variants
Error message is 'user not defined'
The source is from my query from the table.

I expect the outcome something likes:
Record Fund
1 Scuad
2 Scuad
3 Scuad
1 4579
2 4579

Please advise.

 

[BlueIshDan]

The comments beside it state to "'// Reference: Microsoft Scripting Runtime"