Access Query "Show number 0 if no records are selected by counting"

[johan]

Please help me out,

I'd created a selectquery with a count option which results in 'zero
records'.
This is oke, so that is not the problem.

But,...
Normally when there are records who match the count selectioncriteria
you see one record which shows the value.
In my case, because there are no matching records, I want to see the
number 0.

I do not know a solution, but it should be something like
expr: IIF(value of count records[dataveld] = zero ;0;value of
count records[dataveld])

so please please, is there a solution ??

ps. I need a query solution (no VBA etc..)

Thanks and regards,
Johan

 

[Ken Sheridan]

Johan:

I'm not sure I understand you fully, but if you are saying that you want a
row with a count of zero returned where no row is currently returned because
there is no match between related tables the you might be able to use an
OUTER JOIN so that even where there is no match a row will be returned. For
example the following would return the numbers of orders placed by each
customer only where the customer has placed at least one order:

SELECT CompanyName, Count(OrderID) AS CountOfOrders
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY CompanyName;

The following would also return customers who have placed no orders, with a
zero in the CountOfOrders column:

SELECT CompanyName, Count(OrderID) AS CountOfOrders
FROM Customers LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY CompanyName;

However, if you restrict the query on a column in the orders table, e.g. all
orders with a value of 20 or more currency units:

SELECT CompanyName, Count(OrderID) AS CountOfOrders
FROM Customers LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE OrderValue >= 20
GROUP BY CompanyName;

the query would not return any rows for those customers who have either
placed no orders or only orders of less than 20 currency units value. A
restriction can only be made on a column from the table on the left side of a
LEFT OUTER JOIN, not on the right side. You'd have to use a subquery to
return the count:

SELECT CompanyName,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID
AND OrderValue >= 20)
AS CountOfOrders
FROM Customers;

I suspect the last solution, using a subquery, is probably what you need.

Ken Sheridan
Stafford, England

 

[johan]

Thanks.

Problem solved.

regards,
Johan