Query:When 1 field in a table is not = to another field (same tab

[Guest]

Does anybody know how I can do the following? I want to query a table to find
when one field in that table does not match one of two other fields. The
table in question pulls together data from several sources, and we want to
find results where the one field does not have the same info as one or the
other of the other two similar fields.

For example, say we are discussing favorite color. Field A has it as "blue."
If field B or C, or both also have it as "blue," we don't want to see that in
our results. If field A has it as "blue" and neither B or C have it as blue,
we want to see those results. Can this be done, and what would be the easiest
way? Thank you in advance for any help you can provide.

--
Have a nice day!

~Paul
TargetRx
The power to know what comes next.

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''''
ptaylor

 

[Michel Walsh]

WHERE fieldA NOT IN( fiedlB, fieldC)





or



WHERE NOT( fieldA IN(fieldB, fieldC) OR fieldB IN(fieldA, fieldC) )


if fieldB can play the role of fieldA.


Sure, it could be better to have a normalized design:


SELECT id, fieldA as color FROM myTable
UNION ALL
SELECT id, fieldB FROM myTable
UNION ALL
SELECT id, fieldC FROM myTable



saved as, say, qu1, then


SELECT id, color
FROM qu1
GROUP BY id, color
HAVING COUNT(*)=1


would be more conventional. Indeed, if there is only one record for a given
couple {id, color}, it is because that color is alone, not mentioned in
another of its associated fields, and "association" being carried by "having
the same id" concept.




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Awesome! Thank you very much! That solved the problem.

--
Have a nice day!

~Paul
TargetRx
The power to know what comes next.

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''''
ptaylor