Zip: round robin using flag/counter

Ok, so I have 3 tables in my Access db (zipTable, csvTable, rrTable).  I'm basically matching codes with zips between the 2 tables, zipTable and csvTable.  However, I now to figure this piece out..If a zip within zipTable does NOT match a zip inside csvTable, then it should go through a round robin process and assign the code based on where the flag 'c' is and counter in table rrTable.

rrTable:

id    code       rr         flag       counter

1    abcd         2          c             0
2    etrg          4          o             0
3    qwse         6         o             0


So lets say it comes across a record (as it's looping through it) in csvTable that has zip 90025 but that zip doesn't exist in zipTable, then it would look in rrTable o see where the flag (c) was stopped and as in the example above, flag is stopped at id 1  So as you can see, rr = 2 and counter = 0, so then counter would now equal 1 and that record in csvTable would insert "abcd" into field Code in csvTable.  On the next record without a matching zip, it looks at rrTable, sees where flag c is and again, counter=counter+1.  Now if counter equals rr, then flag moves to next record.  In this case above, code "etrg" would be inserted into that record.  Any thoughts how this could be accomplished?
COwebmasterAsked:
Who is Participating?
 
Leigh PurvisConnect With a Mentor Database DeveloperCommented:
Not exactly the prettiest code I've ever written...

Sub InsertRelatedCodes()

    Dim db As Database
    Dim rstR As DAO.Recordset
    Dim rstC As DAO.Recordset
   
    Set db = CurrentDb
   
    db.Execute "UPDATE csvTable INNER JOIN zipTable ON csvTable.zip = zipTable.zip " & _
                        "SET csvTable.code = zipTable.code"
   
    Set rstC = db.OpenRecordset("SELECT * FROM csvTable WHERE code Is Null", dbOpenDynaset)
    Set rstR = db.OpenRecordset("SELECT * FROM rrTable ORDER BY ID", dbOpenDynaset)
   
    rstR.FindFirst "flag = 'c'"
    If rstR.NoMatch Then Exit Sub
   
   
    Do Until rstC.EOF
        With rstR
            If !Counter >= !rr Then
                .Edit
                    !flag = "o"
                .Update
                .MoveNext
                If .EOF Then Exit Sub
                .Edit
                    !flag = "c"
                .Update
            End If
            .Edit
                !Counter = !Counter + 1
            .Update
        End With
        With rstC
            .Edit
                !Code = rstR!Code
            .Update
            .MoveNext
        End With
    Loop
   
    rstC.Close
    rstR.Close
    Set rstC = Nothing
    Set rstR = Nothing
    Set db = Nothing
   
End Sub
0
 
GRayLCommented:
To me, this is quite criptic.  Can you briefly describe the necessary fields in the three tables and how you have them joined? Are you "looping throught the CSVTable" and searching for zips in the ZIPTable joining the two tables on zip (code)  while simultaneously linking CSVTable to rrTable - on what?  Then before using rrTable code, you increment the counter and test against rr - etc. etc.
0
 
COwebmasterAuthor Commented:
Sorry, let me explain further:

I have a table called zipTable with 2 columns:

code     zip

abcd   80001
adfr    80002
etrg    80003
hjuy    80004
qwse  80005


Now I import a csv file into a table called csvTable:

fname     zip         code

Joe        80002
Mary     95002
Ken       80009
Billy       80004


I want to compare zips (each record) in csvTable with zips in zipTable.

Now if any zips in the csvTable matches a zip in zipTable, I need to insert the string in field code in zipTable into that record in csvTable.  If a zip in csvTable (like Mary and Ken above) does not match, then I am trying to do what I described above.


I was given the following script to insert into field code in csvTable but need to figure out this other piece.  See accepted answer:

http://www.experts-exchange.com/Databases/MS_Access/Q_22038767.html
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
COwebmasterAuthor Commented:
Any ideas out there?  Or does anyone know of an off-the-shelf solution for purchase?
0
 
COwebmasterAuthor Commented:
Any ideas?
0
 
GRayLCommented:
I've called for help.
0
 
COwebmasterAuthor Commented:
Ok, thanks GRayL.
0
 
COwebmasterAuthor Commented:
Thanks  LPurvis, let me try this..
0
 
COwebmasterAuthor Commented:
Sorry, I've been real busy.  I still need to test this..
0
 
Leigh PurvisDatabase DeveloperCommented:
<shrugs>
Should work.
(As I said though - not particularly pretty :-)
0
 
GRayLCommented:
You or the code?
0
 
Leigh PurvisDatabase DeveloperCommented:
Oh I'd like to think that my code is *usually* purdier than me. :-)
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.