Query Count Records Unless another fie

[pbDudley]

Hello
I have a Count function query as below which combines 2 fields and creates what is called ASSIGNED_PLAN. This is my unique value
Then it counts the records in a field called plan_site_name and calls it SitesAffected

The outcome is this:
ASSIGNED_PLAN SitesAffected


N-9999 6
N-7777 5

Now I need to not count the records based off of if another field has a date value or not. If it has a date in the field called (Sites_CANCEL_DT, don't count the records in plan_site_name

I don't know how to add that to my query below. Or if its possible in the MS Access 2010 query

Any help is much appreciative.

SELECT [tbl_plan_reviews].[plan_ctrl_cd] & "-" & [tbl_plan_reviews].[plan_ctrl_num] AS ASSIGNED_PLAN,
Count(tbl_plan_sites.plan_site_name) AS SitesAffected
FROM tbl_plan_reviews INNER JOIN tbl_plan_sites ON ( tbl_plan_reviews.plan_ctrl_num = tbl_plan_sites.plan_ctrl_num ) AND ( tbl_plan_reviews.plan_ctrl_cd = tbl_plan_sites.plan_ctrl_cd )
GROUP BY [tbl_plan_reviews].[plan_ctrl_cd] & "-" & [tbl_plan_reviews].[plan_ctrl_num]
ORDER BY [tbl_plan_reviews].[plan_ctrl_cd] & "-" & [tbl_plan_reviews].[plan_ctrl_num];

[aytee111]

If you are doing this is query design (which I recommend) add the date field and use "WHERE" in the total line, then add your criteria below.

[pbDudley]

thank you, i overlooked that this morning, Im using query design, the sql above was copied out of the SQL viewer

thanks