Sebastian Cohen S wrote:
Hello,
I am new to SQL and currently using Access 2003 and need a little help
on the following.
I have two queries each contains one column formed with a string that
is very similar (only variying by few characters) to the other column
as follows:
QUERY1 QUERY2
Column1 Column2
The new Query should make a String Intersection: Column1 with Column2
on to a new column.
Help is deeply appreciated.
Have a great day,
Sebastian Cohen S
After reading your thread between you and Tom, this is a concept only.
And MAY work only if Query2 has an autoid or some sort of primary key.
You would need to create a couple of more queries. And write a function.
Create a new query with QUery1. Create an expression that passes the
column to compare to a function. Ex:
Query2Keys : CheckQuery2([Column1])
This would pass the value of column1 to the function.
'function aircode
Function CheckQuery2(strVal As String) As Variant
Dim strSQL As STring
Dim rst As REcordset
strSQL = "Select Q2.ID From Q2 " & _
"WHere Q2.Column2 Like '" & strVal & "*'"
Set rst = currentdb.openrecordset(strSQL,dbopensnapshot)
Do while not rst.eof
CheckQuery2 = CheckQuery2 & _
"'" & rst!ID & "',"
'option 2 is to insert a new record here on the id
'and use this to link to Q2 later
rst.movenext
Loop
'remove ending comma
If CheckQuery2 > "" THen
CheckQuery2 = Left(CheckQuery2,Len(CheckQuery2)-1)
ENd Function
Now you have the list of IDs from Q2 that have a similar string
contained in Q1
Now create another query on Q2 where the id is contained in the the
above query.
I'm not sure how you'll merge the two queries together. You may get a
cartesian join. Option 2. I suppose you could create another table and
instead of passing back the string, create a record in the table for
each q2 id that matches. It would contain the value passed to the
function (Q1 value), and the Q2 ID. Then link Q2 to this new table and
you can display the value from Q1. Or pass to the function also Q1's ID
and store that ID as well as Q2's id. THis way you have a bridge
between both queries. A bit confusing but you should be able to get the
gist of this message.