Need Query to return record even if Count=0

  • Thread starter Thread starter Dkline
  • Start date Start date
D

Dkline

I have a query which is counting the number of incomplete applications. If a
person does NOT have any incompletes, I either need to still return a record
with a count of 0. Right now when the count is 0, there is no record. This
query is used inside of another query.

I've looked at the other threads on similar discussions and have tried
several of them with no joy. I'm missing something.

SQL is:
SELECT qryInsuredsAnnuitants_Names.InsAnnID,
qryInsuredsAnnuitants_Names.InsAnnFullName,
IIF(Count(tblAPS.APSStatus)=0,Null,Count(tblAPS.APSStatus)) AS
CountOfAPSStatus, tblAPS.APSStatus
FROM qryInsuredsAnnuitants_Names INNER JOIN tblAPS ON
qryInsuredsAnnuitants_Names.InsAnnID = tblAPS.InsAnnID
GROUP BY qryInsuredsAnnuitants_Names.InsAnnID,
qryInsuredsAnnuitants_Names.InsAnnFullName, tblAPS.APSStatus
HAVING (((tblAPS.APSStatus)<>"1"))
ORDER BY qryInsuredsAnnuitants_Names.InsAnnID;
 
Break this into 2 queries.
The first is a reduced version of what you have, and is used to just count
the number of applications per person. Lets call this QueryA. The second
query joins the "persons" table to QueryA. By changing the join you can
return ALL people, and use an IIF (much as you have done) to change the Null
to a zero where there is no record from QueryA

Hope this makes sense



Select Person, .. . . from
 
Got it. Set the Left Join and the expression to:
APSStatus:
IIf(IsNull([qryAPSIncompleteCount].[CountOfAPSStatus]),0,[qryAPSIncompleteCount].[CountOfAPSStatus])

Thank you!
 
Back
Top