Calculations within queries

[Novice]

Hi,
I have built a query to count a number of pass/fail occurences against a user. This query is running but is only counting users who have both pass and fail occurences, it is not tracking those that only have pass only or fail only.
I do not have any criteria in the main query in the pass/fail select field. I thought this would capture everything. I can count all passes and all fails in two seperate queries but cannot capture this information in one query.

I tried a crosstab query but it won't allow enough fields to be entered.
Any ideas?
Thanks
Novice

 

[Philem]

Hullo. Please post your SQL, and we'll take a look. Shooting from the hip, my guess is to try a form of this equation:

Iif(Not IsNull([Result]),Iif([Result]="Pass" OR [Result]="pass", "Passed","Failed"),"No Value")

That will check if there is a value in Results, then test possible entries in that field.

HTH

P

 

[Novice]

Hi,
The SQL is as follows.
Thks
Novice

SELECT q_Pass_By_Qtr.Inspection_Area, T_Builders.[Builders Name], [Units_Passed]/([Units_Passed]+[Units_Failed]) AS Pass_Rate, q_Pass_By_Qtr.Units_Passed, q_Fail_By_Qtr.Units_Failed, [Units_Passed]+[Units_Failed] AS Total, q_Pass_By_Qtr.Quarter, q_Pass_By_Qtr.Year
FROM (T_Builders LEFT JOIN q_Pass_By_Qtr ON T_Builders.[Builders Name] = q_Pass_By_Qtr.Builder) LEFT JOIN q_Fail_By_Qtr ON T_Builders.[Builders Name] = q_Fail_By_Qtr.Builder
GROUP BY q_Pass_By_Qtr.Inspection_Area, T_Builders.[Builders Name], [Units_Passed]/([Units_Passed]+[Units_Failed]), q_Pass_By_Qtr.Units_Passed, q_Fail_By_Qtr.Units_Failed, [Units_Passed]+[Units_Failed], q_Pass_By_Qtr.Quarter, q_Pass_By_Qtr.Year
HAVING ((([Units_Passed]+[Units_Failed])>0))
ORDER BY [Units_Passed]/([Units_Passed]+[Units_Failed]) DESC , q_Pass_By_Qtr.Units_Passed, q_Fail_By_Qtr.Units_Failed DESC , [Units_Passed]+[Units_Failed] DESC;