|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Be the architects of evolution and help create the mobile internet future. It’s your move---enter to win here! |
|
#1
April 16th, 2008, 11:57 AM
|
|||
|
|||
|
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
April 16th, 2008, 01:40 PM
|
||||
|
||||
|
That's what SQL is for
Code:
SELECT Feb2006.CustomerNo FROM Feb2006 INNER JOIN Jan2006 ON Feb2006.CustomerNo = Jan2006.CustomerNo; Voila the result of this query is those records with matching CustomerNo in both tables ...
__________________
medialint.com "Energy has the opportunity to change the climate if it's done right." - Sen. John Ensign, R-Nev. (quoted out of context)
|
|
#4
April 17th, 2008, 11:59 AM
|
||||
|
||||
|
Load up access and try using the query builder. Then switch back and forth from design view to SQL view. It doesn't always generate the cleanest SQL but it will help you get a grasp on basic query semantics. For building queries like this it's pretty straightforward just tie the tables together on the common field and set the join type to include only those in both tables (note the different join types and how they become INNER/OUTER/LEFT/RIGHT joins in the SQL as you play around too) ...
|
|
#5
April 17th, 2008, 06:16 PM
|
||||
|
||||
|
If you want to study SQL go to www.w3schools.com and click on the SQL section. It's a good place to start.
|
|
#6
April 18th, 2008, 05:05 AM
|
|||
|
|||
|
Quote:
Thank you for the suggesstion, I will lookup the query wizard in Microsoft Access, that will definitely help and improve my understanding of sql query select.
|
|
| Viewing: Dev Shed Forums > Programming Languages - More > Visual Basic Programming > Finding common elements (values) in two tables in MS access db using VB 6.0 |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Works perfect and very fast indeed for my recordset. 
Linear Mode
