Query Left Join

[jsic1210]

Hello, I'm trying to create a query to count each customer's (tblCustomer) Active or Open contracts (tblContract). I'm using a left join, but it's still not taking all entries from tblCustomer - if the count is zero, it's not showing up as a line in the query. I have 533 customers, but the query only produces 465 records. Here is my SQL statement:

SELECT tblCustomer.CustomerID, tblCustomer.CoName, Count(tblContract.ContractID) AS [Open Active Count]
FROM tblCustomer LEFT JOIN tblContract ON tblCustomer.CustomerID = tblContract.CustomerID
WHERE tblContract.Status = "Active" Or tblContract.Status = "Open"
GROUP BY tblCustomer.CustomerID, tblCustomer.CoName

Is there a way to do this without using a DCount?

 

[GinaWhipp]

Because of your WHERE Statement...

WHERE tblContract.Status = "Active" Or tblContract.Status = "Open"

...you need to add *Or Is Null*

 

[jsic1210]

Do you mean I need to add:

Or tblContract.Status Is Null

Because that will just add to the count by including records where the Status is Null. I want it to return 0 for customers having only contracts with statuses of "Terminated," "Inactive," Or Null.

 

[GinaWhipp]

Oh, then you need to add the balance because right now it is only showing the ones that have a Status of Active or Open. In other words, if you want to see all the customers then you need to provide all Status' and Is Null.

 

[jsic1210]

I'm not sure what you mean by "add the balance."

If I remove the WHERE statement, it shows all 533 records, but the count shows the count of ALL contracts. If company ABC has 1 contract that is Terminated (or Inactive or Null), and no other contracts, I want the query to return as follows:

CustomerID     Company     Open/Active Contracts
         1     ABC                  0

 

[GinaWhipp]

To get the 0 to show in that column, you'll need an IIf Statement, so...

SELECT tblCustomer.CustomerID, tblCustomer.CoName, tblContract.Status, IIf([Status]="Open" Or [Status]="Active",1,0) AS [Open]
FROM tblCustomer LEFT JOIN tblContract ON tblCustomer.CustomerID = tblContract.CustomerID