How do I return zero for a query count?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When using Count in the Total field of a query, if there aren't any records
that match the specified criteria, how do I get the query to return a value
of 0, rather than just not including a record at all?
 
You need to create two queries
1. To return the Sum per something, let say customer

SELECT CustNum, Sum(Balance) AS SumOfBalance
FROM TableName
GROUP BY CustNum

2. Include the above query, linked to the original table, this time
returning all the customers with the sum from the query, and return zero to
all the customers that doesn't apear in the query

SELECT TableName.CustNum, nz([SumOfBalance],0) AS CustBalance
FROM TableName LEFT JOIN QueryName ON TableName.CustNum= QueryName.CustNum
GROUP BY TableName.CustNum, nz([SumOfBalance],0)

GoodLuck
 
Sorry, change the Sum with count

--
\\// Live Long and Prosper \\//
BS"D


Ofer said:
You need to create two queries
1. To return the Sum per something, let say customer

SELECT CustNum, Sum(Balance) AS SumOfBalance
FROM TableName
GROUP BY CustNum

2. Include the above query, linked to the original table, this time
returning all the customers with the sum from the query, and return zero to
all the customers that doesn't apear in the query

SELECT TableName.CustNum, nz([SumOfBalance],0) AS CustBalance
FROM TableName LEFT JOIN QueryName ON TableName.CustNum= QueryName.CustNum
GROUP BY TableName.CustNum, nz([SumOfBalance],0)

GoodLuck
--
\\// Live Long and Prosper \\//
BS"D


derwwing said:
When using Count in the Total field of a query, if there aren't any records
that match the specified criteria, how do I get the query to return a value
of 0, rather than just not including a record at all?
Click to expand...
Click to expand...
 
Back
Top