Creating Unique Internal Counts

CPGDeveloper

CPGDeveloper

Board Regular
Joined
Oct 8, 2008
Messages
189
I have a table similar to this:

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION></CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>CUST_ID</TH><TH bgColor=#c0c0c0 borderColor=#000000>Acct</TH><TH bgColor=#c0c0c0 borderColor=#000000>Count</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5>222176058</TD><TD borderColor=#d0d7e5>7045821651</TD><TD borderColor=#d0d7e5 align=right>4</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>222176058</TD><TD borderColor=#d0d7e5>7045625285</TD><TD borderColor=#d0d7e5 align=right>4</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>222176058</TD><TD borderColor=#d0d7e5>7045627142</TD><TD borderColor=#d0d7e5 align=right>4</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>222176058</TD><TD borderColor=#d0d7e5>7049751496</TD><TD borderColor=#d0d7e5 align=right>4</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>222186390</TD><TD borderColor=#d0d7e5>9103089368</TD><TD borderColor=#d0d7e5 align=right>5</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>222186390</TD><TD borderColor=#d0d7e5>9103089293</TD><TD borderColor=#d0d7e5 align=right>5</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>222186390</TD><TD borderColor=#d0d7e5>9103089429</TD><TD borderColor=#d0d7e5 align=right>5</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>222186390</TD><TD borderColor=#d0d7e5>9103089469</TD><TD borderColor=#d0d7e5 align=right>5</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5>222186390</TD><TD borderColor=#d0d7e5>9108180602</TD><TD borderColor=#d0d7e5 align=right>5</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

I want to create another column which would act as a sequence number...so for the first 4 records the field would be popoulated with 1,2,3,4 and the next 5 records 1,2,3,4,5.

Any ideas or suggestions on how to get started?

Many Thanks!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Like so:

Excel Workbook
ABCD
1CUST_IDAcctCountINDEX
2222176058704582165141
3222176058704562528542
4222176058704562714243
5222176058704975149644
6222186390910308936851
7222186390910308929352
8222186390910308942953
9222186390910308946954
10222186390910818060255
Sheet2
 
Upvote 0
Thank You -- I didn't think to use Excel but that makes perfect sense...I'll do that...

Just out of curiosity, do you know how I might accomplish the same thing in an Access Query? or VBA perhaps?
 
Upvote 0
Yes, you can do it in Access VBA.

I have done this using DAO RecordSets. Here is what the code might look like:
Code: 
Public Sub CreateSeqNum()
 
    On Error GoTo ErrHandler
    Dim MyDb As DAO.Database
    Dim MyRS As DAO.Recordset
    Dim strOldID As String
    Dim strNewID As String
    Dim intCounter As String
 
    Set MyDb = CurrentDb
    Set MyRS = CurrentDb.OpenRecordset("Query1", dbOpenDynaset)
 
    With MyRS
        If .BOF And .EOF Then
            MsgBox "No Records in Table"
            GoTo ExitTransform
        End If
        .MoveFirst
 
        strOldID = "XX"
        intCounter = 1
 
        Do While Not .EOF
            strNewID = .Fields("Cust_ID")
            If strNewID = strOldID Then
                intCounter = intCounter + 1
            Else
                intCounter = 1
            End If
            .Edit
            .Fields("Seq") = intCounter
            .Update
            .MoveNext
            strOldID = strNewID
        Loop
    End With
 
ExitTransform:
    Set MyRS = Nothing
    Set MyDb = Nothing
 
    Exit Sub
ErrHandler:
    MsgBox "ERR # " & Err & " - " & Error$
    Resume Next
 
End Sub
You need to make sure you select the Microsoft DAO Object Library from References in VBA to use DAO Recordsets.

In a nutshell, set-up a query in which you have your records sorted in the order you want (by Cust_ID first, obviously). I named this query "Query1" (you can see it referenced in the code). You must also make sure that to include your blank "Sequence" field in this query (I used the field name "Seq").

I then put a command button an a form to call this procedure, i.e.
Code: 
Private Sub Command0_Click()
    CreateSeqNum
    MsgBox "Records Updated"
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

MCTampa
  • Question Question
Grouping Consecutive Values by Min and Max
Replies
2
Views
1K
MCTampa
MCTampa
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
G
  • Question Question
Counting unique instances given another criteria
Replies
1
Views
896
xenou
xenou
D
  • Question Question
Create a query that searches many columns and returns many values
Replies
6
Views
547
Deirdre
D
A
  • Question Question
Help editing VBA for Access
2
Replies
12
Views
1K
xenou
xenou

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top