dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Finding common elements (values) in two tables in MS access db using VB 6.0

  1. #1
    Join Date
    Apr 2008
    Posts
    13

    Finding common elements (values) in two tables in MS access db using VB 6.0

    Hello!
    I am new to VB 6.0. Currently I am doing some data pre-processing for a project, and not being good in VB I had difficulty in something.

    Suppose I have two tables in MS access:
    1) Jan2006
    2) Feb2006

    And these two tables have equal number columns. My focus is only to select a column named "CustomerNo" in both Tables in my MS access database (I converted from excel files using VB 6.0).

    I am trying to find the common elements in both the CustomerNo columns in Jan2006 and Feb2006, using VB 6.0 (rs.find) currently, however I am unable to do so. After finding the common elements I wish to extract them into a new table and then go ahead with my other data files.

    Exceptions:
    (1) There are duplicate values in the CustomerNo column, which need to be skipped, when the common values are extracted
    (2) I have 50,000+ rows of data, resulting in 600MB db file for pre-processing in this manner, needed a fast and optimizable way
    (3) The number of rows are not same in any of the "CustomerNo" columns. However the data is numerical value like:

    TABLE: JAn2006
    CustomerNo
    00200002
    00200003
    00200004
    00200005
    00200006
    00200006
    00200007
    00200008
    00200010
    00200011
    00200012
    00200015
    00200016
    00200020

    TABLE: Feb2006
    CustomerNo
    00200002
    00200003
    00200004
    00200005
    00200007
    00200008
    00200010
    00200011
    00200012
    00200015
    00200016
    00200021
    00200022

    Here is the code I was working on, currently having a problem with the pointers using rs.find:

    Code:
    Dim cn            As New ADODB.Connection
    Dim cnstr         As String
    Dim sql           As String
    Dim rs1           As New ADODB.Recordset
    Dim rs2           As New ADODB.Recordset
    Dim rs3           As New ADODB.Recordset
    Dim strFilePath   As String
    Dim strloop       As String
    
    strFilePath = App.Path & "\data.mdb"
    cnstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFilePath & ";"
    
    cn.Open cnstr
    sql1 = "SELECT CustomerNo FROM Jan2006"
    sql2 = "SELECT CustomerNo FROM Feb2006"
    
    Set rs1 = cn.Execute(sql1)
    Set rs2 = cn.Execute(sql2)
    
    
    
    Do Until rs2.EOF
       rs1.MoveFirst
       strloop = rs1.Fields("CustomerNo").Value
       MsgBox rs1.Fields("CustomerNo").Value
       rs2.Find ("CustomerNo='strloop'"), , adSearchForward, 1
       
    If (rs2.EOF = True) Then
    Debug.Print rs1.Fields("CustomerNo")
    End If
    
    rs2.MoveNext
    Loop
    
    rs1.Close
    rs2.Close
    
    End Sub

  2. #2
    Join Date
    Apr 2008
    Posts
    28
    hi,

    I'm not sure if you want to extract Distinct CustomerNo from both tables..
    (1) There are duplicate values in the CustomerNo column, which need to be skipped, when the common values are extracted

    Or you want to extract the common elements of the two...
    I am trying to find the common elements in both the CustomerNo columns in Jan2006 and Feb2006

    So I created 2 SQL queries to help you.


    This will get all the distinct CustomerNo available from the 2 tables:

    "SELECT Distinct CustomerNo
    FROM (SELECT CustomerNo FROM Jan2006 UNION ALL SELECT CustomerNo FROM Feb2006)"


    And this query will get all the distinct common elements of the two tables:

    "SELECT Distinct Jan2006.CustomerNo
    FROM Jan2006 INNER JOIN Feb2006 ON Jan2006.CustomerNo = Feb2006.CustomerNo"


  3. #3
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    You have many problems here, not only is your code not correct but you are not very clear on what you are trying to achieve.
    According to your example data you can have duplicate customers in both tables. In Jan2006 you have two 00200006 entries but none in Feb2006. So is this customer considered a duplicate since it is in Jan2006 more that once? Is it only considered a duplicate when the customer exsists in both tables? When you say extract them into another table, do you mean all copies from each table is to be copied into another table and then removed from the Jan2006 and Feb2006 tables. Or just copy one record to the new table, then delete the others?
    As far as you code goes, it is wrong in many ways. You are looping throught the same recordset (rs2) as you are doing the find on. Yet getting the value from the first recordset (rs1) but never changing the record that it is on. Also your find is incorrect because you are searching for customerNo strloop when you realy want the customerNo stored in the strloop variable.
    Here are some fixes to your code to make it function; but you shold also know that this is the worst and slowest possible way of doing this.
    Code:
       
    Dim bkMrk as Variant  '<-Start parameter for the find needs to be a bookmark
    'Move to the first record before the loop starts
    rs1.MoveFirst
    rs2.MoveFirst
    'Get The Bookmark For the first Record in recordset 2
    bkMrk = rs2.Bookmark
    Do Until rs1.EOF '<-loop until the end of rs1 not rs2
       strloop = rs1.Fields("CustomerNo").Value
       MsgBox rs1.Fields("CustomerNo").Value
       'append the contents of strloop to your search string 
       rs2.Find ("CustomerNo='" & strloop & "'"), , adSearchForward, bkMrk  '<-always start from the first rs2 records bookmark
     
       If (rs2.EOF = True) Then
          Debug.Print rs1.Fields("CustomerNo")  '<- notice this only shows customers not found in rs2
       End If
    
       rs1.MoveNext  '<- change to the next rs1 record not the rs2 record
    Loop

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Try
    Code:
    sSQL = "SELECT Jan2006.CustomerNo " 
    sSQL = sSQL & "FROM Jan2006 " 
    sSQL = sSQL & "INNER JOIN Feb2006 ON Jan2006.CustomerNo = Feb2006.CustomerNo "
    sSQL = sSQL & "GROUP BY Jan2006.CustomerNo "

  5. #5
    Join Date
    Apr 2008
    Posts
    13
    Thank you for the response. You all seem like SQL and VB gurus to me. With your help I have successfully filtered out the things I didn't need. However just FYI this was done for 24 CustomerNo columns, using a looping system.

    Thanks once again for your kind help. I guess using SQL query is far much better rather than using an array method by looping and getting stuck with pointers.

  6. #6
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    If you are dealing with databases, always use SQL for data manipulation. It is what the language was designed for.

    There are many fine SQL tutorials out on the web.

Similar Threads

  1. Replies: 60
    Last Post: 09-13-2002, 05:41 PM
  2. vb and access
    By zinith in forum VB Classic
    Replies: 1
    Last Post: 05-02-2002, 10:44 AM
  3. Replies: 2
    Last Post: 11-29-2001, 11:38 AM
  4. Open Access database Form from VB 6.0?
    By Demo in forum VB Classic
    Replies: 7
    Last Post: 07-25-2000, 08:10 AM
  5. Multi-row calculations
    By Bob Hines in forum Database
    Replies: 7
    Last Post: 04-27-2000, 11:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center