Well Richard,

You really made me look fool when I watched lesson 5 of the SQL seminar. I couldn’t believe that I took such a long route to achieve exactly the same result. I will share with you and my fellow students the problem, the solution that I applied and what I should have done really….

The problem;
My customer is a manager of a TB program. He has 18 health posts that report to him. He is expected to visit/supervise each health post at least once in a quarter. In reality, he can visit a facility one or more times per quarter. However for one reason or another, some facilities may not be visited within a three months period. At the end of a quarter, the TB manager sends a report to his HQ about the number of health posts he visited…not how many times (so the total should not exceed the 18 health posts).

This is what I did;
*VisitQ is a parameter query and it stores the names of the health posts and the exact dates when the TB manager visited them. In one of the TextBoxes of the report, I inserted the following formula (in the ControlSource;

=IIf(Nz(DCount("VisitID","VisitQ","HealthPost='Ruhaama H/C III' "),0)=0,0,1)+IIf(Nz(DCount("VisitID","VisitQ","HealthPost='Rugarama H/C III' "),0)=0,0,1)+IIf(Nz(DCount("VisitID","VisitQ","HealthPost='Kayonza H/C III' "),0)=0,0,1)+IIf(Nz(DCount("VisitID","VisitQ","HealthPost='Ngoma H/C III' "),0)=0,0,1)+IIf(Nz(DCount("VisitID","VisitQ","HealthPost='Ntungamo H/C III' "),0)=0,0,1)+IIf(Nz(DCount("VisitID","VisitQ","HealthPost='Kitondo H/C III' "),0)=0,0,1)+IIf(Nz(DCount("VisitID","VisitQ","HealthPost='Nyakyera H/C III' "),0)=0,0,1)+IIf(Nz(DCount("VisitID","VisitQ","HealthPost='Rukoni H/C III' "),0)=0,0,1)+IIf(Nz(DCount("VisitID","VisitQ","HealthPost='Bwongyera H/C III' "),0)=0,0,1)+IIf(Nz(DCount("VisitID","VisitQ","HealthPost='Rweikiniro H/C III' "),0)=0,0,1)+IIf(Nz(DCount("VisitID","VisitQ","HealthPost='Butare H/C III' "),0)=0,0,1)+IIf(Nz(DCount("VisitID","VisitQ","HealthPost='Kagamba H/C III' "),0)=0,0,1)+IIf(Nz(DCount("VisitID","VisitQ","HealthPost='Itojo Hospital' "),0)=0,0,1)+IIf(Nz(DCount("VisitID","VisitQ","HealthPost='Kitwe H/C IV' "),0)=0,0,1)+IIf(Nz(DCount("VisitID","VisitQ","HealthPost='Rubaare H/C IV' "),0)=0,0,1)+IIf(Nz(DCount("VisitID","VisitQ","HealthPost='Rwashamaire H/C IV' "),0)=0,0,1)+IIf(Nz(DCount("VisitID","VisitQ","HealthPost='Rushooka H/C II' "),0)=0,0,1)+IIf(Nz(DCount("VisitID","VisitQ","HealthPost='Universal Care Clinic' "),0)=0,0,1)

I now realise that this is what I should have done;

After building VisitQ (parameter query above), I would have just built another query as follows;

SELECT DISTINCT HealthPost FROM VisitQ

Then I would save this query as HealthPostVisitedQ

In my ControlSource of the report, I would then just had one short formula as
=Nz(DCount("HealthPost","HealthPostVisitedQ"),0)

And that is it!

MICAH

---

Reply from Richard Rost:

Wow. So simple. So elegant. Good catch!