We help IT Professionals succeed at work.

Zip: round robin using flag/counter

COwebmaster
COwebmaster asked
on
Medium Priority
438 Views
Last Modified: 2008-03-06
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?
Comment
Watch Question

Commented:
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.
COwebmasterWeb Manager

Author

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

Author

Commented:
Any ideas out there?  Or does anyone know of an off-the-shelf solution for purchase?
COwebmasterWeb Manager

Author

Commented:
Any ideas?

Commented:
I've called for help.
COwebmasterWeb Manager

Author

Commented:
Ok, thanks GRayL.
Database Developer
CERTIFIED EXPERT
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
COwebmasterWeb Manager

Author

Commented:
Thanks  LPurvis, let me try this..
COwebmasterWeb Manager

Author

Commented:
Sorry, I've been real busy.  I still need to test this..
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
<shrugs>
Should work.
(As I said though - not particularly pretty :-)

Commented:
You or the code?
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
Oh I'd like to think that my code is *usually* purdier than me. :-)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.