Solved

Zip: round robin using flag/counter

Posted on 2006-10-30
14
386 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?
0
Comment
Question by:COwebmaster
  • 6
  • 3
  • 3
14 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 17837681
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
 

Author Comment

by:COwebmaster
ID: 17837795
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
 

Author Comment

by:COwebmaster
ID: 17854286
Any ideas out there?  Or does anyone know of an off-the-shelf solution for purchase?
0
 

Author Comment

by:COwebmaster
ID: 17902703
Any ideas?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17902849
I've called for help.
0
 

Author Comment

by:COwebmaster
ID: 17902861
Ok, thanks GRayL.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 500 total points
ID: 17904715
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
 

Author Comment

by:COwebmaster
ID: 17907064
Thanks  LPurvis, let me try this..
0
 

Author Comment

by:COwebmaster
ID: 18055412
Sorry, I've been real busy.  I still need to test this..
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18207085
<shrugs>
Should work.
(As I said though - not particularly pretty :-)
0
 
LVL 44

Expert Comment

by:GRayL
ID: 18208677
You or the code?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18209120
Oh I'd like to think that my code is *usually* purdier than me. :-)
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now