By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,324 Members | 1,918 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,324 IT Pros & Developers. It's quick & easy.

String Intersection

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
I don't know if I undersand your definition of Intersection?

if by Intersection you mean...
That Column2 (match word)
T 1000 1
h 0100 1
i 0000 0
s 0000 0
1100
^
|- Column1 (known word)

.... what would column three contain "Th" for matched characters or
"is" for
un-matched characters? Now that I re-read this thing, I must be
barking up the wrong tree, cause I don't see the value in returning
either matched or un-match characters (although because I don't see
the value, doesn't mean there isn't any).

It seems more info is needed.

sc****@cable.net.co (Sebastian Cohen S) wrote in message news:<da**************************@posting.google. com>...
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

Nov 13 '05 #2

P: n/a
Hello Tom,

Thank you for your patience and reply. I mean by intersection of the
two columns by:

column 1 text: this
column 2 text: this(1)
intersection leads to the text: this

My final objective is to delete extra characters of very similar data
that is in two columns.

Have a great day,

Allow me to Thank You again,

Sebastian Cohen S

tw*@gate.net (Tom Warren) wrote in message news:<f4**************************@posting.google. com>...
I don't know if I undersand your definition of Intersection?

if by Intersection you mean...
That Column2 (match word)
T 1000 1
h 0100 1
i 0000 0
s 0000 0
1100
^
|- Column1 (known word)

... what would column three contain "Th" for matched characters or
"is" for
un-matched characters? Now that I re-read this thing, I must be
barking up the wrong tree, cause I don't see the value in returning
either matched or un-match characters (although because I don't see
the value, doesn't mean there isn't any).

It seems more info is needed.

sc****@cable.net.co (Sebastian Cohen S) wrote in message news:<da**************************@posting.google. com>...
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

Nov 13 '05 #3

P: n/a
My question in terms relative to this example was, how do we know that
"this(1)" in column 2 contains characters that need to be deleted, and
NOT that "this" in column 1 is missing characters? Is column 1 a known
value? Are all words in column 2 truncated to the shortest possible
word in column 1? How are these two columns joined, a common column
not defined in the example?

Sorry to answer your Qs with more Qs but...
Tom

sc****@cable.net.co (Sebastian Cohen S) wrote in message news:<da**************************@posting.google. com>...
Hello Tom,

Thank you for your patience and reply. I mean by intersection of the
two columns by:

column 1 text: this
column 2 text: this(1)
intersection leads to the text: this

My final objective is to delete extra characters of very similar data
that is in two columns.

Have a great day,

Allow me to Thank You again,

Sebastian Cohen S

tw*@gate.net (Tom Warren) wrote in message news:<f4**************************@posting.google. com>...
I don't know if I undersand your definition of Intersection?

if by Intersection you mean...
That Column2 (match word)
T 1000 1
h 0100 1
i 0000 0
s 0000 0
1100
^
|- Column1 (known word)

... what would column three contain "Th" for matched characters or
"is" for
un-matched characters? Now that I re-read this thing, I must be
barking up the wrong tree, cause I don't see the value in returning
either matched or un-match characters (although because I don't see
the value, doesn't mean there isn't any).

It seems more info is needed.

sc****@cable.net.co (Sebastian Cohen S) wrote in message news:<da**************************@posting.google. com>...
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

Nov 13 '05 #4

P: n/a
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.


Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.