SUM [qty] if [inspectionresult]=1

[Mustang86]

good day.

I'm generating a report from a table. The table has..
employee (employee name)
inspectionResult (1 if pass, 2 if fail)
qty (number of items)
and a bunch of other product data that shows in my report.

The data from the table is filtered on [inspectionResult]=2 using the report properties

I have grouping in the report by [employee] (employee header) where I have the employee's name showing

in the detail section, I have details of the records if [inspectionResult]=2. (only need details if the product was bad)

I have a subreport in the employee footer. Child and Master fields of [employee] are linked/indicated in the properties.

There are 3 pieces of data in the subreport.
1) SUM (the QTY of all the records for that employee if the [inspectionResult]=1) for that record
2) SUM (the QTY of all records for that employee if the [inspectionResult]=2)for that record
3) total SUM([qty]).

the total SUM([qty]} works and gives me the number of items total for only that employee.

using DSUM returns the sum of all the (accepted) records and (rejected) (as expected due to the properties of DSUM)

I can =Sum([inspectionResult]=2)and get the number of RECORDS, not the sum of the [qty].......
but for the life of me I can't figure out how to =SUM([qty])when[inspectionResult]=1).

Any assistance would be appreciated...

 

[Remou]

Perhaps
Sum(IIf([InspectionResult=1,[qty],0))

 

[Mustang86]

Yes! Works well!

and with that the % can be found.
=(Sum(IIf([InspectionResult]=1,[qty],0))/(Sum([qty])))*100

I thought I had tried that but I probably had my format messed up.

Thank you much!

 

[danvlas]

Another option:

Sum(qty * (InspectionResult Mod 2))

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html