?
Solved

compare zips between 2 tables/insert

Posted on 2006-10-26
28
Medium Priority
?
338 Views
Last Modified: 2008-03-04
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
Comment
Question by:COwebmaster
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 10
  • 3
  • +1
28 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17813310
Does csvTable have a field already to accept the code?
0
 

Author Comment

by:COwebmaster
ID: 17813356
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17813388
Ok if this is in VBA? Easier to loop through recordsets that way. You'll need a form and a button.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 65

Expert Comment

by:rockiroads
ID: 17813419
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
 

Author Comment

by:COwebmaster
ID: 17813421
Yes, it can be in vba.  What will be in the form?
0
 

Author Comment

by:COwebmaster
ID: 17813446
rockiroads, ok so how can I implement that?  Also, did you and others understand the whole roundrobin thing?
0
 

Author Comment

by:COwebmaster
ID: 17813484
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 17813492
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17813522
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
 

Author Comment

by:COwebmaster
ID: 17813559
Jeff, do I add this logic to the button on the form?
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17813669
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17813688
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
 

Author Comment

by:COwebmaster
ID: 17813749
Ok, let me try that now..
0
 

Author Comment

by:COwebmaster
ID: 17814016
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17814173
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
 

Author Comment

by:COwebmaster
ID: 17814250
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17814359
is your ZIP field text?
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17814369

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

Author Comment

by:COwebmaster
ID: 17814540
Yes, datatype is Text for all fields.  The reason being is because Canadian zips are in there too.
0
 

Author Comment

by:COwebmaster
ID: 17814571
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
 
LVL 34

Accepted Solution

by:
jefftwilley earned 2000 total points
ID: 17814642
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 17814719
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
 

Author Comment

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

Author Comment

by:COwebmaster
ID: 17814802
Wait..it worked!!!
0
 

Author Comment

by:COwebmaster
ID: 17814939
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17815007
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
 

Author Comment

by:COwebmaster
ID: 17837427
Hi, any more on what I'm proposing?  Let me ask as a seperate question I guess.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17837943
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…
Suggested Courses

765 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