I believe you must use the Sum function from another context (table or TO). Otherwise the field using Sum is one field of one record and will, of course, only "sum" itself.
Sum adds up the list of values located inside the parenthesis. Sum ( Expenses::amount ) only gets one value to sum up and thus produces the same result as if you just used Amount in its place.
A summary field, as you've found will add up all the values in yoru found count and can even compute a running total if that should be useful.
In order for Sum ( ) to work for you, it'd need to refer to all the records being summed. That requires a relationship to another table or table occurrence. You can use that to relate a table to itself so that you can compute the sum, but the resulting total is an unstored calculation and this also cannot be used in many relationships. (Unstored fields can only be used on the Parent side of a relationship.)
Say you want sum to compute the sum of all the records in your table.
Define this relationship:
Expenses::anyfield X Expenses2::Anyfield
Create Expenses2 by selecting expenses, then clicking the button with two green plus signs. Now Sum ( Expenses2::Amount ) will compute the total for all records in your table. If you only want sum to compute a total for a smaller subset of the total records, you'll need to modify the relationship to link only to those records.
@Rick Whitelaw: Makes sense to me now!
@PhilModJunk: I did exactly what you said, however it is not returning anything now. Is it because my field type 'number'? does it needs to be text?
Amount should be of type number, but even a text field containing a number should sum correctly.
Your total field should be of type calculation set to return number. Don't try this with a number field and an auto-entered calculation.
On the tables tab, do you see one table: Expenses with two occurrences: Expenses, Expenses2?
Yes I do.
Here's a demo file that does work. Compare it to yours and see if you can spot what's different. (Click blue down load button located below the file description text.)
Downloading now. Going to report with my findings. Thanks a lot Phil!
Opened the file and cValueSum is showing 6 on every row. Is that normal?
Am giving up, spent the whole day and night figuring how this sum works and am still stuck... Thanks a lot for you help but I guess am off to easier alternatives.