Ct:1 Appears in Left Join for all values rather than selected valu

[Chuck W]

Hi,
I have a table called tblCodeBlue that has values such as AcctNumber, Unit,
and CodeBlueDate. I created a query called qryCodeBlue1 with AccoutNumber,
Unit, Month (Calculated using CodeBlueDate). I am importing my data into
another system so I need a numeric value so I also added Ct:1 for Count with
a value of 1. I also have a table called tblMonths that has Month and Unit
as fields. There are eight units in our facility and we report on data going
back to 1/1/08 so this table has eight records for each month from 1/1/08 to
12/1/10. I created a query called qryCodeBlue2 and am doing a left join on
tblMonths to qryCodeblue1 with a left join of Month => Month and Unit=>Unit.
I am doing this because I want a record to appear for all units and all
months regardless of if they have a value in qryCodeBlue1. So if there are
no code blues for a given unit for a given month, there is still a record for
graphing purposes (I need to show all months). I want the Ct:1 field to show
as null if a unit does not have code blues though.

The problem is the the Ct:1 value from qryCodeBlue1 that I added to
qryCodeBlue2 shows for all values when I only want it to show for records
from qryCodeBlue1 only if that make sense. Can someone help?

Thanks,

 

[KARL DEWEY]

Try this --
IIF([CodeBlueDate] Is Null, Null, Count(1)) As [Ct:1]