[skyshare]

I have an access table with 20 fields with a VB6 connection. Is there a way in sql to search all fields in the table for an item of text without referencing each field individually?


I am trying to avoid the following...

Select * from <tablename> where field1 ='text' or field2 = 'text' or field3='text'....etc


thanks

[modi123_1]

Yes, you'll need to name all the columns. It wouldn't make sense to search a text value across say the row id, right? I am curious what forty columns make up this table and under what scenerio a text seach would apply to all of them. Maybe a re-architecture is called for.

[skyshare]

The fields are text fields named 'PR1' to 'PR20' which hold a string of text less than 10 characters. Not all are populated but there are thousands of rows. The text I want to search for could be anywhere in the table.

So the current sql I use would be :
'select * from mytable where PR1 = 'mytext' or PR2 = 'mytext' or pr3 = 'mytext' ....etc

I just wondered if there was a way to search the whole table at once?

[modi123_1]

So there's no row index? No date entered? Nothing that *wouldn't* be searched?

[skyshare]

No, no other fields apart from the index

[modi123_1]

There's your odd case out.. a column that *doesn't* need to be searched.. so yes.. enumerate all your columns.. then that also helps future proof against forgetfulness. Say you add a new column but don't want to search it - boom.. enumerated columns prevent that mess up.

Side note - I still think having 40 similar columns is a silly idea and can be normalized out into one table with one column and 0 to n rows that are keyed back to a master table.. but then again with column names like "PR1" and "PR20" it's damn hard to know what this is holding.

[skyshare]

Thanks for the reply