• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

compare zips between 2 tables/insert

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.
0
COwebmaster
Asked:
COwebmaster
  • 14
  • 10
  • 3
  • +1
1 Solution
 
jefftwilleyCommented:
Does csvTable have a field already to accept the code?
0
 
COwebmasterAuthor 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.
0
 
jefftwilleyCommented:
Ok if this is in VBA? Easier to loop through recordsets that way. You'll need a form and a button.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
rockiroadsCommented:
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
0
 
COwebmasterAuthor Commented:
Yes, it can be in vba.  What will be in the form?
0
 
COwebmasterAuthor Commented:
rockiroads, ok so how can I implement that?  Also, did you and others understand the whole roundrobin thing?
0
 
COwebmasterAuthor 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.
0
 
rockiroadsCommented:
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???)
0
 
jefftwilleyCommented:
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
0
 
COwebmasterAuthor Commented:
Jeff, do I add this logic to the button on the form?
0
 
jefftwilleyCommented:
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
0
 
jefftwilleyCommented:
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
0
 
COwebmasterAuthor Commented:
Ok, let me try that now..
0
 
COwebmasterAuthor 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
0
 
jefftwilleyCommented:
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
0
 
COwebmasterAuthor 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




0
 
jefftwilleyCommented:
is your ZIP field text?
0
 
jefftwilleyCommented:

if it is, change to this
strCSV = "Select * from csvTable where Zip = '" & sZip & "';"  <---notice the tics '
0
 
COwebmasterAuthor Commented:
Yes, datatype is Text for all fields.  The reason being is because Canadian zips are in there too.
0
 
COwebmasterAuthor 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
0
 
jefftwilleyCommented:
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
0
 
rockiroadsCommented:
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.
0
 
COwebmasterAuthor 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
0
 
COwebmasterAuthor Commented:
Wait..it worked!!!
0
 
COwebmasterAuthor 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.
0
 
jefftwilleyCommented:
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
0
 
COwebmasterAuthor Commented:
Hi, any more on what I'm proposing?  Let me ask as a seperate question I guess.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 14
  • 10
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now