compare zips between 2 tables/insert

COwebmaster
COwebmaster used Ask the Experts™
on
Ok, I'm trying to do the following:

I have a table called zipTable holding 4 fields:

code     zip      roundrobin   counter  (header)

abcd   80001          2              0
adfr    80002          4              0
etrg    80003         12             0
hjuy    80004          0              0
qwse  80005          3              0


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

fname    zip

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.

This is the first of several.  Thanks for your input and help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2006

Commented:
Does csvTable have a field already to accept the code?

Author

Commented:
I just tried to edit the question but it wouldn't let me.  Here's the rephrase:

Ok, I'm trying to do the following:

I have a table called zipTable holding 4 fields:

code     zip      roundrobin   counter  (header)

abcd   80001          2              0
adfr    80002          4              0
etrg    80003         12             0
hjuy    80004          0              0
qwse  80005          3              0


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 using field counter in zipTable, cycle through based on the number in field roundrobin.

This is the first of several.  Thanks for your input and help.



To answer your question, yes.
Top Expert 2006

Commented:
Ok if this is in VBA? Easier to loop through recordsets that way. You'll need a form and a button.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Top Expert 2006

Commented:
let me get this straight, do u want to rewrite the csv file?
You cant directly write to a csv file if u link it in as a table

What I think u might have to do is this

1. Open csv file
2. Read one line at a time
3. Check codes, if different update code
4. write line just read and/or updated to new file
5. loop until done
6. copy new csv file over old csv file



alternative is to rerun the query that csv was based on and just overwrite existing csv

Author

Commented:
Yes, it can be in vba.  What will be in the form?

Author

Commented:
rockiroads, ok so how can I implement that?  Also, did you and others understand the whole roundrobin thing?

Author

Commented:
So let's say I then have all these records in csvTable (with field code filled in each record), I then need to ultimately parse out those records by code into their own csv files that I will upload to a file server.  I can treat that as another question though if it's out of scope here.
Top Expert 2006

Commented:
I can do that for u no problem based on the steps 1-6
right now though Im just leaving work, wont be back online for a couple of hours

if my understanding is correct then its a easy task, someone else may even write it for u in the meantime !!! (Jeff???)
Top Expert 2006

Commented:
something like this aught to do

Function AddCodes()
    Dim rsCSV As DAO.Recordset
    Dim rsZIP As DAO.Recordset
    Dim strCSV As String
    Dim strZIP As String
    Dim sZip As String
    Dim sCode As String
    strZIP = "Select * from zipTable;"
    Set rsZIP = CurrentDb.OpenRecordset(strZIP)
        If rsZIP.EOF Then
            MsgBox "No Zip Records to compare"
            rsZIP.Close
            Set rsZIP = Nothing
        Exit Function
    rsZIP.MoveFirst
    Do Until rsZIP.EOF
        sZip = rsZIP.Fields("Zip").Value
        sCode = rsZIP.Fields("Code").Value
            strCSV = "Select * from csvTable where zip = " & sZip & ";"
            Set rsCSV = CurrentDb.OpenRecordset(strCSV)
            If rsCSV.EOF Then
                rsCSV.Close
                Set rsCSV = Nothing
                GoTo NextOne
            End If
            rsCSV.MoveFirst
            Do Until rsCSV.EOF
                rsCSV.Fields("Code").Value = sCode
                rsCSV.MoveNext
            Loop
            rsCSV.Close
            Set rsCSV = Nothing
NextOne:
        rsZIP.MoveNext
    Loop
    rsZIP.Close
    Set rsZIP = Nothing
    MsgBox "All Done"
End Function

Author

Commented:
Jeff, do I add this logic to the button on the form?
Top Expert 2006

Commented:
Yep...on the On_Click event


also, change this one line

 strZIP = "Select * from zipTable;"
 to

 strZIP = "Select DISTINCT Zip,Code from zipTable;"

