[Tutorial] Calculations in databases (quick primer)

[Villeroy]

Hoizontal calculation in rows (Volume=Height*Width*Lenght): calculate_form.odb

Edit: This file has been replaced and is now a link at the bottom of this posting

Vertical calculation in columns(aggregation sum,count,average,...): sumproduct.odb

Vertical aggregations can be used together with grouping in order to get the function results for each combinaition of groups.
Example: In the second example file you see various function results for the selected combination of "category 1" and "category 2".
Refer to any SQL documentation, such as http://www.1keydata.com/sql/sql.html (chapters about SUM, GROUP BY, HAVING
Hint: Calc's data pilot can do very similar aggregations from list ranges as well as from database tables with a flexible cross-table layout.
http://wiki.services.openoffice.org/wik ... /DataPilot

The supported set of features depends on the type of database connection as indicated in the status bar of your database window.
File based databases (dBase, spreadsheets, csv) support horizontal calculation only: /specifications of file based functions

True databases allow Base to apply the whole arsenal of Base features plus any additional functions supported by the connected database engine. Base's built-in database engine is fully documented at http://hsqldb.org/doc/guide/ch09.html
Hint: Try direct SQL and read carefully the error messages. Quite often the database engine understands your SQL better than Base does. Quite often the error messages are more comprehensible.
My [Tutorial] Structured Query Language in Base explains the different ways to use SQL with Base queries, forms and reports.

[szd6k8]

This is so frustrating. I don't understand where the calculation goes. I see where they put sum, min, max, avg, etc., but they don't seem to allow select distinct. I wish they had some kind of tutorial for OoBase for dummies............LOL. The calculate_form.odb is a link that is no longer available. I've been trying to put the calculation in the new column of the query. Still getting error. Any more suggestions would be greatly appreciated.

Edit: Calculate_form.odb has been replaced and is now a link at the bottom of the previous posting.

[idahouser]

Hoizontal calculation in rows (Volume=Height*Width*Lenght): calculate_form.odb

Edit: This file has been replaced and is now a link at the bottom of this posting

This file is read-only and therefore right click - Edit is not available. I cannot see what's behind all the form fields.

[eremmel]

Every AOO file loaded from internet is read-only as protection. Start with a Menu -> File -> Save as.. to get an file that can be edited and inspected.

[idahouser]

Every AOO file loaded from internet is read-only as protection. Start with a Menu -> File -> Save as.. to get an file that can be edited and inspected.

OK, what am I not doing right? I saved the odb file with a new name so now when I right click on the Form "Cuboids" I can go to edit. However, the form is still read only so I still can't get to background info. So I saved it directly as an odt file. I can open that file in Writer by double clicking and now I can edit it. But the instructions say to open up the Form Navigator and check the properties. There is no form entry in the hierarchy and none of the entries show properties choice when I right click on them.

I hate to say it but so far the file above has been a tutorial failure for learning about calculated fields.

[eremmel]

Hi Ida houser, You are right I do not succeed either.

[Villeroy]

I have no idea how that "Cuboids" form became read-only in edit mode. I attached another odb with 3 forms calculating volume = width * height * depth