I want to know how to take the result of a calculation ,which is a alias of a summed field, and save it to a field in a table?
@Panchopar: Are you summing several field values within each row/record. Or aggregating over many records (and storing in a different table)?
See additional question in below COMMENTS.
Hello,
This seems to be a continuation of this question:
how to use the total of a sum function as part of another calulation in libreoffice base
So starting with the last question, it was noted to use the alias in a select you could do this with an embedded select.
Simple calculation with alias:
Trying to use that alias at the same level (not embedded) results as an error - not found:
If the alias is embedded in another select it can be used in the upper level:
Now your current question is very different from what has just been discussed. An update is done using the value. This can be a fixed value, a value of another field or the value of a calculation. In HSQLDB embedded it matters not if the calculation has an alias or not (other databases may be different). The update is looking for a value:
Whether the alias P1 is present or not, the result is the same.
It also occurs to me you may be looking to do this on a regular basis with every record. This will take more thought process on your part and will require you to use either macros (must be specific to your needs) or possibly triggers (see you DB documentation for this).
MySQL also has calculated fields but this may not fit your needs (again see documentation).
If this is the case, you will need to consider all possible situations and not just at entry of a new record.
I am doing this for every new record entered. (Trying to do it), I am using mysql as my backend and Libre office base as my front end. In the old days dBase lll did this easy. Setting up variables was also eazy ,doing that in LO Base ,not so eazy. Thanks for all the help. I will study this.
My question (original) seems unanswered - i.e. does the calculation ONLY involve other values from the current table record - maybe a TRIGGER (as Ratslinger suggests) would suffice in this case. More info about your database schema (structure) would help (at least me). Maybe you might be able to give us a step-by-step description of WHAT you actually do ‘by hand’ where you say… I am doing this for every new record entered. (Trying to do it)… That does sound like to me that you ONLY doing the manual update for NEW records?
Another question: do you really need to store this information in your table? It seems like a lot of extra work whichever way you look at it. Why couldn’t this info just be ‘calculated’ by a suitable SQL query whenever you need it?