Query - Same Criteria, Multiple Columns

[forrest33]

Here's my dilemma:

Company Code1 Code2 Code3 Code4 Code5 Code6

How do i search for, let's say code 417230 through all the 6 code columns? Or do I need to reconfigure my table?

 

[PHV]

Or do I need to reconfigure my table
Have a look here:

http://r937.com/relational.html

Anyway, you may consider a normalization query, say qryCodeNorm:
SELECT Company, 'Code1' AS CodeCol, Code1 AS Code
FROM yourTable WHERE Code1 Is Not Null
UNION ALL SELECT Company, 'Code2', Code2
FROM yourTable WHERE Code2 Is Not Null
...
UNION ALL SELECT Company, 'Code6', Code6
FROM yourTable WHERE Code6 Is Not Null

And now your search query:
SELECT * FROM qryCodeNorm WHERE Code = 417230

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886