Good Afternoon.

I was wondering if anyone could possible assist me.

I wondered how to total field values if the field was a certain value.  Like if I had a list of items a1 a1 b1 c1 a1 how can you only add the a value then the b value etc etc.

Hope this is quite clear to understand.

Thank you very much for any help or advice.

 

5 minutes ago, Faldo said:

Hope this is quite clear to understand.

I am afraid not.

 

Sorry

Il try again.  😤  Im trying to sum individual values of individual fields.  Like sum them if they equal the criteria.  

If I had a list of items and prices with items the same name but different prices sum them up if the were say red.  And another sum if they were Blue and so on.

Phew hard going.   Is that any better. 😣

2 hours ago, Faldo said:

If I had a list of items and prices

Such list should be stored as individual records, not individual fields.Then you can simply find the records you want to summarize, sort them by the Name field and show them in a list layout with a sub-summary by Name part. A summary field defined as Total of Price, and placed in the sub-summary part, will show the total of each group. If you like, you can delete the body part so that only sub-totals are shown.

Alternatively, you could have a parent table of Names where each name is a unique record. Then you can summarize the matching records in the Items table using either a summary field (defined in the Items table) or a calculation field using the Sum() function (defined in the Names table). Keep in mind that this will include all related records, without the possibility of performing a find.

Finally, you also have the option of using the ExecuteSQL() function, with a query like:

SELECT Name, SUM (Price) 
FROM Items 
GROUP BY Name

 

2 hours ago, Faldo said:

Phew hard going.

Your question has 63 words. My answer above has 182. It should be the other way around.

 

3 minutes ago, comment said:

Such list should be stored as individual records, not individual fields.Then you can simply find the records you want to summarize, sort them by the Name field and show them in a list layout with a sub-summary by Name part. A summary field defined as Total of Price, and placed in the sub-summary part, will show the total of each group. If you like, you can delete the body part so that only sub-totals are shown.

Alternatively, you could have a parent table of Names where each name is a unique record. Then you can summarize the matching records in the Items table using either a summary field (defined in the Items table) or a calculation field using the Sum() function (defined in the Names table). Keep in mind that this will include all related records, without the possibility of performing a find.

Finally, you also have the option of using the ExecuteSQL() function, with a query like:

SELECT Name, SUM (Price) 
FROM Items 
GROUP BY Name

 

Your question has 63 words. My answer above has 182. It should be the other way around.

 

Hi thanks a million will have a go.  You have more words as you have more wisdom and knowledge.  Thanks again. 🙄😁

Hi sorry just one more thing if you can.  How would you go about defining if the Price was a credit or a debit?  Would you use 2 seperate fields one dredit and one debit.  I had a dropdown which you can choose money in or money out which didnt seem to work.  Thanks for your help and patience.

 

It depends on how you want to work with it and/or what you want eventually to do with it. The simplest way is to enter debit amounts as negative numbers. Ledger-type solutions will have separate fields for credit and debit - but then a calculation field will return credit as positive and debit as negative, so that a single summary field can be used for the balance.

2 hours ago, Faldo said:

I had a dropdown which you can choose money in or money out which didnt seem to work.

That could work, too - provided you also had a calculation field like the one descried above, that would return the amount as negative if you selected "money out".

 

Edited April 7, 20205 yr by comment

Brilliant thats great thanks.