Excluding certain records from a query

  • Thread starter Thread starter Tony
  • Start date Start date
T

Tony

Hi Group,

I've been working at this for a couple of hours and I'm
getting nowhere - need some help.
I am trying to remove records which match 3 critereon. I
can get it to work for the first criteria using <> but I
cannot get it to work for the required 3 criteria, which
is...

Programme = 002
Priority = 001
Measure = 007

Programme = 002
Priority = 004
Measure = 003

Programme = 002
Priority = 006
Measure = 002

Can someone help

Tony

Here is the basic SQL for the first criterion

SELECT DISTINCTROW tblApplication.Programme,
tblProgramme.OperationalProgrammeName,
tblPriority.PriorityName, tblApplication.Priority,
tblApplication.Measure
FROM (tblApplication LEFT JOIN tblProgramme ON
tblApplication.Programme =
tblProgramme.OperationalProgrammeCode) LEFT JOIN
tblPriority ON (tblApplication.Programme =
tblPriority.OperationalProgrammeCode) AND
(tblApplication.Priority = tblPriority.PriorityCode)
WHERE (((tblApplication.Programme)<>"002") AND
((tblApplication.Priority)<>"001") AND
((tblApplication.Measure)<>"007"));
 
This is still unclear as to what you want, however if you
want to just exclude these few record types you would make
a new field in you query like this...
PPM:[programme] & [priority] & [measure]
then in the where clause you would put <>"002001007"
and "....".

Martin
 
The following includes only records that do not match one of the sets of criteria.

WHERE Not
((tblApplication.Programme = "002" AND
tblApplication.Priority ="001" AND
tblApplication.Measure ="007")
OR (tblApplication.Programme = "002" AND
tblApplication.Priority ="004" AND
tblApplication.Measure ="003")
OR (tblApplication.Programme = "002" AND
tblApplication.Priority ="006" AND
tblApplication.Measure ="002"))

It may be relatively slow, depending on how it gets optimized by the query compiler.
 
John,

Perfecto! Thanks for your help - learnt something new
there.

Thanks

Tony
-----Original Message-----
The following includes only records that do not match one of the sets of criteria.

WHERE Not
((tblApplication.Programme = "002" AND
tblApplication.Priority ="001" AND
tblApplication.Measure ="007")
OR (tblApplication.Programme = "002" AND
tblApplication.Priority ="004" AND
tblApplication.Measure ="003")
OR (tblApplication.Programme = "002" AND
tblApplication.Priority ="006" AND
tblApplication.Measure ="002"))

It may be relatively slow, depending on how it gets
Click to expand...
optimized by the query compiler.
 
Back
Top