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

[awyeah]

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:

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

[rollete]

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"

[Ron Weller]

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.

   
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

[Hack]

Try

sSQL = "SELECT Jan2006.CustomerNo " 
sSQL = sSQL & "FROM Jan2006 " 
sSQL = sSQL & "INNER JOIN Feb2006 ON Jan2006.CustomerNo = Feb2006.CustomerNo "
sSQL = sSQL & "GROUP BY Jan2006.CustomerNo "

[awyeah]

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.

[Hack]

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.