Sequence records in an Access table

I need to sequence records in a Access table based on a field (Provider Name). All like Provider Names should have the same sequence number. For example if the are 5 Dickson INCs then they should have the same sequence number. Can you provide sample code? Maybe use an update query. Thanks
shieldscoAsked:
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.

John TsioumprisSoftware & Systems EngineerCommented:
So you want to create your own "AutoNumber"...if this is the case i have used in the past something like this on the On Current Event
If me.NewRecord then
Me.ProviderNameID= Nz(DMax("ProviderNameID", "Providers"), 0) + 1
End if 

Open in new window

Make the necessary adjustments and you will be fine.
If you already have the data and you want to insert the "sequence"...you need to make a small method that will iterate all records and calculate the sequence...
0
shieldscoAuthor Commented:
I already have the data and need to insert the sequence... do you have a sample on sequencing like Provider Names
0
PatHartmanCommented:
Is this a one time issue where you need to assign ID's to existing text fields?  Or is this something else?  Maybe it's both.  You have to fix up existing data and you need to do something each time you add a new item.

We have no where near enough information to provide a rational solution.  All we are doing is guessing.

Assuming you are adding primary keys you will also have to replace the text foreign key with the new ID AND fix up your RI to link the numeric fields and fix up all your queries to join on the numeric fields.
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.

shieldscoAuthor Commented:
I just need to add a sequence number to a table for all like provider names.

Provider Name      Sequence Number
Jonce Co                   1
Jonce Co                   1
Jonce Co                   1
Smith LLC                  2
Smith LLC                  2
LinC Inc                     3
K&L Inc                      8
K&L Inc                      8
K&L Inc                      8
0
shieldscoAuthor Commented:
need to update the same number to the sequence field where the Provider Name is the same....
0
PatHartmanCommented:
Why would you not just use the PK of the provider table?  You do have a Provider table, don't you?
0
John TsioumprisSoftware & Systems EngineerCommented:
Take this for a spin
Public Function SequenceNumbers()
    Dim rst As DAO.Recordset
    Dim seqCounter As Integer
    Dim currentProvider As String
    Dim initiated As Boolean
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM YOURTABLE ORDER BY [Provider Name]")
    With rst
        .MoveFirst
        While Not .EOF

            If initiated Then
                .Edit
                If .Fields("Provider Name") = currentProvider Then
                'Keep eveything the same
                Else
                    currentProvider = .Fields("Provider Name")
                    seqCounter = seqCounter + 1
                End If
                .Fields("Sequence Number") = seqCounter
                .Update

            Else
                currentProvider = .Fields("Provider Name")
                .Edit
                seqCounter = 1
                .Fields("Sequence Number") = seqCounter
                initiated = True
                .Update
            End If
            .MoveNext
        Wend
    End With
    rst.Close
    Set rst = Nothing
End Function

Open in new window

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
Gustav BrockCIOCommented:
I have posted my RowCounter function dozens of time, but here it is again.

As it can create numbers by any ID, also text, it will work out of the box for you, just use the [Provider Name] as ID and leave out CStr() as the "ID" already is a string:

SELECT RowCounter([Provider Name],False) AS [Sequence Number], *
FROM YourTable
WHERE (RowCounter([Provider Name],False) <> RowCounter("",True));

Open in new window


Public Function RowCounter( _
  ByVal strKey As String, _
  ByVal booReset As Boolean, _
  Optional ByVal strGroupKey As String) _
  As Long
  
' Builds consecutive RowIDs in select, append or create query
' with the possibility of automatic reset.
' Optionally a grouping key can be passed to reset the row count
' for every group key.
'
' Usage (typical select query):
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' Usage (with group key):
'   SELECT RowCounter(CStr([ID]),False,CStr[GroupID])) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' The Where statement resets the counter when the query is run
' and is needed for browsing a select query.
'
' Usage (typical append query, manual reset):
' 1. Reset counter manually:
'   Call RowCounter(vbNullString, False)
' 2. Run query:
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable;
'
' Usage (typical append query, automatic reset):
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter("",True)=0);
'
' 2002-04-13. Cactus Data ApS. CPH
' 2002-09-09. Str() sometimes fails. Replaced with CStr().
' 2005-10-21. Str(col.Count + 1) reduced to col.Count + 1.
' 2008-02-27. Optional group parameter added.
' 2010-08-04. Corrected that group key missed first row in group.

  Static col      As New Collection
  Static strGroup As String
  
  On Error GoTo Err_RowCounter
  
  If booReset = True Then
    Set col = Nothing
  ElseIf strGroup <> strGroupKey Then
    Set col = Nothing
    strGroup = strGroupKey
    col.Add 1, strKey
  Else
    col.Add col.Count + 1, strKey
  End If
  
  RowCounter = col(strKey)
  
Exit_RowCounter:
  Exit Function
  
Err_RowCounter:
  Select Case Err
    Case 457
      ' Key is present.
      Resume Next
    Case Else
      ' Some other error.
      Resume Exit_RowCounter
  End Select

End Function

Open in new window

0
Fabrice LambertFabrice LambertCommented:
Hmm, that fact that you need to duplicate data in a single time indicate a design flaw.

As Pat mentionned, it will be better to split your data in 2 table with a one to many Relationship linking them.

Everything else will be about querying the table.
0
Gustav BrockCIOCommented:
An update query would look like this:

UPDATE YourTable 
SET [Sequence Number] = RowCounter([Provider Name], False)

Open in new window

0
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.