Auto number in Access

It is hard to explain but basically I have numbers set up like this in 5 columns. I need to number c5 based on c1 and c2. In this instance there are only three different sets based on c1/c2. I want to start at 1 and end up at whatvever number of rows there are in this data set. Essentially the next 3 rows in c5 will be 4 and so forth. Basically c1=x, c2=y,c3=z, c4=c(concentration) c5=subsurface log #. I am comfortable transfering code to Access VBA. The number is going to be in the millions for # of rows usually. The actual number in c5 will be in much larger increments than 3 also. Probably around 200 or so. So for 200 rows there will be a #1 in c5 and for the next 200 a #2 etc...

c1      c2      c3      c4      c5
12      14      10      1      1
12      14      11      3      1
12      14      12      4      1
13      15      10      7      2
13      15      11      8      2
13      15      12      3      2
10      21      10      5      3
10      21      11      7      3
10      21      12      9      3
olm4nAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ramromconsultant Commented:
unttested code:

dim rs as recordset, ctr as long, c1 as long, c2 as long
with rs
  .open("numberTable")
  .movefirst
  c1 = 0
  c2 = 0
  do while not .eof
    if  c1 <>  rs.c1 or c2 <> rs.c2 then
      ctr += 1
      c1 =  rs.c1
      c2 =  rs.c2
    end if
   .edit
   !c5 = ctr
  .update
 loop
end with

The only problem I see is the select might not be in the same order as the original table. You might
0
harfangCommented:
Hi,

You could use something like this...


Sub Renumber_c5()

    Dim cnn As ADODB.Connection
    Dim lngCountC5 As Long
    Dim lngThisC1 As Long   ' or double?
    Dim lngThisC2 As Long   ' or double?
    Dim lngPending As Long
   
    On Error GoTo ErrorMsg
    Set cnn = CurrentProject.Connection
    With New ADODB.Recordset
        .Open "Select * From tblHardToExplain Order By c1, c2", _
            ActiveConnection:=cnn, _
            CursorType:=adOpenKeyset, _
            LockType:=adLockOptimistic
           
        cnn.BeginTrans
        On Error GoTo Rollback
       
        Do Until .EOF
       
            If !c1 <> lngThisC1 Or !c2 <> lngThisC2 Then
                lngThisC1 = !c1
                lngThisC2 = !c2
                lngCountC5 = lngCountC5 + 1
            End If
            !c5 = lngCountC5
            .Update
            lngPending = lngPending + 1
           
            ' try to optimize the number here...
            If lngPending >= 5000 Then
                cnn.CommitTrans
                Debug.Print .AbsolutePosition
                DoEvents
                cnn.BeginTrans
                lngPending = 0
            End If
           
            .MoveNext
        Loop
       
        cnn.CommitTrans
        On Error GoTo ErrorMsg
       
        .Close
    End With
   
    Exit Sub
   
Rollback:
    cnn.RollbackTrans
ErrorMsg:
    MsgBox Err.Description
    Exit Sub

End Sub


Note: I added the transactions and the partial commits because you announced high numbers of records. See what suits you...

Hope this helps!
(°v°)
0
ramromconsultant Commented:
While harfang was posting his respoonse I was editing mine, since I had accidentally submitted it prematurely. We both have the same idea; his is more thorough.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

harfangCommented:
Yes, I saw that unfinished comment. Something with "Select distinct c1, c2 From numberTable". How did you edit your comment? I thought one could not do that...
(°v°)
0
olm4nAuthor Commented:
I am getting a "no value given for one or more required parameters" error message.
0
harfangCommented:
This was not exacly meant as cut-n-paste ready code...
Did you for instance adjust this: "Select * From tblHardToExplain Order By c1, c2"?
When stating an error message, the line where it happened is also quite important...

Cheers!
(°v°)
0
olm4nAuthor Commented:
I could also simply number c5 like this

r1 to r3 = 1
r4 to r6 = 2
r7:r9 = 3

etc..

I guess it is just as dependent on the # of rows that will be named. The pattern will always be the same. in the example above it is 3.
0
olm4nAuthor Commented:
Yeah I changed the table name. The first line is where I get an error it says when I do a "debug step into" but I don't believe it. If I don't change the table name from tblHardtoexplain to the proper one I get an error saying it cannot find this tbl.
0
olm4nAuthor Commented:
I opened my .dbf in Minitab and it did exactely what I needed above. It was too easy there. Thanks for all your help. Minitab is pretty quick at sorting too.
0
hnasrCommented:
So you solved it!
0
harfangCommented:
Well, I guess we don't have to debug the above in that case... I know very little about Minitab, so I'm sitll worried about the "millions for # of rows usually". Can the datasheet handle that?
Good luck in any case,
(°v°)
0
ramromconsultant Commented:
"How did you edit your comment?" I am an EE Page Editor. Page Editors can edit comments.
0
olm4nAuthor Commented:
I was able to do what I wanted with minitab and yes it will open 12 million rows if needed. Minitab is generally a statistics package but I am starting to use it for data manipulaiton too.
0
harfangCommented:
Obviously, your data  comes from Minitab anyway. If it can handle the heat, stick with it :)
Good luck!
(°v°)

[ramrom: I should have known. that would be the only way...]
0
ee_ai_constructCommented:
PAQ / Refund
ee ai construct, community support moderator
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.