Create a summary field of total field 1.
If you isolate only the records you want to total, in your example case records A and C, then the summary field will have the total.
For sake of discussion lets pretend records A and C have a primary key field with values 34 and 39
Enter find mode.
set field(the_table::primary key field; 34)
new record request
set field(the_table::primary key field; 39)
Perform find and the summary field will have the total..
You could also use ExecuteSQL( "SELECT SUM(field1) FROM the_table WHERE primary_key_field=? OR WHERE primary_key_field=?" ; "" ; ""; 34; 39)
I'm sure there are other methods but these were the first two that came to mind.
Alot depends on what you want to do with the sum.
Ok, I will give that a try, thanks
coherentkris ideas are good, but if you need to sum "particular records" this might be a database design issue instead. Consider creating a 1:M relationship: create a child table whose records you would logically sum.
An example would be something like CUSTOMERS->INVOICES->INVOICE_ITEMS
So rather than trying to have INVOICE_ITEM data (and amounts) in the CUSTOMERS table, you would use DB relational design and have those amounts linked (and then easily "SUM-able" for a given CUSTOMER).
If you requirement is more "ad-hoc", then a summary field or SQL as already suggested is a good way to go.
There are more people in the database, but only want the total from these...or certain
And what did you do to organize the data into columns like this? (there's more than one way).
It would appear that the common info that selects the data to be summed is that they all have the same date?
If so, then you need to either match (via relationship), filter (filtered portal), Find (scripted find) or Select (SQL query) by that common date value in order to correctly reference the group of records from which you want to total the records.
The Data is in the Daily Database, then I duplicated daily, 10 times, daily1, daily2..... Then I set the relationship, to equal the year = year and the member id = a number field 1 (this is were I put the id for the record it want. there are 10 number files...
Display with 10 different portal daily 1 - daily 10
shows 31 records, starting with 1 for jan, 28 or 29 records for feb starting with 32.....
Frustrating when you can see the data on the screen but just can't get it to add up, LOL
If you have a relationship that matches to the records that you want to sum, average or compute some other aggregate value, there's an aggregate function that you can use such as:
Sum ( RelatedTable:: Field ) to sum all related records. So if you can set up an unfiltered portal that lists all the records that you want to sum, the above expression, if referring to the same table occurrence as the portal, will compute a sum of the specified field across those records.
If you define a summary field to compute the same aggregate value, but define it in the related table, it will produce the same value, plus, if you place it in a filtered portal, it will total only the related records that pass the filter.
And then you also have the other two options: A scripted find (find the records, set a variable or field in a related parent record to the value of the summary field described earlier.) or A Select Query in ExecuteSQL which can also use the SQL version of the same aggregate functions such as sum.
They are also called "Cross Tab" reports.
short for "cross tabulation" (aka "contingency table"). PivotTable is TM by MicroSoft!