I have 4 columns, each can have a value of either B, D or E. I want to
exclude all records with a "D" in any column, and all records where
all 4 columns have a B

SELECT CHF.BILLNUMBER, CHF.HF1, CHF.HF2, CHF.HF3, CHF.HF4
FROM CHF
WHERE (((CHF.HF1)<>"D") AND ((CHF.HF2)<>"D") AND ((CHF.HF3)<>"D") AND
((CHF.HF4)<>"D"));

That is what I have so far, and that gets rid of all the D's like I
need, I just cant figure out how to exclude records where they have B's
for all 4 columns.


Is it possible?

(E-Mail Removed) wrote:
> I have 4 columns, each can have a value of either B, D or E. I want to
> exclude all records with a "D" in any column, and all records where
> all 4 columns have a B
>
> SELECT CHF.BILLNUMBER, CHF.HF1, CHF.HF2, CHF.HF3, CHF.HF4
> FROM CHF
> WHERE (((CHF.HF1)<>"D") AND ((CHF.HF2)<>"D") AND ((CHF.HF3)<>"D") AND
> ((CHF.HF4)<>"D"));
>
> That is what I have so far, and that gets rid of all the D's like I
> need, I just cant figure out how to exclude records where they have B's
> for all 4 columns.
>
>
> Is it possible?
>

Add to your WHERE clause:
OR (CHF.HF1='B' AND CHF.HF2='B' AND ... etc. )

This response would not be complete without a comment about the table
design. It's not a good idea to store repeating groups of data in the
same table. It leads to headaches like the one you have. Google on
"database normalization" (with the quotes) for lots more on this topic.

HTH
--
Smartin

I think it is desired to exclude all B's:-

OR NOT (CHF.HF1='B' AND CHF.HF2='B' AND ... etc. )


"Smartin" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> (E-Mail Removed) wrote:
>> I have 4 columns, each can have a value of either B, D or E. I want to
>> exclude all records with a "D" in any column, and all records where
>> all 4 columns have a B
>>
>> SELECT CHF.BILLNUMBER, CHF.HF1, CHF.HF2, CHF.HF3, CHF.HF4
>> FROM CHF
>> WHERE (((CHF.HF1)<>"D") AND ((CHF.HF2)<>"D") AND ((CHF.HF3)<>"D") AND
>> ((CHF.HF4)<>"D"));
>>
>> That is what I have so far, and that gets rid of all the D's like I
>> need, I just cant figure out how to exclude records where they have B's
>> for all 4 columns.
>>
>>
>> Is it possible?
>>
>
> Add to your WHERE clause:
> OR (CHF.HF1='B' AND CHF.HF2='B' AND ... etc. )
>
> This response would not be complete without a comment about the table
> design. It's not a good idea to store repeating groups of data in the same
> table. It leads to headaches like the one you have. Google on "database
> normalization" (with the quotes) for lots more on this topic.
>
> HTH
> --
> Smartin

David Cox wrote:
> I think it is desired to exclude all B's:-
>
> OR NOT (CHF.HF1='B' AND CHF.HF2='B' AND ... etc. )
>
>

We're both wrong. Let's start over.

No D's anywhere:
CHF.HF1<>'D' AND CHF.HF2<>'D' AND CHF.HF3<>'D' AND CHF.HF4<>'D' {P1}

Not B's everywhere:
NOT (CHF.HF1='B' AND CHF.HF2='B' AND CHF.HF3='B' AND CHF.HF4='B') {P2}
alternatively,
CHF.HF1<>'B' OR CHF.HF2<>'B' OR CHF.HF3<>'B' OR CHF.HF4<>'B' {P2'}

So,
WHERE {P1} AND {P2}
Becomes
WHERE
(CHF.HF1<>'D' AND CHF.HF2<>'D' AND CHF.HF3<>'D' AND CHF.HF4<>'D')
AND NOT (CHF.HF1='B' AND CHF.HF2='B' AND CHF.HF3='B' AND CHF.HF4='B')

alternatively,
WHERE
(CHF.HF1<>'D' AND CHF.HF2<>'D' AND CHF.HF3<>'D' AND CHF.HF4<>'D')
AND (CHF.HF1<>'B' OR CHF.HF2<>'B' OR CHF.HF3<>'B' OR CHF.HF4<>'B')

--
Smartin