Calculate the sum in a form

[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...

[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

[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:

[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.

[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;

[kev1n]

Can you show the generated SQL? (Options Menu -> Form Info) and also the form properties where I can see your browse columns

[cuadradoja]

Hi Kevin, here is the information you asked me for

[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

[cuadradoja]

Dude, you´re a genius!! The code works perfectly... thank you very much!!!