Count does not give me 0

[Guest]

I want to count how many orders a customer placed in a month, in the case of
a customer did place order in that month, I want it show me 0. Unfortunately,
my statement does not show me the customer without a order. Any idea? the
following is the statement:

Select CustomerID, iif(Count(orders) is null, 0, Count(orders)) from Orders
where Month(OrderDate=5)

 

[OfficeDev18 via AccessMonster.com]

I want to count how many orders a customer placed in a month, in the case of
a customer did place order in that month, I want it show me 0. Unfortunately,
my statement does not show me the customer without a order. Any idea? the
following is the statement:

Select CustomerID, iif(Count(orders) is null, 0, Count(orders)) from Orders
where Month(OrderDate=5)

Try this:

Select CustomerID, Count(nz(Orders,0)) As CountOfOrders FROM tblCustomers
LEFT JOIN Orders ON tblCustomers.CustomerID=Orders.CustomerID WHERE Month
(OrderDate)=5

(I assume your last few characters are simply a typo.) If you don't have a
separate table for customers (I assume in my SQL that you do) then your data
is not normalized properly; I suggest you put customer data into a separate
table (see the Access help file on normalization) before continuing. Also, if
you're not familiar with different joins, or the nz() function, see the help
file about them too.

Alternatively, you can arrange all your CustomerIDs into a separate sub-query,
as follows:

Select CustomerID FROM Orders GROUP BY CustomerID

Let's call that qryCustOrd. Now your main query can be as follows:

Select CustomerID, Count(nz(Orders,0)) As CountOfOrders FROM qryCustOrd LEFT
JOIN Orders ON qryCustOrd.CustomerID=Orders.CustomerID WHERE Month(OrderDate)
=5

That should give you the data you want.

Also, to give you more flexibility, you can change the last part of the SQL
to:

WHERE Month(OrderDate)=[Enter Month Number] And Year(OrderDate)=[Enter Year]

That forces the user to enter the month number and year each time he runs the
query; you don't have to hard-code the information right in the SQL. Also, if
he wants historical data, it's available at the touch of a button.

 

[Duane Hookom]

You would need to join in the customer table that has every customerid in
it. Use a join that includes all records from the customer table.

 

[Guest]

An option you can use will be a dcount

Select CustomerID, Dcount("*", "[Orders]","CustomerID = " & [CustomerID] & "
AND Month(OrderDate)=5) From Orders GROUP BY Orders.CustomerID, Dcount("*",
"[Orders]","CustomerID = " & [CustomerID] & " AND Month(OrderDate)=5)

 

[Guest]

Sorry, I forgot to close the dcount

Select CustomerID, Dcount("*", "[Orders]","CustomerID = " & [CustomerID] & "
AND Month(OrderDate)=5") From Orders GROUP BY Orders.CustomerID, Dcount("*",
"[Orders]","CustomerID = " & [CustomerID] & " AND Month(OrderDate)=5")

 

[Guest]

I don't like this query so much, and I can't go to sleep thinking about it.
You can create one query that return all the customers

Select CustomerID From Orders GROUP BY Orders.CustomerID

And create a second query, based on the first one

Select CustomerID, Dcount("*", "[Orders]","CustomerID = " & [CustomerID] & "
AND Month(OrderDate)=5") From QueryName GROUP BY QueryName.CustomerID,
Dcount("*",
"[Orders]","CustomerID = " & [CustomerID] & " AND Month(OrderDate)=5")

that way the count doesnt work over time
--
I hope that helped
Good luck

Sorry, I forgot to close the dcount

Select CustomerID, Dcount("*", "[Orders]","CustomerID = " & [CustomerID] & "
AND Month(OrderDate)=5") From Orders GROUP BY Orders.CustomerID, Dcount("*",
"[Orders]","CustomerID = " & [CustomerID] & " AND Month(OrderDate)=5")

--
I hope that helped
Good luck

I want to count how many orders a customer placed in a month, in the case of
a customer did place order in that month, I want it show me 0. Unfortunately,
my statement does not show me the customer without a order. Any idea? the
following is the statement:

Select CustomerID, iif(Count(orders) is null, 0, Count(orders)) from Orders
where Month(OrderDate=5)

 

[Guest]

Hi everyone,

Thank a lot for all your help. I appreciate it. I kind of stuck in something
else. I will try your solutions later.

Thanks again. Have a good day.

 

[Michel Walsh]

Hi,


Indeed, and COUNT on a field of the other table.

COUNT(*) cannot return zero, in this context... if you think about it,
if it would return 0, it will also return that Celine Dion has ALSO a count
of 0, and so for Georges W., and so and so. The presence of the record (with
the close set of "names" that interest you), by itself, makes it 1 record,
or more, so COUNT(*), in that context, should return at least 1 (or more).
But COUNT(fieldname) removes the NULL values, it counts the number of
records where the mentioned field has a value other than null. So,
COUNT(fieldName) can return zero, as in the case where the field name is the
"unpreserved" table of an outer join, or, if you prefer, if it is a field
name from the "other" table, as per Duane instructions.



Hoping it may help,
Vanderghast, Access MVP