Query on parts of field- Inconsistent format

  • Thread starter Thread starter code_hungry
  • Start date Start date
C

code_hungry

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.
 
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.
Click to expand...

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.
 
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] &
"*"
 
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 said:
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.
Click to expand...

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.
Click to expand...
 
Back
Top