Percentage calculation

[CS_10]

Please ignore that last post.



I have since played around some more and got much further.

I think one last obstacle to overcome and then I can leave you in peace.

I've attached the front end to this post.

Please could you look at it and see how I would do the following.

rptPPTestProgress is the name of the report with all of these fields on. The control source is qryAggregateJoin.

Most of the fields on there seem to be working correctly, and performing the correct count.

I have 2 on there that I can't get to count though.

These are next to the labels:
Number Failed
Number Passed.

What I want here is the number of records in tblPatchApplication where the Result = Passed/Failed, but still within [Enter PP] criteria.

Please could you help me with hopefully this last bit?

Thanks again.

[June7]

None of this data has been filtered by PP. If you need to filter by PP then need to apply filter in each aggregate query.

qryApplicationAggregate

SELECT Count(tblPatchApplication.PA_ID) AS CountOfPA_ID, tblPatchApplication.Patch_No, Count(tblPatchApplication.Result) AS CountOfResult, Sum(IIf([Result]="Failed",1,0)) AS FailCount, Sum(IIf([Result]="Passed",1,0)) AS PassCount
FROM tblPatch INNER JOIN tblPatchApplication ON tblPatch.PatchNo = tblPatchApplication.Patch_No
WHERE (((tblPatch.PP_ID)=[Enter PP]))
GROUP BY tblPatchApplication.Patch_No;

Or summarize by PP_ID and Patch_No:

SELECT Count(tblPatchApplication.PA_ID) AS CountOfPA_ID, tblPatch.PP_ID, tblPatchApplication.Patch_No, Count(tblPatchApplication.Result) AS CountOfResult, Sum(IIf([Result]="Failed",1,0)) AS FailCount, Sum(IIf([Result]="Passed",1,0)) AS PassCount
FROM tblPatch INNER JOIN tblPatchApplication ON tblPatch.PatchNo = tblPatchApplication.Patch_No
GROUP BY tblPatch.PP_ID, tblPatchApplication.Patch_No;

Do the same compound grouping for Risk. Then join these two queries on tblPatch but the linking must be on both fields (compound key join). Now apply filter [Enter PP].

[CS_10]

Thank you so much, I think that has done it.

Really appreciate all the help you've given me!

[CS_10]

Just a quick query, that I didn't think was worth raising a new issue.

I have an object on my report (the main summary), and this is the control source:

=Sum([qryPPSummary]![CountOfRiskID])

If no risks have been entered at all, instead of returning a 0, it is blank.

How can I get it to return 0 instead of blank?

[June7]

Try Nz function.

=Nz(Sum([qryPPSummary]![CountOfRiskID]),0)

[CS_10]

Thanks very much for that. Seems to have done the trick.

I am using two of the fields that I have used this Nz function in to work out a percentage, now that returns #Num!

My expression is =[Text43]/[Text29]

Both of the text fields used above are in the same format as the control source you provided in the above post.

Anyway I can prevent #Num! appearing?

[June7]

Do you mean controls instead of fields?

That might be division by zero error.

Instead of returning zero if null, return 1 for the divisor.

Nz([fieldname],1)