Sum specific fields

[Reid]

Hello,

I'm trying to sum a field "lu_lot_qty" but removing duplicate "ITW" numbers. So in the list there are 2 occurrences of ITW 1369 and 1439. I don't want the sum to include duplicates of these. The sum needs to be by "lu_lot_no". For lot 00000000, the correct sum should be 7,215 (not 13,119).

[comment]

It looks like there should be another table where ITW and lu_lot_qty would be unique?

[Reid]

Yes there is another table where ITW is unique. The qty is not, however.

[comment]

In your example, whenever ITW repeats, so does lu_lot_qty. This would suggest that lu_lot_qty too belongs in the other table. I am only guessing here, because it's all Chinese to me. However, I am pretty sure the request is the result of data not being normalized properly.

[Reid]

You are correct. The tables are linked by ITW. The part_no, lot_no, and lot_qty are lookups from the other table. I am using this table because I need a report by defects so I am using the most specific table possible while copying less specific data from other tables.

[comment]

One way to get the correct result would be to divide the looked up (or the related) quantity by the count of the records in the category (ITW, I think). Then, when you summarize the calc, it will even out.