Query for Pass/Fail/Totals

[ashleymac]

My team tests items from several teams for functionality from various teams in my company. We rate them as Pass or Fail after testing is complete. What I'm needing to do is hopefully create a query that will count how many of each Pass and Fail and then a total of Pass+Fail for a total amount of items tested each team has for the month.

I have the attached query set up to pull from our record keeping table to pull all results from a specified time frame. I attempted to make a query based off of qryAllTeamResults to do what I'm trying to accomplish but I can't seem to get it to work. So at this point I have 3 different queries like qryTeamPassCount which I've attached - where the criteria for the Result-Where column is changed to either: "Pass"/ "Fail" /"Pass" or "Fail" This option doesn't work because when I go to create the Report it will not allow me to report on all 3 queries in a single report.

Any help will be greatly appreciated!

 

[Pat Hartman]

Remove the criteria from the query.

Add sorting/grouping on Results to the report.

Add totals in the group footer and the report footer.

 

[plog]

I'm needing to do is hopefully create a query that will count how many of each Pass and Fail and then a total of Pass+Fail for a total amount of items tested each team has for the month.

You need one query:

SELECT TeamName, SUM(IIF(Result="Pass", 1, 0) AS PassTotal, SUM(IIF(Result="Fail", 1, 0) AS FailTotal, COUNT(Result) AS ResultTotal
FROM YourTableNameHere
GROUP BY TeamName

Then you build a report on it. In the detail section goes all the fields above. Then you should add a ReportHeader/Footer and in the footer you put these 3 controls:

=SUM[PassTotal])
=SUM[FailTotal])
=SUM[ResultTotal])

Those 3 will add up all the data above it in the report.

 

[ashleymac]

You need one query:

SELECT TeamName, SUM(IIF(Result="Pass", 1, 0) AS PassTotal, SUM(IIF(Result="Fail", 1, 0) AS FailTotal, COUNT(Result) AS ResultTotal
FROM YourTableNameHere
GROUP BY TeamName

Then you build a report on it. In the detail section goes all the fields above. Then you should add a ReportHeader/Footer and in the footer you put these 3 controls:

=SUM[PassTotal])
=SUM[FailTotal])
=SUM[ResultTotal])

Those 3 will add up all the data above it in the report.

Thanks for your help however I'm getting the attached Syntax error. Did I type something in wrong?

 

[plog]

Yes, I missed a closing parenthesises on the SUMs. Add a closing parentheseis before each " AS"

 

[ashleymac]

Yes, I missed a closing parenthesises on the SUMs. Add a closing parentheseis before each " AS"

Thank you SO much! That's exactly what I needed.