Actually, this is a little complicated. But once you've done this, it turns out that it's quite easy. You'll build subqueries that you'll join in the final query for your report.
I may not put this together right because I don't fully understand the need. But with the way I'm interpretting it, try this...
Create a query that gives you a list of all work orders that meets your second situation (excluding the bit about the customers having 3 plus). In this case you're looking for JobTypeID=2 and JobStatus=0 and Max(DateComplete). Use the Group By option to group by CustomerID, WorkOrderID and get the Max of DateComplete. This now gives you the customer IDs, workorder ids and the maximum completed date. (Actually, if work order ID is unique independent of Customer ID, you don't need Customer ID in this query.)
Create a query that joins the above query to your WorkOrder table by workorder ID (if workorder IDs are not unique across customers, you need to join customer ID too). Set your criteria to JobTypeID=2 and JobStatus=1 and DateComplete greater than the max completed date from the above query. Use the Group By to get only unique customer IDs and count WorkOrderID (when you count a field, always count one that will never be null because if you Count a field that might be null, the record is not counted). This query now gives you the needed count of Workorders that match JobTypeID=2 and JobStatus=1 and where datacompleted is > than the max for JobTypeID=2 and JobStatus=0 for each customer ID.
It's not clear whether you only want to show only work orders that match the criteria or whether you will show just any customer that matches the situation. I'll take on the showing the work orders part since it's a bit more complicated.
At this point you can create another query that includes your Customers, WorkOrders and the query that counts and the query that gives the Max(datecompleted).
LEFT JOIN the Customers to Workorders by Customer ID. LEFT JOIN the Customers to the counting query by Customer ID, and LEFT JOIN Workorders to the Max(datecompleted) query by WorkorderID. (These joins should have an arrow leading away from the first table to second table or query.)
Based on your post, the first two conditions must be met Simultaneously while the last condition is independent of the first two.
Use two rows to set your criteria, this creates an OR situation.
On one row, check that the count of WorkOrderID from your count query is greater then 3 and that the date of the workorder is greater than the max date from the Max(DateComplete) query. This will show each work order for your first two conditions.
On another row, check that JobTypeID = 2 and JobStatus is null and DateRequired > Date(). This will show each work order for your last condition.
So the whole trick I applied here was to breakdown each component of the request into a query that I used as a subquery. Notice that I threw out the "three plus records and DateCompleted" requirement and decided which information I needed first. I.e. instead of trying to count first, I decided I needed the Max date first. Then I could count those that had a date greater than the max date.
Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group