Is Not Query

[r24igh]

Hi All,

I'm trying to run a top 10 query where I ignore the names of 5 people but include the rest. The sql I've got so far is:

SELECT TOP 10 Sum([All Combined].SumOfTotalCost) AS SumOfSumOfTotalCost, [All Combined].name
FROM [All Combined]
WHERE (((Month([Month]))=Month(Now())-1)) and ((([All Combined].name) Not In (([All Combined].name)="PERSON1", ([All Combined].name)="PERSON2", ([All Combined].name)="PERSON3", ([All Combined].name)="PERSON4", ([All Combined].name)="PERSON5")))
GROUP BY [All Combined].name
ORDER BY Sum([All Combined].SumOfTotalCost) DESC;

However, this only takes off person 1, the other 4 are still appearing in the top 10. Without the "Not" part, the query works fine.

What have I done wrong?

 

[Minty]

You just need a list of persons
([All Combined].name) Not In ('person1', 'person2', 'person3' ...)

 

[NauticalGent]

Good morning r24igh,

Unfortunatly, I do not know why your SQL statement is not working. My Kung-Fu is as good as some of the others on this site.

However, when I have done something similar to this, I use a table to load what I want omitted and use a "Not-in" query to return the record set I need. Works like a charm.

I can share a screen-shot of the query grid and/or SQL if you are interested.

 

[NauticalGent]

Or...you can use Minty's Kung-Fu which is much better than mine!

 

[r24igh]

Thank you both! Much appreciated, I tried Minty's approach and that worked a charm.

Merci!

 

[Minty]

Or...you can use Minty's Kung-Fu which is much better than mine!

Not really Honk Kong Phooey maybe...

In (or Not In) can be handy if you want to dynamically build a list of records to do something with in a loop, maybe from a multi-select list box or similar.