That way it won't loop through every record in your zip table...just the ones where those two fields together are unique

J
Top Expert 2006

Commented:
Paste the function into a CODE module...not the form's module.

then with the On_Click event you just call it like this

Sub Command1_OnClick()
AddCodes
end sub

Author

Commented:
Ok, let me try that now..

Author

Commented:
I'm getting a Block If without End If compile error on this:

Function AddCodes()
    Dim rsCSV As DAO.Recordset
    Dim rsZIP As DAO.Recordset
    Dim strCSV As String
    Dim strZIP As String
    Dim sZip As String
    Dim sCode As String
    strZIP = "Select DISTINCT Zip,Code from zipTable;"
    Set rsZIP = CurrentDb.OpenRecordset(strZIP)
        If rsZIP.EOF Then
            MsgBox "No Zip Records to compare"
            rsZIP.Close
            Set rsZIP = Nothing
        Exit Function
    rsZIP.MoveFirst
    Do Until rsZIP.EOF
        sZip = rsZIP.Fields("Zip").Value
        sCode = rsZIP.Fields("Code").Value
            strCSV = "Select * from csvTable where zip = " & sZip & ";"
            Set rsCSV = CurrentDb.OpenRecordset(strCSV)
            If rsCSV.EOF Then
                rsCSV.Close
                Set rsCSV = Nothing
                GoTo NextOne
            End If
            rsCSV.MoveFirst
            Do Until rsCSV.EOF
                rsCSV.Fields("Code").Value = sCode
                rsCSV.MoveNext
            Loop
            rsCSV.Close
            Set rsCSV = Nothing
NextOne:
        rsZIP.MoveNext
    Loop
    rsZIP.Close
    Set rsZIP = Nothing
    MsgBox "All Done"
End Function
Top Expert 2006

Commented:
Function AddCodes()
    Dim rsCSV As DAO.Recordset
    Dim rsZIP As DAO.Recordset
    Dim strCSV As String
    Dim strZIP As String
    Dim sZip As String
    Dim sCode As String
    strZIP = "Select DISTINCT Zip,Code from zipTable;"
    Set rsZIP = CurrentDb.OpenRecordset(strZIP)
        If rsZIP.EOF Then
            MsgBox "No Zip Records to compare"
            rsZIP.Close
            Set rsZIP = Nothing
        Exit Function
        End If                      <------need this
    rsZIP.MoveFirst
    Do Until rsZIP.EOF
        sZip = rsZIP.Fields("Zip").Value
        sCode = rsZIP.Fields("Code").Value
            strCSV = "Select * from csvTable where zip = " & sZip & ";"
            Set rsCSV = CurrentDb.OpenRecordset(strCSV)
            If rsCSV.EOF Then
                rsCSV.Close
                Set rsCSV = Nothing
                GoTo NextOne
            End If
            rsCSV.MoveFirst
            Do Until rsCSV.EOF
                rsCSV.Fields("Code").Value = sCode
                rsCSV.MoveNext
            Loop
            rsCSV.Close
            Set rsCSV = Nothing
NextOne:
        rsZIP.MoveNext
    Loop
    rsZIP.Close
    Set rsZIP = Nothing
    MsgBox "All Done"
End Function

Author

Commented:
Ok, did that now getting:

Function AddCodes()
    Dim rsCSV As DAO.Recordset
    Dim rsZIP As DAO.Recordset
    Dim strCSV As String
    Dim strZIP As String
    Dim sZip As String
    Dim sCode As String
    strZIP = "Select DISTINCT Zip,Code from zipTable;"
    Set rsZIP = CurrentDb.OpenRecordset(strZIP)
        If rsZIP.EOF Then
            MsgBox "No Zip Records to compare"
            rsZIP.Close
            Set rsZIP = Nothing
        Exit Function
        End If
    rsZIP.MoveFirst
    Do Until rsZIP.EOF
        sZip = rsZIP.Fields("Zip").Value
        sCode = rsZIP.Fields("Code").Value
            strCSV = "Select * from csvTable where Zip = " & sZip & ";"
            Set rsCSV = CurrentDb.OpenRecordset(strCSV)    <---- this line saying "Datatype mismatch in criteria expression"
            If rsCSV.EOF Then
                rsCSV.Close
                Set rsCSV = Nothing
                GoTo NextOne
            End If
            rsCSV.MoveFirst
            Do Until rsCSV.EOF
                rsCSV.Fields("Code").Value = sCode
                rsCSV.MoveNext
            Loop
            rsCSV.Close
            Set rsCSV = Nothing
