count fun returns with no data, how can i get it to return zero

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

Guest

I have entered a count function in a queries with three other criterias and
it returns no data... how can i get it to reurn zero if no data was found
 
Can you post the SQL for the query so we can have a guess? Having an idea of
what the tables look like and what's in them would be helpful too...
 
SELECT Count([Implementation-1].[Client Name]) AS [CountOfClient Name],
[Implementation-1].Status, [Implementation-1].[Effective Date]
FROM [Implementation-1]
GROUP BY [Implementation-1].Status, [Implementation-1].[Effective Date]
HAVING ((([Implementation-1].Status)="Transitioned") AND
(([Implementation-1].[Effective Date])="10/1/2005"));


Here is the SQL for the query. If there is no data to be found, can i get it
to return a zero?
 
I'm pretty sure that the nature of the group by clause is such that it
returns nothing if there are no records. To get it to return a record with a
zero count, you'll need to create a new table or query that you can join with
your existing ClientCount query using an Outer join.

One way to do this is to create a separate table (eg. "SearchTable") with
fields "Status" and "Effective Date", and put one record in it with Status =
Transitioned and Effective Date = 10/1/2005

The SQL to join this to your ClientCount query is:
SELECT SearchTable.Status, SearchTable.[Effective Date], ClientCount.
[CountOfClient Name]
FROM SearchTable LEFT JOIN ClientCount ON (SearchTable.[Effective Date] =
ClientCount.[Effective Date]) AND (SearchTable.Status = ClientCount.Status);

Note that this returns a null rather than a zero count, but you can deal with
that using an IIF expression.

Also, if you do it this way, you won't need the HAVING clause in your
ClientCount query.

The messy part about this is that you need to add a record to the table for
every Status / Date combination you want a count for. Whether this is what
you want depends on whether you want to enter all of the criteria up front,
or only when the query runs - it's not clear to me from your SQL how the user
interface will work, because you've got the criteria in hard coded into the
SQL...
 
Back
Top