[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

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
0
olm4n
Asked:
olm4n
  • 5
  • 5
  • 3
  • +2
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now