Solved

compare zips between 2 tables/insert

Posted on 2006-10-26
28
285 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
  • 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 500 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 Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

708 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

16 Experts available now in Live!

Get 1:1 Help Now