Excluding multiple criteria from a query

[Tony]

Hi Group,

I am having big problems with this query and would
apprecaite some expert help as I have spent 4 hours trying
to get my head round it. I am trying to devise a query
that removes the following criteria...

Programme = 003
Priority = 001
Measure = 001, 002, 003, 004, 005, 006, 007

Programme = 003
Priority = 002
Measure = 001, 002, 003, 004

Programme = 003
Priority = 003
Measure = 001

Programme = 003
Priority = 004
Measure = 001

Here is the WHERE clasue of my SQL so far but it doesn't
seem to be working. Where am I going wrong?

WHERE (((qryStatsMaster.ApplicationStatus)="LOOAccepted"
Or (qryStatsMaster.ApplicationStatus)="Completed") AND
((([qryStatsMaster].[Programme]="003" And
[qryStatsMaster].[Priority]="001" And
[qryStatsMaster].[Measure]="001" And "002" And "003"
And "004" And "005" And "006" And "007")
Or ([qryStatsMaster].[Programme]="003" And
[qryStatsMaster].[Priority]="002" And
[qryStatsMaster].[Measure]="001" And "002" And "003"
And "004")
Or ([qryStatsMaster].[Programme]="003" And
[qryStatsMaster].[Priority]="003" And
[qryStatsMaster].[Measure]="001")
Or ([qryStatsMaster].[Programme]="003"
And [qryStatsMaster].[Priority]="004" And
[qryStatsMaster].[Measure]="001"))=False))
ORDER BY qryStatsMaster.Priority, qryStatsMaster.Measure;

Many thanks

Tony

 

[anonymous]

This question was posted a few days ago and answers were
posted. Why are u asking again?

 

[Tony]

Group,

A similar question was posted a few days ago (by me) but
it is not the exact question. I had used the methodology
that I gained from the last post to try and solve this new
question but after 4 hours of precious work time trying I
gave up - hence the post - I just don't how to do it.

The previous question was based on 1 criteria under each
Measure but now it has extended to multiple criteria under
each Measure. I can get it to work for 1 criteria but not
multiple.

It would be appreciated that an answer or help be posted
as I genuiely cannot solve this. I always spend a lot of
time trying to solve queries before I post and always as a
last resort. I am fairly new to SQL and I need to learn
how to solve complex queries such as this.

Regards

Tony

-----Original Message-----
This question was posted a few days ago and answers were
posted. Why are u asking again?

-----Original Message-----
Hi Group,

I am having big problems with this query and would
apprecaite some expert help as I have spent 4 hours trying
to get my head round it. I am trying to devise a query
that removes the following criteria...

Programme = 003
Priority = 001
Measure = 001, 002, 003, 004, 005, 006, 007

Programme = 003
Priority = 002
Measure = 001, 002, 003, 004

Programme = 003
Priority = 003
Measure = 001

Programme = 003
Priority = 004
Measure = 001

Here is the WHERE clasue of my SQL so far but it doesn't
seem to be working. Where am I going wrong?

WHERE (((qryStatsMaster.ApplicationStatus)="LOOAccepted"
Or (qryStatsMaster.ApplicationStatus)="Completed") AND
((([qryStatsMaster].[Programme]="003" And
[qryStatsMaster].[Priority]="001" And
[qryStatsMaster].[Measure]="001" And "002" And "003"
And "004" And "005" And "006" And "007")
Or ([qryStatsMaster].[Programme]="003" And
[qryStatsMaster].[Priority]="002" And
[qryStatsMaster].[Measure]="001" And "002" And "003"
And "004")
Or ([qryStatsMaster].[Programme]="003" And
[qryStatsMaster].[Priority]="003" And
[qryStatsMaster].[Measure]="001")
Or ([qryStatsMaster].[Programme]="003"
And [qryStatsMaster].[Priority]="004" And
[qryStatsMaster].[Measure]="001"))=False))
ORDER BY qryStatsMaster.Priority, qryStatsMaster.Measure;

Many thanks

Tony



.

.

 

[John Spencer (MVP)]

First, your syntax is incorrect

[qryStatsMaster].[Measure]="001" And "002" And "003" And "004"

Would have to be
[qryStatsMaster].[Measure]="001" And [qryStatsMaster].[Measure]="002" And
[qryStatsMaster].[Measure]="003" And [qryStatsMaster].[Measure]="004"

The problem is that no record will ever have the different values in the same
field. I suspect that you need to use the In Clause

[qryStatsMaster].[Measure] IN ("001", "002", "003", "004")

So for the first bit

WHERE (Programme = "003" and Priority = "001" AND
Measure In ("001", "002", "003", "004", "005", "006", "007"))
OR (Programme = "003" and Priority = "002" AND
Measure In ("001", "002", "003", "004"))
OR ...