AnsweredAssumed Answered

Sum of Prices using Case Statement

Question asked by kallain on Jul 5, 2012
Latest reply on Jul 6, 2012 by philmodjunk


Sum of Prices using Case Statement



I am trying to use a calculation field to get the sum of a subset of records using a case statement.

I have a table called 'Bids' with the fields 'Bid Price' and 'Mark'. I have a second table called 'Shots' with a field called 'Shot Cost'

On the 'Shots' layout, I have a portal to the 'Bids' table containing the fields mentioned above. I'm trying to get the 'shot cost' field to display the sum of 'bid price', but ONLY when the 'mark' field contains the word "Mark". 

The 'shot cost' field is currently set up as a calculation with the following:

Case ( Bids::Mark="Mark" ; Sum ( Bids::Bid Price ) )

But this results in a sum of ALL records instead of only those that are 'marked'

For instance:

Record 1 = bid price $10, MARK

Record 2 = bid price $20 (not marked)

Record 3 = bid price $30 (not marked)

Result of Shot Cost = $60 (I only want it to count record 1, it should be $10)


Any assistance would be greatly appreciated.

Thank you,