Count based on query results?

[goldriverdancer]

Dear all,

I'm sure there's a really easy solution to this, but I can't seem to find it and am getting more confused after looking through the forum.

I'm trying to run a query that returns: [Pump Models], Sumof[Run time to Failure], Count[Run time to failure], and then perform a calculation involving the Sumof and Count fields.

Currently, I select "group by" in the totals row for [Pump Models] and "sum" for [Run time to failure]. This works fine, and shows me the total run time to failure for each pump.

Now, I want to find the number of times each pump model has failed, and tried to do this by selecting "count" in the totals row for [Run time to failure] in the next column. However, the query returns the number of Pump models instead of the number of failures for each pump.

I hope i'm clear enough; basically I'm trying to narrow down my count of failures based on Pump Model.

Thanks very much in advance for your help.


Stef

 

[Galaxiom]

Make another query which counts the failure records grouped by ModelID then join the two queries back together on the ModelID.

Can be combined into one query with subqueries if you like but you may find the individual queries useful in other tasks.