NextOne:
        rsZIP.MoveNext
    Loop
    rsZIP.Close
    Set rsZIP = Nothing
    MsgBox "All Done"
End Function




Top Expert 2006

Commented:
is your ZIP field text?
Top Expert 2006

Commented:

if it is, change to this
strCSV = "Select * from csvTable where Zip = '" & sZip & "';"  <---notice the tics '

Author

Commented:
Yes, datatype is Text for all fields.  The reason being is because Canadian zips are in there too.

Author

Commented:
Ok, did that but another error.  It says "Update or CancelUpdate without AddNew or Edit".

Function AddCodes()
    Dim rsCSV As DAO.Recordset
    Dim rsZIP As DAO.Recordset
    Dim strCSV As String
    Dim strZIP As String
    Dim sZip As String
    Dim sCode As String
    strZIP = "Select DISTINCT Zip,Code from zipTable;"
    Set rsZIP = CurrentDb.OpenRecordset(strZIP)
        If rsZIP.EOF Then
            MsgBox "No Zip Records to compare"
            rsZIP.Close
            Set rsZIP = Nothing
        Exit Function
        End If
    rsZIP.MoveFirst
    Do Until rsZIP.EOF
        sZip = rsZIP.Fields("Zip").Value
        sCode = rsZIP.Fields("Code").Value
            strCSV = "Select * from csvTable where Zip = '" & sZip & "';"
            Set rsCSV = CurrentDb.OpenRecordset(strCSV)
            If rsCSV.EOF Then
                rsCSV.Close
                Set rsCSV = Nothing
                GoTo NextOne
            End If
            rsCSV.MoveFirst
            Do Until rsCSV.EOF
                rsCSV.Fields("Code").Value = sCode   <---- this line is highlighted
                rsCSV.MoveNext
            Loop
            rsCSV.Close
            Set rsCSV = Nothing
NextOne:
        rsZIP.MoveNext
    Loop
    rsZIP.Close
    Set rsZIP = Nothing
    MsgBox "All Done"
End Function
Top Expert 2006
Commented:
Should do it...haveing fun? :o)

Function AddCodes()
    Dim rsCSV As DAO.Recordset
    Dim rsZIP As DAO.Recordset
    Dim strCSV As String
    Dim strZIP As String
    Dim sZip As String
    Dim sCode As String
    strZIP = "Select DISTINCT Zip,Code from zipTable;"
    Set rsZIP = CurrentDb.OpenRecordset(strZIP)
        If rsZIP.EOF Then
            MsgBox "No Zip Records to compare"
            rsZIP.Close
            Set rsZIP = Nothing
        Exit Function
        End If
    rsZIP.MoveFirst
    Do Until rsZIP.EOF
        sZip = rsZIP.Fields("Zip").Value
        sCode = rsZIP.Fields("Code").Value
            strCSV = "Select * from csvTable where Zip = '" & sZip & "';"
            Set rsCSV = CurrentDb.OpenRecordset(strCSV)
            If rsCSV.EOF Then
                rsCSV.Close
                Set rsCSV = Nothing
                GoTo NextOne
            End If
            rsCSV.MoveFirst
            Do Until rsCSV.EOF
                rsCSV.Edit
                rsCSV.Fields("Code").Value = sCode   <---- this line is highlighted
                rsCSV.Update
                rsCSV.MoveNext
            Loop
            rsCSV.Close
            Set rsCSV = Nothing
