Help with Grouping and NULL Values!

[Sussman, Alex]

Hi, I am trying to eliminate NULL values when grouping in Access but
it does not seem to work!

I have a table that looks like this:

ID CreateDate CloseDate
1 10/01/2008 NULL
1 NULL 10/15/2008

I would like my result to be :

ID CreateDate CloseDate
1 10/01/2008 10/15/2008

The query I am using is the following:
Select ID, CreateDate, CloseDate
from Table1
Group by CreateDate, CloseDate

If I wrote this exact same query in MS SQL, it would eliminate the
NULL values (i can rename null to whatever i want here or even just
leave the values blank), however they are not eliminated in access.
How do I group to get the results I need?? Any help would be greatly
appreciated!

 

[S.Clark]

Select ID, Min(CreateDate), Max(CloseDate)
from Table1
Group by ID

Maybe?

 

[Sussman, Alex]

Thanks! That works in this case because the values happen to be
numbers. However, I can see a case where instead of numbers, I have
text fields that I want to group and the nulls get in the way. Any
idea what to do in that case? It would look like the following:

ID Favorite Dislikes
1 Apples NULL
1 NULL Oranges

I would like my result to be :

ID Favorite Dislikes
1 Apples Oranges

Any idea what the query would be to get this result?

 

[S.Clark]

I guess it depends on how many records each ID has. If there is only ever 1
favorite and 1 dislike then the Min() or First() function will work.