counting record fields and totals

[cporter]

I have a query where I need to count the number of times a record field
(ontime) is 1 and the total number of records in the query. What is the
best way to do this?

SELECT tblPMTemp.MACHID, tblPMTemp.PROCNO, tblPMTemp.FREQ,
tblPMTemp.DLP, IIf([DLP]+[FREQ]+14<=Date(),1,0) AS Ontime
FROM tblPMTemp
WHERE (("KLA"=Left([machid],3)));

 

[Jerry Whittle]

SELECT tblPMTemp.MACHID,
tblPMTemp.PROCNO,
tblPMTemp.FREQ,
tblPMTemp.DLP,
Sum(IIf([DLP]+[FREQ]+14<=Date(), 1, 0)) AS SumOntime,
Count(tblPMTemp.DLP) AS CountOntime
FROM tblPMTemp
Group By tblPMTemp.MACHID,
tblPMTemp.PROCNO,
tblPMTemp.FREQ,
tblPMTemp.DLP
HAVING (("KLA"=Left([tblPMTemp..machid], 3)));

 

[John Spencer]

SELECT MACHID
, Count(*) as RecordsReturnedForMachID
, SUM( IIf([DLP]+[FREQ]+14<=Date(),1,0)) AS Ontime
FROM tblPMTemp
WHERE MachID Like "KLA*"
GROUP BY MachID

That give you totals by MachId. If you want, totals for all then remove
MachId from the Select clause and delete the Group by clause.

 

[cporter]

John, Your suggestion worked great. Thanks.