Automatically convert a count value to the word "YES"

[douggy]

I have a crosstab query that performs a count function. The output is then the count value as a number.

Is there a way for me to automatically replace the number with the word "YES" for when the count value equals 1.

Kind regards
Mark

Here is my sql so far:

TRANSFORM Count(tblOrganisation.Org_Key) AS CountOfOrg_Key
SELECT tblOrganisation.Company_Name, First(tblOrganisation.Org_Key) AS FirstOfOrg_Key
FROM tblService INNER JOIN (tblOrganisation INNER JOIN tblOrg_Service ON tblOrganisation.Org_Key = tblOrg_Service.FK_Org_Key) ON tblService.Service_Key = tblOrg_Service.FK_Service_Key
GROUP BY tblOrganisation.Company_Name
PIVOT tblService.Service;

 

[CosmoKramer]

See if this works:

TRANSFORM IIf(Count(tblOrganisation.Org_Key)=1,"Yes",Count(tblOrganisation.Org_Key)) AS CountOfOrg_Key

 

[douggy]

That's great. Just what I wanted!

Would you mind telling me how I would get it to default to &quot;NO&quot; if the count value is < 1.

Thanks a million

Mark

 

[CosmoKramer]

Doug,

Is the count either a 0 or 1?? If it is then:

TRANSFORM IIf(Count(tblOrganisation.Org_Key)=1,"Yes","No")) AS CountOfOrg_Key