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
Muskie12Asked:
Who is Participating?
 
JezWaltersCommented:
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
            Else
                strField1 = !Field1
                strSeries = "A"
            End If
            .Edit
            !Field2 = strSeries
            .Update
            .MoveNext
        Loop

        ' Close record set
        .Close
    End With
    Set rstRecordset = Nothing

End Sub

Open in new window

0
 
JezWaltersCommented:
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
0
 
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
,a.field3
from tableA as a

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.

 
JezWaltersCommented:
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
0
 
Muskie12Author Commented:
I'll start adding in the code but I'm not sure how to add a reference...
0
 
JezWaltersCommented:
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.
0
 
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?
0
 
JezWaltersCommented:
Here's a sample database, showing my code in action:
Q26921844.mdb
0
 
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!
0
 
JezWaltersCommented:
If you post a sample database that's nearer to what you've got, I'll adapt my code to suit.
0
 
Muskie12Author Commented:
Its going to be a while but if I need to, I'll repost.

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