Solved

Zip: round robin using flag/counter

Posted on 2006-10-30
14
388 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

895 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

15 Experts available now in Live!

Get 1:1 Help Now