I have two distinct tables, from these tables I want to compare two fields containing text values (keywords) whose data type is memo.
they look like this:
but, being a noobie to access programming, I don't know much about SQL programming...
I prefer for the moment learnig how to use VBA to define small codes, and use these in a query or a macro... That's what I did for all the prevous work I have done on this project.
So my questions is :
How could I compare my two tables for occurances of a same keyword in both tables? thx for your answer.
My goal is to obtain a resulting table containing fields for each matching keywords and its RecIds in the two tables where it is found...
this bit of code will give you a string will all the id's of matching values. thel ist will be delimited with ;. I thought this would make it easier for importing into a table or something.
I haven't tested it so I hope it helps.
Dim Db As Database
Dim Rs1 As Recordset
Dim Rs2 As Recordset
Dim StrIds As String
Set Db = CurrentDb()
Set Rs1 = Db.opensrecordset("table1")
While Not Rs1.EOF
Set Rs2 = Db.OpenRecordset("select * from [table2] where [value] = " & Rs1![Value])
If Rs2.EOF And Rs2.BOF Then
'there are no records for this value
'there are matching records
While Not Rs2.EOF
StrIds = StrIds & ";" & Rs2![Value]
hey thank you for the job! it's cool!
but the final step of my task will be to automate fully the data work. Concerning your example, I have to click "GO" to start the matching work.... won't that be a problem if I want to use succeding macros to process my task? I'll try to use part of the code associated with the button "GO" and use a sub macro on it to process the matching automatically.
One more thing, given that my tbl1 has 20000 entries or keywords and that tbl2 will be a lot smaller (less than a couple hundred for the moment), which table in your code is used to give the keyword which is searched for in the other table. Because, logically the smaller table should give the words, should'nt it?
thx for your time
found the solution to my preceding question I'll use tbl1 as the samllest table.
So I transformed the code to this in order to use it with a macro:
Option Compare Database
Dim dbCurrent As Database
Dim rstTableOne As Recordset
Dim rstTableTwo As Recordset
Dim rstTableThree As Recordset
Dim CompResult As Variant
Set dbCurrent = CurrentDb()
'set recordset to TableOne
' NOTE: the text between the " " here can be any SQL statement
Set rstTableOne = dbCurrent.OpenRecordset("tblClientKeyword")
'set recordset to TableTwo
Set rstTableTwo = dbCurrent.OpenRecordset("tblNewKeyword")
'set recordset to TableThree
Set rstTableThree = dbCurrent.OpenRecordset("tblresults")
'goto first record in TableOne
If Not (rstTableOne.EOF) Then
MsgBox ("Table One is Empty!")
'quick fix, error check incase TableTwo is empty
' If this is not here and TableTwo has no data
' then this program crash
If rstTableTwo.EOF Then
MsgBox ("Table Two is Empty!")
'loop through each record till end of records in TableOne
Do Until (rstTableOne.EOF)
'loop through each record till end of records in TableTwo
Do Until rstTableTwo.EOF
'compare records in TableOne and TableTwo
CompResult = StrComp(rstTableOne!Value, rstTableTwo!Value, vbTextCompare)
'note if string is the same then 0 is returned,
If CompResult = 0 Then
'MsgBox ("String is the same")
'copy data to TableThree
!Value = rstTableOne!Value
!RecId_TableOne = rstTableOne!RecId
!RecId_TableTwo = rstTableTwo!RecId
'MsgBox ("String not the same")
'MsgBox ("Table Two Id: " & rstTableTwo!RecId)
'move to next record in recordset
'MsgBox ("Table One Id: " & rstTableOne!RecId)
'goto first record in TableTwo if End Of File
If (rstTableTwo.EOF) Then
'move to next record in TableOne
'set to nothing
Set rstTableTwo = Nothing
Set rstTableThree = Nothing
Set rstTableThree = Nothing
And I get this: "error code 3265 execution error"
on this line
thanks for your help!! it works fine!!!
izyrider I see that you have a good perception on my VBA programming skills... I had'nt understood that the field value had to be changed to the real field names of each table... It's all on me... Sorry ventelation to have made you think on that...
Now that I can match each of my keywords, I'd like to know if there's a way to define the case sensitiveness or not in the code?
Because, for example, if I am searching through table 2 for the key word "accessory", the only matching found is for the same word... But there are fields in table 2 containing the keywords "Accessory reproductive glands".
I'd like to be able to have a match on this search, even though the field from table 2 contains only part of the keyword searched. For this problem I separated the compound keywords "Test of materials" contained in one field into three fields containing "test", "of", "materials". But the inconvenient of this technique is that I am going to have way more matches than before. Therefore, I'll have "to weighten" the matchings sort of like in a google search... Where for a specific multiple keywords search coming from a single entry, a percentage is returned as:
"from client number 12's 10 keywords, there are:
- 7 keywords matching with partnership offer number 68 (70%)
- 1 keywords matching with partnership offer number 68 (10%)
Therefore some matches made on common, regular, non relevant keywords as articles (of, an, a, the.... you know them... ) won't get as much attention as a true match with pertinent keywords...
My other problem is that I want a match even though keywords are written in a different manner: "Test" and "test" for example should return me a hit. Should I turn any capital letter into a small letter in my keywords field?
hey thx man!
tried it works fine for me thanks! I don't think I'll go higher in difficulty and to try to find misstyped words (e.g. pheontype instead of phenotype)!!! Otherwise it'll be hell!!!
appreciate your help on this one! c u around