Group query

[Matti Koski]

Group query



I have groups in column1 and values in column2. I would like to pick up all
the groups that have values in column2. If the value is zero in column2 in
some group, the whole group should not exist in the query. (See below groups
A and C should not exist).



A 2

B 3

A 1

C 2

D 4

A

D 5

C

F 3



Matti

 

[Guest]

Hi Matti

Quick one this ;-)

Place this is the criteria row of column 2

Not Is Null

Oh by the way - you said that some of the records in column 2 are Zero (0)
but in the example you posted you showed Null - there is a big difference.
Just a point.

Hope this helps

 

[John W. Vinson]

Group query



I have groups in column1 and values in column2. I would like to pick up all
the groups that have values in column2. If the value is zero in column2 in
some group, the whole group should not exist in the query. (See below groups
A and C should not exist).

Not zero? or not null? Zero is a number, a legitimate value; NULL is
the ABSENCE of a number.

That said: in order to exclude all A and C records from the query
because there are NULLs (or zeros) in at least one A or C record, you
need a Subquery:

SELECT Column1, Column2
FROM yourtable
WHERE NOT EXISTS
(SELECT Column1 FROM yourtable AS X
WHERE X.Column1 = yourtable.Column1
AND NZ(X.Column2) = 0);


John W. Vinson [MVP]

 

[Matti Koski]

Sorry

I ment naturally null values.

Matti

Not zero? or not null? Zero is a number, a legitimate value; NULL is
the ABSENCE of a number.

That said: in order to exclude all A and C records from the query
because there are NULLs (or zeros) in at least one A or C record, you
need a Subquery:

SELECT Column1, Column2
FROM yourtable
WHERE NOT EXISTS
(SELECT Column1 FROM yourtable AS X
WHERE X.Column1 = yourtable.Column1
AND NZ(X.Column2) = 0);


John W. Vinson [MVP]