I have been asked to create a report that will show all the properties in the Customers table that do not have any records on the Workorders table with (JobTypeID=2 AND JobStatus="0")

So far I have come up with this to show all the properties and the count of records meeting the criteria:

SELECT C.CustomerID, ([JobStatus]="0" And [JobTypeID]=2) AS Kount, Count([Kount]) AS KountComplete
FROM Workorders AS W INNER JOIN Customers AS C ON W.CustomerID = C.CustomerID
GROUP BY C.CustomerID, ([JobStatus]="0" And [JobTypeID]=2);

and it will show you the records.

I thought I would be able to use this information to run a second query and list all the records that have a count of 0 but it doesn't work. It shows you each combination with a count, eg

Customer ID Kount KountComplete
2 0
2 -1 2
2 0 6

So the above record has 2 records that have (JobTypeID=2 and JobStatus="0"), 6 that don't and 0 that come somewhere in between.

Another record is shown as

4428 0
4428 0 5

This is the sort of record I want to identify as it doesn't have any records where Kount = -1. BUT thanks to the grouping effect if I say where Kount <> -1 then it would still include CustomerID 2.

So, should I approach this another way or tweak this one?

Thanks

Andrew

Ok, I've managed to get it. Using the query below, I then did a Right Join as shown below to list all records and then filter out those with the JobTypeID=2 and JobStatus="0":

SELECT C.CustomerID, QC.KountComplete, C.NameNumb, C.BillingAddress, C.City, C.PostalCode
FROM qryCountCompleteServices AS QC RIGHT JOIN Customers AS C ON QC.CustomerID = C.CustomerID
WHERE (((C.Owner)=6218) AND ((C.BlockCustomer)=0) AND ((QC.KountNA) Is Null));

The Owner and BlockCustomer were added after the event to further filter.