## [Tutorial] Calculations in databases (quick primer)

Forum rules
No question in this forum please
For any question related to a topic, create a new thread in the relevant section.

### [Tutorial] Calculations in databases (quick primer)

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.
Attachments
calculate_form(3).odb
3 Forms with calculated field and a micro-tutorial on form editing.
1. Form + calculated subform
2. Single form with calculated field
3. Single form as table grid
Last edited by Villeroy on Sat Apr 06, 2019 3:51 pm, edited 2 times in total.

Villeroy
Volunteer

Posts: 27096
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: [Tutorial] Calculations in databases (quick primer)

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.
OenOffice 4.1.1 Windows 8.1
szd6k8

Posts: 29
Joined: Wed Mar 25, 2015 7:24 pm

### Re: [Tutorial] Calculations in databases (quick primer)

Villeroy wrote: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.
OpenOffice ver 4.1.6 Windows 10 Pro
idahouser

Posts: 2
Joined: Mon Mar 25, 2019 7:15 am

### Re: [Tutorial] Calculations in databases (quick primer)

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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
eremmel

Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am

### Re: [Tutorial] Calculations in databases (quick primer)

eremmel wrote: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.
OpenOffice ver 4.1.6 Windows 10 Pro
idahouser

Posts: 2
Joined: Mon Mar 25, 2019 7:15 am

### Re: [Tutorial] Calculations in databases (quick primer)

Hi Ida houser, You are right I do not succeed either.
It's Microsoft marketing that tells you computers are qualified for non-technicians
eremmel

Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am

### Re: [Tutorial] Calculations in databases (quick primer)

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x

Villeroy
Volunteer

Posts: 27096
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Who is online

Users browsing this forum: No registered users and 1 guest