Searching column b for data/string from column a

[Paladine]

Hoping someone can help me. I would like to do this in a query rather than VBA code.

I have a column (Column A) with string/text values what are lab test abbreviations, and I want to search another column (Column B) for instances of column A data. The data in column b is basically varying lengths of column A seperated by comma's.

Example:
Column A Column B
ABC PQR, GHI
DEF GHI, DEF
GHI
JKL
MNO
PQR GHI, JKL

So I would like the query to return those rows with any data matching column A. As not every row in column be will have data. So in the above example it would pull out ROW ABC, DEF, and PQR.

Hope this makes sense, and thanks in advance.

_________________
Paladine

 

[lespaul]

Maybe this is just an error, but your example doesn't match what you said you wanted.

So I would like the query to return those rows with any data matching column A.

except you then say that you want these results:
ABC
DEF
PQR

But PQR is used in ColumnB.

There are several threads that address splitting up data that is combined. You should be able to use one of those functions to check your data. I'll post back with a link when I find it.

For future reference, when you store multiple pieces of information in a single field, you are breaking normalization rules. If you had a separate table as a joiner table between your many to many relationship, this would be a very simple query.



Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell

 

[Paladine]

Thanks lespaul, any help is appreciated.

I know that it breaks normalization. The reason it does is because the date is an import from an excel spreadsheet. I need the query inorder to sort the data based on criteria we specify.

And by So I would like the query to return those rows with any data matching column A. I mean, I only want to pull rows that contain data which exists both in A and B columns. But the more I look at this...the more the logic breaks down. hehe. I think I will split the data in column b up into logical groups and process those against column A. There are other criteria with makes this important to do, but the principle is still the same.

Thanks for the help! And if you find that link(s) I would appreciate a post on it.



_________________
Paladine

 

[r937]

But PQR is used in ColumnB."

yes, but not on the same row

select ColumnA
     , ColumnB
  from daTable
 where ColumnB like '*' & ColumnA & '*'

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)