Group query

  • Thread starter Thread starter Matti Koski
  • Start date Start date
M

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
 
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
 
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).
Click to expand...

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]
 
Sorry

I ment naturally null values.

Matti
John W. Vinson said:
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]
Click to expand...
 
Back
Top