filling table

[janco]

Hi fellows,
I am new in this forum, beginner in access and I have a problem that cannot be solved with my level of access knowledge. I need a querry, or a piece of vba code, whatever, that helps me to fill two additional fields in table. I am not going to write you exactly what kind of data I am working with, but I hope you will easily see the logic. And sorry for my english, as you probably have noticed, I am not good neither in access nor in english

I have a table with several filled fields and many records, some fields are not important, two of those important are primary key (pk), and, say, Names. I need to add two other columns, say col1 and col2 of Integer data type, so that the resulting table has the form:

pk | Names | col1 | col2
pk1 | A | 1 | 0
pk2 | A | 1 | 1
pk3 | A | 1 | 2
pk4 | A | 1 | 3
pk5 | B | 2 | 0
pk6 | C | 3 | 0
pk7 | C | 3 | 1
pk8 | C | 3 | 2
pk9 | C | 3 | 3
pk10 | C | 3 | 4


pk11 | C | 3 | 5
pk12 | D | 4 | 0
pk13 | D | 4 | 1
pk14 | D | 4 | 2
...
so the col1 contains the same number for all records with the same name, and the second column col2 contains, say, indices of the records starting with 0 for every record with different name.

I am sure, this is easy problem for many of you, but I really don't know how to figure it out. I know how to add a column, define its data type but its not enough
Thanks a lot
Jan

[rpeare]

Assume your table is called tbl_Data and the fields are exactly what you have listed paste this code into a module and run it:

Dim db As Database
Dim rst As Recordset
Dim sSQL As String
Dim sCurrNames As String
Dim sPrevNames As String
Dim iNamesCount As Integer
Dim iRecCount As Integer

Set db = CurrentDb
sSQL = "SELECT * FROM tbl_Data ORDER BY NAMES, PK"
Set rst = db.OpenRecordset(sSQL)
rst.MoveFirst
iNamesCount = 1
iRecCount = 0
rst.Edit
rst.Fields("col1").Value = iNamesCount
rst.Fields("col2").Value = iRecCount
rst.Update
sPrevNames = rst.Fields("names")
rst.MoveNext
Do While rst.EOF <> True
    sCurrNames = rst.Fields("names").Value
    If sCurrNames = sPrevNames Then
        iRecCount = iRecCount + 1
        rst.Edit
        rst.Fields("col1").Value = iNamesCount
        rst.Fields("col2").Value = iRecCount
        rst.Update
    Else
        iRecCount = 0
        iNamesCount = iNamesCount + 1
        rst.Edit
        rst.Fields("col1").Value = iNamesCount
        rst.Fields("col2").Value = iRecCount
        rst.Update
    End If
    
    sPrevNames = sCurrNames
    rst.MoveNext
Loop

[janco]

Thank you, it works perfectly!