call this sub routine like this
AssignRowNum "table1"
replace table1 with your table name.
if the num column is null it will pick it up and try to put the pattern numbers in. Then run the cross tab query already defined and we should be ok.
Code:
Public Sub AssignRowNum(datTableName As String)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim cnt As Long
Dim lastID As Long
Set db = CurrentDb()
strSQL = "Select * from " & datTableName & " where num is NULL order by ID, inkmark"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
If rst.RecordCount > 0 Then
rst.MoveFirst
lastID = rst!ID
cnt = 0
Do While Not rst.EOF
If lastID = rst!ID Then
cnt = cnt + 1
rst.Edit
rst!num = cnt
rst.Update
Else
cnt = 1
rst.Edit
rst!num = cnt
rst.Update
lastID = rst!ID
End If
rst.MoveNext
Loop
End If
End Sub