Easy Custom Calculation?

[rjk1080]

Expr1: (300-(sum[total purchase]))

get error: there could be an operand without operator




What am I doing wrong here?

If I go this route:

sum(300-[total purchase])

the query comes out wrong. The table is a transaction table. A customer can have more than one transaction. Problem is, with the expression above, if a specific customer has two transactions, the expression will take 300x2=600 - total purchase. 3 transactions will use 900, and so on.

I feel like the expression at the top makes more sense were it not for the error message.

I know this is probably basic stuff. Would appreciate any help. Thanks.

[rjk1080]

I forgot to add that customers are credited $300 to begin with.

This query is supposed to track their remaining balances

[RuralGuy]

Use the Query Builder to create your agregrate query first without the special calculation. Pressing the Sigma character in the toolbar changes a select query to an agregrate query. Once you get the Sum() working then add your special calculation field.

[rjk1080]

The query is set up as an aggregate. The second field is "Total Purchase." In the total row, I selected sum. This works correctly. It sums a customers total purchases. The special calculation is what is not working. I am trying to get it to take 300 minus the total purchase amount the query comes up with in the second column.

[RuralGuy]

Try:
Expr1: 300-(sum([total purchase]))

[rjk1080]

It worked! Thank you very much RG.

[RuralGuy]

Excellent! Thanks for marking the thread as Solved as well.