Select Portion of a Text String

[Qaspec]

I have a table [tableA] with a memo field [memo1] and I have another table
that contains different values [tableV].[Variables].
I need to search the string in the memo1 field and select those records
where any portion of the string equals a value in my tableV.variables field.

For example if :

tableA.memo1 = yes green A1T cust states 14
tableA memo1 = yes blue BY5 cust down 23
table A memo1 = no red CV67 pat states 89

tableV.Variables = A1T
tableV.Variables = CV67

I need my query to return the first and 3rd record from tableA because a
portio of the text matched a value in another table. Please help.

 

[Stefan Hoffmann]

hi,

For example if :
tableA.memo1 = yes green A1T cust states 14
tableA memo1 = yes blue BY5 cust down 23
table A memo1 = no red CV67 pat states 89

tableV.Variables = A1T
tableV.Variables = CV67

I need my query to return the first and 3rd record from tableA because a
portio of the text matched a value in another table. Please help.

This should work:

SELECT A.[memo1]
FROM [tableA] A, [tableV] V
WHERE A.[memo1] LIKE "*" & V.[Variables] & "*"
GROUP BY A.[memo1]

Caveat: It uses a cross join. So you shouldn't use it on large tables.


mfG
--> stefan <--

 

[Marshall Barton]

I have a table [tableA] with a memo field [memo1] and I have another table
that contains different values [tableV].[Variables].
I need to search the string in the memo1 field and select those records
where any portion of the string equals a value in my tableV.variables field.

For example if :

tableA.memo1 = yes green A1T cust states 14
tableA memo1 = yes blue BY5 cust down 23
table A memo1 = no red CV67 pat states 89

tableV.Variables = A1T
tableV.Variables = CV67

I need my query to return the first and 3rd record from tableA because a
portio of the text matched a value in another table. Please help.

You could use something like:

SELECT tbleSvcTickLocal.ServiceTicketID,
tbleSvcTickLocal.Summary AS Comments,
tbleSvcTickLocal.CreateDt
FROM tbleSvcTickLocal INNER JOIN TableV
ON tbleSvcTickLocal.Summary Like "*" & TableV.Variables
& "*"

Note that you can not do this kind of thing in the query
designer, use SQL view instead.

Warning: don't use a Totals (GROUP BY) type query unless you
need to do aggregate calculations using Count, Sum,etc.

 

[John Spencer]

Alternative would be to use a Non-equi join. You can test both options and
see if one is faster than the other.

SELECT Memo1
FROM TableA INNER JOIN TableV
ON TableA.Memo1 LIKE "*" & TableV.Variables & "*"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

hi,

For example if :
tableA.memo1 = yes green A1T cust states 14
tableA memo1 = yes blue BY5 cust down 23
table A memo1 = no red CV67 pat states 89

tableV.Variables = A1T
tableV.Variables = CV67

I need my query to return the first and 3rd record from tableA because
a portio of the text matched a value in another table. Please help.

This should work:

SELECT A.[memo1]
FROM [tableA] A, [tableV] V
WHERE A.[memo1] LIKE "*" & V.[Variables] & "*"
GROUP BY A.[memo1]

Caveat: It uses a cross join. So you shouldn't use it on large tables.


mfG
--> stefan <--

 

[KARL DEWEY]

Try this --
SELECT [tableA].[memo1]
FROM [tableA], [tableV]
WHERE [tableA].[memo1] Like "*" & [tableV].[Variables] & "*";