I can already get that count without a problem. What happens is I get no results for all of the other fields when I try to count how many records are greater than 0 in the CCS>0 field when there are NONE.
I understand, and that is what the process I suggested should do.
Have you tried it?
I have set-up a simple example below, and it looks like you may not even need to use NZ, if you have a one-to-many relationship where you are have to Group the records. You would use NZ if you have a one-to-one relationship. In either case, the key is that you MUST do a Left Join.
So let's say that you have two Tables. Table1 is a client listing. Here is some sample data:
Code:
ClientName
abc
lmn
rst
xyz
Let's say that Table2 is a Sales table, that shows the number of sales you have made to each client:
Code:
ClientName SaleDate ItemSold
abc 1/1/2016 10
abc 3/1/2016 20
abc 5/1/2016 50
xyz 4/1/2006 100
Then, if we create a query where we do a LEFT JOIN from Table1 to Table2, and do an Aggregate Query grouping by ClientName and counting the ItemsSold field, we get this result:
Code:
ClientName CountOfItemSold
abc 3
lmn 0
rst 0
xyz 1
The SQL code for that query looks like:
Code:
SELECT Table1.ClientName, Count(Table2.ItemSold) AS CountOfItemSold
FROM Table1 LEFT JOIN Table2 ON Table1.ClientName = Table2.ClientName
GROUP BY Table1.ClientName;
Isn't that what you are looking for (in a simplified scenario)?