Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Zip: round robin using flag/counter

Posted on 2006-10-30
14
Medium Priority
?
401 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

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
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 2000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

916 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