Access count with join problem

[jsantari]

I have the following query which I am trying to use to count the number of items in a category. The query works until I add the following:

and items.isactive='Yes'

If I leave the above out I get the counts but it includes the items that are flagges as inactive. Anyone know how to make this work?

Here is the full query that does not work correctly:

select categories.id,categories.parentcd,categories.catcd, count(items.id) as ct from categories left join items on categories.id = items.catid where categories.parentcd='Automotive' and items.isactive='Yes' group by categories.id,categories.parentcd,categories.catcd

Thanks,
Jim

 

[PHV]

If items.isactive is a boolean (YesNo) field:
and items.isactive=True

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