To whom may like to take a stab at it…

I have a query that looks at a table that I created and an ODBC table. The 
table I create will have the text (this is text, not number) strings as 
follows

TRN ‘ this is the field header
7465 
7589
4587
5248
1234
5879
9845
5687

This table I created should run against the ODBC Comment field which may 
have the following occurrences

COMMENT ‘this is the field header
TRN 4587
TRN 1234, 9845, 5687
TRN7589, 7589
TRN 7465
5248

I am the one who will have to run this query, and I would like to avoid 
having to use the Like “*” & [ ] & “*” because that would make this process 
too manual. Please let me know if I have any luck.

Thanks you very much for your help.

On Wed, 20 Feb 2008 10:15:02 -0800, code_hungry
<codehungry@discussions.microsoft.com> wrote:

>To whom may like to take a stab at it�
>
>I have a query that looks at a table that I created and an ODBC table. The 
>table I create will have the text (this is text, not number) strings as 
>follows
>
>TRN � this is the field header
>7465 
>7589
>4587
>5248
>1234
>5879
>9845
>5687
>
>This table I created should run against the ODBC Comment field which may 
>have the following occurrences
>
>COMMENT �this is the field header
>TRN 4587
>TRN 1234, 9845, 5687
>TRN7589, 7589
>TRN 7465
>5248
>
>I am the one who will have to run this query, and I would like to avoid 
>having to use the Like �*� & [ ] & �*� because that would make this process 
>too manual. Please let me know if I have any luck.
>
>Thanks you very much for your help.

SELECT [firsttable].TRN, [secondtable].* 
FROM [firsttable] INNER JOIN [secondtable]
ON [secondtable].[comment] LIKE "*" & [firsttable].[TRN] & "*"

There's no manual intervention required here; this will display each TRN from
the first table together with all fields from the second table where that TRN
appears anywhere within COMMENT.


You have my permission and encouragement to tar and feather whoever designed
this COMMENT field in this way.
-- 
             John W. Vinson [MVP]

Create your select query and put both tables in the design view space for 
tables.  In the criteria grid row for Comment use this  -- Like "*" & [TRN] & 
"*"

-- 
KARL DEWEY
Build a little - Test a little


"code_hungry" wrote:

> To whom may like to take a stab at it…
> 
> I have a query that looks at a table that I created and an ODBC table. The 
> table I create will have the text (this is text, not number) strings as 
> follows
> 
> TRN ‘ this is the field header
> 7465 
> 7589
> 4587
> 5248
> 1234
> 5879
> 9845
> 5687
> 
> This table I created should run against the ODBC Comment field which may 
> have the following occurrences
> 
> COMMENT ‘this is the field header
> TRN 4587
> TRN 1234, 9845, 5687
> TRN7589, 7589
> TRN 7465
> 5248
> 
> I am the one who will have to run this query, and I would like to avoid 
> having to use the Like “*” & [ ] & “*” because that would make this process 
> too manual. Please let me know if I have any luck.
> 
> Thanks you very much for your help.
> 

John, 

Thanks for your answer. I had to twick the SQL statement a bit but in the 
end it worked out. Major step for my work thanks a LOT.

here is the actual statement that worked.

SELECT TRN.[TRN Number], ARN_InvHistoryHeader.SOComment
FROM TRN INNER JOIN ARN_InvHistoryHeader ON  ARN_InvHistoryHeader.SOComment 
LIKE "*" & TRN.[TRN Number] &"*"
-- 
I feel the need....the need for code!


"John W. Vinson" wrote:

> On Wed, 20 Feb 2008 10:15:02 -0800, code_hungry
> <codehungry@discussions.microsoft.com> wrote:
> 
> >To whom may like to take a stab at it…
> >
> >I have a query that looks at a table that I created and an ODBC table. The 
> >table I create will have the text (this is text, not number) strings as 
> >follows
> >
> >TRN ‘ this is the field header
> >7465 
> >7589
> >4587
> >5248
> >1234
> >5879
> >9845
> >5687
> >
> >This table I created should run against the ODBC Comment field which may 
> >have the following occurrences
> >
> >COMMENT ‘this is the field header
> >TRN 4587
> >TRN 1234, 9845, 5687
> >TRN7589, 7589
> >TRN 7465
> >5248
> >
> >I am the one who will have to run this query, and I would like to avoid 
> >having to use the Like “*” & [ ] & “*” because that would make this process 
> >too manual. Please let me know if I have any luck.
> >
> >Thanks you very much for your help.
> 
> SELECT [firsttable].TRN, [secondtable].* 
> FROM [firsttable] INNER JOIN [secondtable]
> ON [secondtable].[comment] LIKE "*" & [firsttable].[TRN] & "*"
> 
> There's no manual intervention required here; this will display each TRN from
> the first table together with all fields from the second table where that TRN
> appears anywhere within COMMENT.
> 
> 
> You have my permission and encouragement to tar and feather whoever designed
> this COMMENT field in this way.
> -- 
>              John W. Vinson [MVP]
>