Count to Display Zero if no value

[corner40]

Hi
I have a list box that is displaying a count for each productID on a given date. What I really need to do is also display if the count returned is '0'. here is the SQL for the two queries that create the listbox

Original count query:
SELECT First(PackageComponent.ProductID) AS [ProductID Field], Count(PackageComponent.ProductID) AS NumberOfDups, BookingHead.BookingDate, ProductDescription.GroupTypeID
FROM BookingHead LEFT JOIN (BookingDetail LEFT JOIN (ProductDescription RIGHT JOIN (BasicProduct RIGHT JOIN (ActivitiesBooked LEFT JOIN PackageComponent ON ActivitiesBooked.PackageID = PackageComponent.PackageID) ON BasicProduct.ProductID = PackageComponent.ProductID) ON ProductDescription.ProdDescID = BasicProduct.ProdDescID) ON BookingDetail.BookingDetailID = ActivitiesBooked.BookingDetailID) ON BookingHead.BookingHeadID = BookingDetail.BookingHeadID
GROUP BY BookingHead.BookingDate, ProductDescription.GroupTypeID, PackageComponent.ProductID, BookingDetail.Status
HAVING (((Count(PackageComponent.ProductID))>0) AND ((BookingDetail.Status)="booked"));



ListBox rowsource:

SELECT q_ProdIDCountBasedonDatePack.[ProductID Field], q_ProdIDCountBasedonDatePack.BookingDate, q_ProdIDCountBasedonDatePack.NumberOfDups
FROM q_ProdIDCountBasedonDatePack
WHERE (((q_ProdIDCountBasedonDatePack.[ProductID Field])=122 Or (q_ProdIDCountBasedonDatePack.[ProductID Field])=123) AND ((q_ProdIDCountBasedonDatePack.BookingDate)=[forms]![f_weemax].[cbodate]));

Both queries work as expected, but I just need to be able to display a '0' if no record counted.

thanks
jeremy

 

[PHV]

Have you tried to replace this:
HAVING (((Count(PackageComponent.ProductID))>0)
By this ?
HAVING (((Count(PackageComponent.ProductID))>=0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

HAVING (((Count(PackageComponent.ProductID))>0)

this portion of your first query elimates all products that have a count of zero!!

I modified the first query a bit, perhaps it will give you the correct information:

SELECT First(PackageComponent.ProductID) AS [ProductID Field], Count(PackageComponent.ProductID) AS NumberOfDups, BookingHead.BookingDate, ProductDescription.GroupTypeID
FROM BookingHead LEFT JOIN (BookingDetail LEFT JOIN (ProductDescription RIGHT JOIN (BasicProduct RIGHT JOIN (ActivitiesBooked LEFT JOIN PackageComponent ON ActivitiesBooked.PackageID = PackageComponent.PackageID) ON BasicProduct.ProductID = PackageComponent.ProductID) ON ProductDescription.ProdDescID = BasicProduct.ProdDescID) ON BookingDetail.BookingDetailID = ActivitiesBooked.BookingDetailID) ON BookingHead.BookingHeadID = BookingDetail.BookingHeadID
WHERE BookingDetail.Status="booked"
GROUP BY BookingHead.BookingDate, ProductDescription.GroupTypeID, PackageComponent.ProductID, BookingDetail.Status;

Leslie

 

[PHV]

Leslie, modification for modification, why keep the First function on a grouped field ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[corner40]

Hi PHV and Hi Leslie
PHV I had previously tried changing it to >=0 but with on success.

Leslie, thanks for the modified code but it too did not work.

Any other information I might need to provide I can certainly do. Any other suggestions would be much appreciated.

thanks guys. if I can get this figured out my day will be made
Jeremy

 

[lespaul]

PHV, Didn't see the FIRST!!!! Don't know why your solution didn't work for him anyway!



Leslie

 

[corner40]

Here is a sample I whipped up. please take a look

http://rossiter.mntw.ca/downloads/KidZoneSamples.zip

thanks
Jeremy

 

[corner40]

No one can come up with a solution. Help Please!!! You guys are all smart.
thanks for looking
Jeremy

 

[JonFer]

corner40 - In your first query, use this SQL after the FROM clause:

WHERE BookingDetail.Status="booked"
GROUP BY BookingHead.BookingDate, ProductDescription.GroupTypeID, PackageComponent.ProductID, BookingDetail.Status ;

I don't see a need for the HAVING Count>=0 criteria because a count result will always be >=0.


John

http://www.interautomateconsulting.com

 

[cmmrfrds]

If you are counting Count(PackageComponent.ProductID) and because of the left join, in some cases, there may not be a productID i.e. Null, then you could use Sum instead after forcing the nulls to a 0. This would give the same results and account for the nulls with a 0.

Sum(iif(PackageComponent.ProductID is Null,0,1))
AS NumberOfDups