I think I may have reached a limitation with my database design. I am wondering whether I can do one of two things:
1. Set up a query to find a condition within a record, then return the field name where the condition is met, or
2. Transpose field names into a single field
I realize this is the opposite of how a query normally works (find a record that meets the condition within a field), so I'm not sure if this is possible.
I have a table that is currently set up like this:
PART_NUMBER JAN14 FEB14 MAR14 APR14 MAY14 1234567890 900 800 200 -100 200 1234567891 800 -500 -400 -200 100
I would like to find when each part number meets the condition of being less than zero.
So output of the query would look like:
PART_NUMBER NEGATIVE_MONTH 1234567890 APR14 1234567891 FEB14 1234567891 MAR14 1234567891 APR14
I could possibly transpose the field names to a single field itself, like below, but it will require a lot of work, and violate the primary key (PART_NUMBER):
PART_NUMBER VALUE MONTH 1234567890 900 JAN14 1234567890 800 FEB14 1234567890 200 MAR14 1234567890 -100 APR14 1234567890 200 MAY14 1234567891 800 JAN14 1234567891 -500 FEB14 1234567891 -400 MAR14 1234567891 -200 APR14 1234567891 100 MAY14
Any ideas on how to accomplish this? Or am I stuck until I re-design my table?