NextOne:
        rsZIP.MoveNext
    Loop
    rsZIP.Close
    Set rsZIP = Nothing
    MsgBox "All Done"
End Function
Top Expert 2006

Commented:
Hello Im back. It looks like Jeff is solving it.
Is this csv file linked or imported? I was under the impression u just had a csv file and one table.

Author

Commented:
Still getting that error.

The csv file is imported.  Should it be linked?

I have 2 tables:

zipTable
 --> Zip  Code (both Text fields)

csvTable
 --> fname  Zip  Code  (All Text fields)

I have 1 form with button.  on button (On Click), I have:

Private Sub Command0_Click()
AddCodes
End Sub


Created a Module.  In it:

Dim rsCSV As DAO.Recordset
    Dim rsZIP As DAO.Recordset
    Dim strCSV As String
    Dim strZIP As String
    Dim sZip As String
    Dim sCode As String
    strZIP = "Select DISTINCT Zip,Code from zipTable;"
    Set rsZIP = CurrentDb.OpenRecordset(strZIP)
        If rsZIP.EOF Then
            MsgBox "No Zip Records to compare"
            rsZIP.Close
            Set rsZIP = Nothing
        Exit Function
        End If
    rsZIP.MoveFirst
    Do Until rsZIP.EOF
        sZip = rsZIP.Fields("Zip").Value
        sCode = rsZIP.Fields("Code").Value
            strCSV = "Select * from csvTable where Zip = '" & sZip & "';"
            Set rsCSV = CurrentDb.OpenRecordset(strCSV)
            If rsCSV.EOF Then
                rsCSV.Close
                Set rsCSV = Nothing
                GoTo NextOne
            End If
            rsCSV.MoveFirst
            Do Until rsCSV.EOF
                rsCSV.Fields("Code").Value = sCode
                rsCSV.MoveNext
            Loop
            rsCSV.Close
            Set rsCSV = Nothing
NextOne:
        rsZIP.MoveNext
    Loop
    rsZIP.Close
    Set rsZIP = Nothing
    MsgBox "All Done"
End Function

Author

Commented:
Wait..it worked!!!

Author

Commented:
Ok, I guess that last one did it Jeff..good job.  One thing though on this post...I need to deal with the round robin zip assignment and that is if a record does not match, then it should still cycle through and populate records BUT it uses a round robin proces that's based on a roundrobin (rr) number.  These numbers will be stored in a thrid table called rrTable. So, it would look like:

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 to see where the flag (c) was stopped in rrTable and as in the example above, flag is stopped at abcd.  Now rr = 2 and counter = 0, so counter = 1 and that record in csvTable would be abcd.  On the next record without a matching zip, it looks at rrTable, sees where flag c is and counter=counter+1.  Now if counter equals rr, then flag moves to next record.  In this case above, code etrg.  And it would continually loop through cycle.  Let me know if I'm not clear or if you think I should post this as a new question.
Top Expert 2006

Commented:
the code you're using ignores the "No Matches" completely. It never loops through everything. Let me explain.

I open up my outer recordset based on unique Zip & Code values.

Using your example
code     zip      roundrobin   counter  (header)

abcd   80001          2              0
abcd   80001          4              0
etrg    80003         12             0
hjuy    80004          0              0
qwse  80005          3              0

See....there are two combinations where abcd and 80001 exist...so I'm just grabbing one. (DISTINCT)

I then use 80001 to select all records from CSV that have that as a zipcode.
 If it doesn't find any, it goes to the next ZIP record

It's effectively pulling ONLY matches...thus ignoring non-matches completely.

Let me take a closer look at what you're asking in this last Q.
J

Author

Commented:
Hi, any more on what I'm proposing?  Let me ask as a seperate question I guess.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial