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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

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


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

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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!
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.