cigarprofiler
Registered User.
- Local time
- Today, 04:55
- Joined
- Mar 25, 2017
- Messages
- 32
On this thread, plog helped me out with a bunch of queries I was struggling with.
My question concerns the query Review_sub_10:
This query counts the number of observations in the "animal" category for each review_id. There are nine other categories, but not every review_id will have observations in all categories. And that's where I am asking for the impossible:
The normal behaviour for the query is not to return a record for a review_id for which there is no public_tblreview_items.review_item_type_id=16.
What I would like, however, is for the query to return zero in case Count(public_tblreview_items.review_item_id) finds nothing.
I have searched all morning for a possible solution. I checked out COALESCE, Nz, Iif-statements, EXIST, IN, adding Boolean logic to the query's WHERE-statement and what not, all to no avail. It's not a null-to-zero conversion, because there is no null - there is nothing.
I can think of a workaround in VBA where I enter a dummy observations with value 0 to each review, and rewrite the queries to sum review_item_type_value instead of counting review_item_id, but I'm hoping the query above can be adjusted to return zero in case of no observations.
Is that possible?
My question concerns the query Review_sub_10:
Code:
SELECT public_tblreview_items.review_id, Count(public_tblreview_items.review_item_id) AS observations_animal
FROM public_tblreview_items
WHERE (((public_tblreview_items.review_item_type_id)=16))
GROUP BY public_tblreview_items.review_id;This query counts the number of observations in the "animal" category for each review_id. There are nine other categories, but not every review_id will have observations in all categories. And that's where I am asking for the impossible:
The normal behaviour for the query is not to return a record for a review_id for which there is no public_tblreview_items.review_item_type_id=16.
What I would like, however, is for the query to return zero in case Count(public_tblreview_items.review_item_id) finds nothing.
I have searched all morning for a possible solution. I checked out COALESCE, Nz, Iif-statements, EXIST, IN, adding Boolean logic to the query's WHERE-statement and what not, all to no avail. It's not a null-to-zero conversion, because there is no null - there is nothing.
I can think of a workaround in VBA where I enter a dummy observations with value 0 to each review, and rewrite the queries to sum review_item_type_value instead of counting review_item_id, but I'm hoping the query above can be adjusted to return zero in case of no observations.
Is that possible?
Last edited:
