Re: Query:When 1 field in a table is not = to another field (same tab
- From: "Michel Walsh" <vanderghast@VirusAreFunnierThanSpam>
- Date: Mon, 18 Jun 2007 10:13:27 -0400
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
"Paul (TRX)" <PaulTRX@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8F300F40-F4FB-459E-9105-9892FC7500D8@xxxxxxxxxxxxxxxx
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
.
- Follow-Ups:
- Re: Query:When 1 field in a table is not = to another field (same
- From: Paul (TRX)
- Re: Query:When 1 field in a table is not = to another field (same
- Prev by Date: SUM IN COLUMN IN SQL VIEW
- Next by Date: Re: Dates in future
- Previous by thread: SUM IN COLUMN IN SQL VIEW
- Next by thread: Re: Query:When 1 field in a table is not = to another field (same
- Index(es):
Relevant Pages
|
|
