Query Question - Please Help!! 2

[greg32246]

I have some data as follows:


Group Item Date
1 1a 06/01/2009
1 1b
2 2a
2 2b
2 2c
3 3a
3 3b
3 3c
3 3d 06/05/2009



What I want to do is return a single value for the Group where ALL items in the group have a NULL date.

So in this example, I would just want to return "2", since at least one item in each of the other groups has a non-null value.

How do I accomplish this with a query or queries??

 

[PWise]

Select distinct Group
from tablename
left join(
Select distinct [group]
from tablename
where date is not null
)GroupHasDate
on GroupHasDate.group=tablename.group
and GroupHasDate.group is null

 

[PHV]

SELECT [Group]
FROM yourTable
GROUP BY [Group]
HAVING Count([Date])=0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[greg32246]

Thanks pwise.

I had to change the last AND to a WHERE and it works perfectly! I am terrible at joins and combining SQL statements. I would have never come up with that on my own!

Thanks again,

greg

 

[greg32246]

That works as well, PHV. Thanks!

And I can express it in the query builder without typing straight SQL. For a user like me, that's good!

I was trying to use a Group By, but I didn't think of a count expression. Cool.

Thanks again,

greg