Aggregate Query

[DonL]

Having problems with an aggregate query:

I have a table with fields Job Type, Manager, Start Date, Closed Date.

Each record identifies a unique job. The Job Type identifies the type of job! One manager could be on many jobs. The start date is the date the job started. The closed date is when the job was closed. If there is no closed date then the job is open.

At the moment I have created a form that has a text box for the user to enter a 'Week Start Date'. Based on this week start date I need to detail the following:

• For each job type for the week entered - the total number of jobs started in the week for each manager; the total number of closed jobs for that week; and the total number of open jobs for that week. Total Jobs should = closed + open.

Without being able to use criteria in the count function I really don't know how to get the open jobs piece out. I'm getting errors such as too complex etc when I do this. What I have so far:

• Group by Job Type
• Group By Manager
• Count Manager (gives total jobs)
• Where Date added is >=[forms]![Date Capture Form]![text0] And <[forms]![Date Capture Form]![text0]+7
• Count Date Closed (give closed jobs for that period)
• Count Date Closed If " " (this is giving me the error to get open jobs).

Your help is appreciated!

[DonL]

I've figured it out so no need to reply. Just needed a subquery.