Count

[Ivor Williams]

I've created a query called qryJobs. Following is the SQL view of this
query:

SELECT tblMaterial.POID, tblMaterial.ProjNo, Count(tblMaterial.ProjNo) AS
Jobs
FROM tblMaterial
GROUP BY tblMaterial.POID, tblMaterial.ProjNo
HAVING (((tblMaterial.POID)=[FormsPO]![txtPOID]));

I've created a second query called qryJobCount which uses as a record source
the qryJobs query. Following is the SQL view of the qryJobCount query:

SELECT qryJobs.POID, qryJobs.ProjNo, Count(qryJobs.Jobs) AS CountOfJobs,
IIf([Jobs]=1,[ProjNo],"M") AS Suffix
FROM qryJobs
GROUP BY qryJobs.POID, qryJobs.ProjNo, IIf([Jobs]=1,[ProjNo],"M");

I've created a report based on the qryJobCount query. On the report is a
text box. If there is only one record in the qryJobCount query, I want the
value in the qryJobs.ProjNo to be displayed in the text box. If there is
more than one record in the qryJobCount query, I want the text box to
display "M".

Where I run into a problem is when there is only one record in the
tblMaterial table with the ProjNo field. In that situation, the qryJobCount
query returns more than one record. All I want is one record which will
display either a ProjNo value or "M".

How can I resolve this problem?

Ivor

 

[[MVP] S.Clark]

=IIF(DCount("*", "qryJobs")=1, DLookup("ProjNo","qryJobs"), "M")