Query criteria help

[James529]

Ok, I am stymied.

Have a table of inventory records.

Multiple columns, three of them contain criteria i want to select / exclude on.

1. Department
2. Class
3. Subclass

I want to select all records for department "04" but exclude records that are in Class "05" sub-class "02" in that dept.

In other words, the record:

Department "04"
Class "05"
SubClass "02" will be excluded

but..............

Dept. "04"
Class "05"
SubClass "01" will retrieve the record.


Every way I try to set up this query it will EXCLUDE all records in Class "05" or INCLUDE only Department "05"


([AllItemsConsolidatedTable1]![Class]<>&quot;05&quot; And [AllItemsConsolidatedTable1]![SubCls]=&quot;02&quot


Can someone help me with syntax that will allow me to group the Class and Subclass together as in if field1 contains &quot;04&quot; and field2 contains &quot;05&quot; and the field3 contains &quot;02&quot; to exclude but include all others?


SELECT AllItemsConsolidatedTable1.MAS90ITEMKEY, AllItemsConsolidatedTable1.Dept, AllItemsConsolidatedTable1.Class, AllItemsConsolidatedTable1.SubCls
FROM AllItemsConsolidatedTable1
WHERE (((AllItemsConsolidatedTable1.Dept)=&quot;04&quot AND ((AllItemsConsolidatedTable1.Class)<>&quot;05&quot; And [AllItemsConsolidatedTable1]![SubCls]=&quot;02&quot);

It's like the above is treating the Class and SubClass compare exclusively and i need it to compare them mutually. maybe what i am trying to do is not possible in a query.

thanks
paul

 

[vb6novice]

This worked for me with your example (only Dept 04 and Class 05 and NOT SubClass 02)


WHERE (((AllItemsConsolidatedTable1![Dept])=&quot;04&quot AND ((AllItemsConsolidatedTable1![Class])<>&quot;05&quot And (([AllItemsConsolidatedTable1]![SubCls]=&quot;02&quot);

 

[James529]

Yes, thanks VB6novice, I forgot to mention that I don't just want Class &quot;05&quot;, there are records on the file that are in other classes that I want included but did not wish to code each class to include..just for what to exclude........... for example I want to include all records in Dept &quot;05 no matter the class / subclass with the exception of EXCLUDING class &quot;05&quot; if the SubClass is &quot;02&quot;...............

 

[James529]

Vb6novice, I just ran your example and it produced only records in SubClass &quot;02&quot; excluding all records in Class &quot;05&quot; (returning 277 records out of 3900).

I just want it to return all records in dept &quot;04&quot; if the sublcass is &quot;05&quot; and the class is &quot;02&quot;, including all other records if the dept is &quot;04&quot; (irrespective of the dept. class.) sorta like:

DoWhile Not(rst.eof)
if Dept <> &quot;04&quot; then
if Class = &quot;05&quot; and SubClass = &quot;02&quot; 'exclude
'Do Nothing
else
.addnew 'return the record
endif
endif
.MoveNext 'get next record
Wend

 

[James529]

oops, that first stmt should be &quot; if Dept = &quot;04&quot; then &quot;
;-)

 

[vb6novice]

I'm confused.

Tell me which of these you want to be returned.

Dept Class SubClass
04 04 01
04 05 01
04 05 02
04 05 03
04 05 02
04 05 01
04 05 02
05 06 01
06 06 02
06 05 02