Page 1 of 1
Calculate the sum in a form
Posted: Tue May 16, 2023 2:32 am
by cuadradoja
Hi, I have a table that has a field called 'item' and another field called 'quantity'; of course, the names of the articles may or may not be repeated. I'd like to know, how I can calculate the sum of these items with their respective amounts in the preview of the browser form and in the preview of the edit form. I've tried in many ways, but I still have not succeeded in doing it...
Re: Calculate the sum in a form
Posted: Tue May 16, 2023 4:04 am
by kev1n
Hi,
To calculate the sum (or product ?) of the these columns, add a calculation to the Display column
browse.png
As for the Edit form, add a calculated field (object of type Calc):
calc.png
Re: Calculate the sum in a form
Posted: Mon May 22, 2023 2:28 am
by cuadradoja
Hi Kevin, nice to greet you again... Thanks for your previous explanation, the formula works very well for me when the expression is a multiplication between two fields with numerical values, but I still don't know how to add the records with the same names and I display the final result for each name with their respective values. I show you a snippet of what I want to do is:
Re: Calculate the sum in a form
Posted: Mon May 22, 2023 7:49 am
by kev1n
In a browse form, add this SQL:
Code: Select all
SELECT * FROM (
SELECT NULL AS your_table_name _id, item, SUM(quantity) as quantity FROM your_table_name
GROUP BY NULL, item
) T;
Replace your_table_name with the actual name of your table in the database.
and replace your_table_name_id with the actual name of the primary key.
This query uses the SUM function to calculate the total quantity for each item, grouping the results by the item column.
Note that "NULL AS your_table_name _id" is required for the SQL parser to work.
Re: Calculate the sum in a form
Posted: Mon May 29, 2023 3:41 pm
by cuadradoja
Hi Kevin... I did everything you told me, following your instructions to the letter, but I still don't have good results. When the code you suggested is executed, the browse form goes completely blank. On the other hand, if I put part of the SQL code as I show you below, it only groups the names of the items, but it does not perform the corresponding sum of them; that is, it groups the items and only shows the first quantity, without adding the other common items.
I'm kind of baffled because I don't know what to do to make that work!!
Code: Select all
SELECT NULL AS stock_id, item, SUM(quantity) as quantity FROM stock GROUP BY NULL, item;
Re: Calculate the sum in a form
Posted: Mon May 29, 2023 4:23 pm
by kev1n
Can you show the generated SQL? (Options Menu -> Form Info) and also the form properties where I can see your browse columns
Re: Calculate the sum in a form
Posted: Tue Jun 06, 2023 12:30 pm
by cuadradoja
Hi Kevin, here is the information you asked me for
Re: Calculate the sum in a form
Posted: Tue Jun 06, 2023 1:07 pm
by kev1n
The query you provided is almost correct. However, it is missing an alias for the subquery. Here's the corrected version:
Code: Select all
SELECT * FROM (
SELECT NULL AS stock_id, item, SUM(quantity) as quantity FROM stock
GROUP BY item
) T
Re: Calculate the sum in a form
Posted: Wed Jun 07, 2023 6:20 pm
by cuadradoja
Dude, you´re a genius!! The code works perfectly... thank you very much!!!