Simple addition in Base

[bobwinkel]

I have a database table with 3 columns, cost, shipping and total. Is it possible to get Base to simply add "cost + shipping = total" and (here's the hard part) populate the total field in the table? Using Open Office 4.1.1 with MS Windows 8.1 on a laptop. Thank you

[Villeroy]

Most trivial SQL:

Code: Select all

SELECT *, "cost" + "shipping" AS "Amount" FROM "Your Table"

[Sliderule]

I would like to say Villeroy's example above is very clear and accurate in showing an example of using arithmetic from a database's ( Table or View ) columns. Assuming the end user is using the HSQL Embedded database, OR, HSQL Version 2.X . . . since HSQL ( also H2 ) follows the SQL Standard ( and, in my opinion this is very positive ) . . . the user would have to include the Table Name ( in this case "Your Table" ) in front of the * after the Select ( since one or more additional columns are a part of the result set ) .

Code: Select all

SELECT 
   "Your Table".*, 
   "cost" + "shipping" AS "Amount" 
FROM "Your Table"

Just an additional comment . . . the end user should NOT store in the database a "total" column. Put another way, that calculation should always be down by a Query or a View . . . since, a value ( "cost" or "shipping" ) may change, and, if it changes, the Query or View will recalculate that value.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[bobwinkel]

Thank you for your quick response. In your answer above, you indicate the Total should not be stored in in a database column as a value may change. In my case this example is only part of a 15 field record and there are over 500 records in the database and the values change with every record. I have a query "Total_Qry" which calculates the "Total" for each record. I need to be able to store this calculated field "Total" in a location where it can be recovered for use in reports such as Invoices. Where would you suggest this Total field be stored and how do I get it in and out?

[Sliderule]

A database requires a sound foundation to work correctly. I mean, like building a large building, if the foundation is not sound and solid, everything built on top of it ( the foundation ) is subject to failure.

Per your description . . . I have no idea, since, it makes no sense to me. As I indicated, you can use a Query to calculate the desired result, OR, a View.

I have a query "Total_Qry" which calculates the "Total" for each record. I need to be able to store this calculated field "Total" in a location where it can be recovered for use in reports such as Invoices.

I do not know what recovered means, and, a report can use either one:

1. Table
2. Query
3. View

to pass data to the report generator. Therefore, as a part of the Query or View, you perform the necessary calculation.

But, any / all databases need a sound normalized foundation so the tools can manage the data.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[UnklDonald418]

Perhaps the invoicing example found at
viewtopic.php?f=100&t=56006
might be helpful. The example displays the results of a number of calculations using queries.
While the example doesn't include a report to print the invoice, it wouldn't be too difficult to write one using those same queries.
The possibility of printing an invoice with the incorrect total is eliminated by recalculating it, but not by using a valued stored by a previous calculation.