Need to create a series identifier in Access 2003 table

I have an Access2003 table with three fields; Field 1 and 3 contain data and in the second field I wish to create an identifier -if- a series exists. By definition, a series is two or more records having identical data in Field1

BEFORE macro
Field1     Field2     Field3
a2c                       bacon
d1f                       steak, blade
d1f                       steak, t-bone
d1f                       steak, filet
ghi                       roast, tender
ghi                       roast, shoe-leather
jkl                        hamburger
pq5                      hotdogs, juicy
pq5                      hotdogs, tasteless

AFTER macro
Field1     Field2     Field3
a2c                       bacon
d1f          A           steak, blade
d1f          B           steak, t-bone
d1f          C           steak, filet
ghi          A            roast, tender
ghi          B            roast, shoe-leather
jkl                         hamburger
pq5         A           hotdogs, juicy
pq5         B           hotdogs, tasteless
Who is Participating?
This should get you started:
Option Explicit        ' Force explict variable declarations
Option Compare Binary  ' Use case-sensitive comparisons

Public Sub SetSeriesField()

    ' Declare varaibles
    Dim rstRecordset As DAO.Recordset  ' Record set
    Dim strField1 As String            ' Field1 value
    Dim strSeries As String            ' Series
    Dim strSQL As String               ' SQL

    ' Open record set
    strSQL = "SELECT * " & _
             "FROM YourTable " & _
             "WHERE Field1 IN (SELECT First(Field1) " & _
             "FROM YourTable " & _
             "GROUP BY Field1 " & _
             "HAVING Count(Field1) > 1)"
    Set rstRecordset = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

    ' Process records
    With rstRecordset
        Do Until .EOF

            ' Set Field2 value
            If !Field1 = strField1 Then
                strSeries = Chr(Asc(strSeries) + 1)  ' Works for A-Z only
                strField1 = !Field1
                strSeries = "A"
            End If
            !Field2 = strSeries

        ' Close record set
    End With
    Set rstRecordset = Nothing

End Sub

Open in new window

You'll need to do the following to get the code to work:

1. Add a Reference to the latest Microsoft DAO Object Library
2. Change both occurences of "YourTable" to your table name
Rey Obrero (Capricorn1)Commented:
try this query

select a.field1
,chr(64+(Select Count(b.field1) from tableA as b WHERE b.field1=a.field1 and b.field3<=a.field3)) as field2
from tableA as a

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.

By the way, the code is currently case sensitive for Field1 values - if you want to make it case insensitive, just change the second line to:

    Option Compare Text
Muskie12Author Commented:
I'll start adding in the code but I'm not sure how to add a reference...
In the VB editor, click Tools->References and then tick the check box next to the "Microsoft DAO Object Library" entry in the Available References list.
Muskie12Author Commented:
JezWalters - I made the requested changes and your code runs without error but nothing appeared in Field2 by way of an update.

Capricorn1 - your query basically works but I was looking for the series identifier to be created *only* if a series exists - right now, even a unique occurance of a Field1 code has an identifier created in Field2. Is it possible to adjust without much trouble?
Here's a sample database, showing my code in action:
Muskie12Author Commented:
Experts: my apologies for the delay, my life kinda got turned upside-down and am still working on getting fully back in the saddle. It will be a few more days before I can get back to this problem but I dont want to let this question hang on that long. So, I'd like to award the points as follows (and if there's an alternate suggestion, pls let me know):

JezWalters -
I couldn't get your solution to work in my dB but as your dB shows, your code does work so I have to assume the problem is mine, maybe I'll be able to find it. 450

Capricorn -
Your query does work although it misses in the one area, however I would likely have an easier time of working around that. 50

Many thanks for the help!
If you post a sample database that's nearer to what you've got, I'll adapt my code to suit.
Muskie12Author Commented:
Its going to be a while but if I need to, I'll repost.

Thanks again for your help!
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.

All Courses

From novice to tech pro — start learning today.