Help with Grouping and NULL Values!

S

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

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top