Query for Same value in multiple fields

[razorking]

This is an odd one. I have always achieved this by somewhat less than graceful means. I suspect there is a better way to do this. I am wondering if it can be done in a single query versus creating multiple queries and then bringing together into the final product.

generically speaking: I have a table with three fields: policy1, policy2 and policy3. Any of the fields can have the same value for any one of the records on the table. Meaning: I might have two records like this:
Account#----policy1-----policy2-----policy3
25642---------null---------DI--------null-----(note: null is no value in field)
25643----------DI--------null--------null

What is the correct way to query this table to find all accounts that have policy DI in any of the three policy fields? If I query on parameter DI for field policy1 I won't see the 25643 account. What am I missing??

 

[honda882000]

You should be able to use OR in your WHERE clause. For example:

SELECT * FROM table WHERE policy1 = 'DI' OR policy2 = 'DI' OR policy3 = 'DI'

 

[boblarson]

I hope you don't take this the wrong way, but since you used the phrase "What is the correct way..." I am taking that as an opening.

First of all, your database is not NORMALIZED.

It should not have repeating fields (policy1, policy2, etc). Those should be RECORDS in a table and not columns. So, I would suggest reading up on normalization. IF your database was normalized it would be REALLY EASY to get the information out. But as it isn't then you have to do things with "less than graceful means" always.

Normalization

 

[razorking]

Thanks for the replies.

I have no problem with the admonition or constructive criticsm. Your point on normalization is well taken. The particular table I am working with is actually a link table from another system. So it's not my creation, and probably not how I would have done things. But it's there and I need to get get data from it. That being said, I know my own creations are probably not always optimized or in the category of "best practices". I am interested though in learning how to design and create correctly to avoid problems in the future - so I appreciate the comments on normalizing the db.

I will try Honda's method. That might be waht I need, but I usually create my queries in the QBE grid, so not sure how to do it there.