group by null 2

[dinster]

Hi all, i want to count the records and group them. But this doesnt work if there's empty records in location.

SELECT Sheet4.Location, Count(Sheet4.Location) AS CountOfLocation
FROM Sheet4
GROUP BY Sheet4.Location;


e.g

Location count
test1 5
test2 7
empty 0 (where this should be 4 records)

so how do i group empty records?


cheers

 

[Remou]

You need to get rid of the nulls, so how about:

SELECT Sheet4.Location, Count(Nz([Location],0)) AS Loc
FROM Sheet4
GROUP BY Sheet4.Location;

 

[PHV]

Use this:
SELECT Sheet4.Location, Count([!]*[/!]) AS CountOfLocation
FROM Sheet4
GROUP BY Sheet4.Location

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

 

[r937]

both you guys are wrong

you gots to use IIF(Location='','Empty',Location)

don't get rid of the nulls, count them!

;-)

r937.com | rudy.ca

 

[Remou]

In Access a Null is not equal to '', yesno?

 

[r937]

that's right!

and dinster said "if there's empty records in location" not "if there's null records in location"

my comment about counting them was in response to your suggestion to get rid of them

;-)

r937.com | rudy.ca

 

[PHV]

don't get rid of the nulls, count them!
Rudy, my suggestion counts the nulls !

 

[r937]

yes i know, phv, but it doesn't call them 'empty'

;-)

r937.com | rudy.ca

 

[Remou]

I do not think that your suggestion counts nulls, r937, it counts empty strings, calling them empty, as you pointed out.

 

[r937]

yes, i know!! it isn't supposed to count nulls!!

have we finally got rid of the nulls?

you started it

;-)

r937.com | rudy.ca

 

[Remou]

Certainly I started it, but you joined in!

12 Dec 07 10:05

don't get rid of the nulls, count them!