How to extract records that aren't distinguised in a field

[starbest]

I have inherited a database that was not set up as being relationally friendly. I am trying to report on traffic conditions in comparison with weather conditions. There are four categories of weather distinguished by their own separate fields. The name of the fields are: Sunny, Rainey, Foggy, Stormy. There is one field (conditions), however, that indicates traffic conditions: light, moderate, heavy.

The query looks like this:

CONDITIONS SUNNY RAINEY FOGGY STORMY
Light 0 0 1 0
Light 0 0 0 1
Heavy 1 0 0 0
Moderate 0 1 0 1

The 1(s) indicate "yes" and the 0(s)indicate "no".
I have been asked to report on all conditions that are other than SUNNY, RAINEY, FOGGY, STORMY. How can I extract that data to place it in a abstract category called "Other"? The report would look like the following.

CONDITIONS SUNNY RAINEY FOGGY STORMY Other
Light 0 0 1 0
Light 0 0 0 1
Heavy 1 0 0 0
Moderate 0 1 0 1

 

[SkipVought]

Hmmmmmmm........

What's the business case for this assignment?

What is the Criteia for "Other"

This table seems so general. For instance a relevant piece of data might be a date of observation, and another might be a location.

Wht kind of entity is this report being prepared for?





Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[starbest]

Thanks Skip . . .

. . . for your reply and inquiries. These four weather condition categories would really have been easier to work with had only one field been created called: "Weather Conditions", with a combo box added to the user entry form with selections for: Sunny, Rainey, Foggy, Stormy.

I have, since this posting, created a field in order to replicate a one field "Weather Conditions" by creating a field in the report's underlying query called Weather Conditions and I have developed it using he following conditional if statement.

IIf([Sunny]<>0,yes,2)+ IIF([Rainey]<>0,yes,3))

I am attemping to assign values to this field that would represent each kind of weather condition. The only problem now is to figure out the syntax to string these IIf statements along without causing them to add numbers together.

In other words:
How do string along these multiple conditions without using the "+" sign? This method is causing the numbers to add themselves together rather than remain static or the same as assigned.

 

[SkipVought]

String concatenation operator is &

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SkipVought]

String concatenation operator is &

I'd also use UNION queries to stack the data into a Normalized structure.

Select Condition, 'Sunny' as [WeatherCond], Sunny
From Table
UNION ALL
Select Condition, 'Rainey', Rainey
From Table
UNION ALL
....

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]