[Tutorial] Calculations in databases (quick primer)

Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Post Reply
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[Tutorial] Calculations in databases (quick primer)

Post by 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.
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
(31.63 KiB) Downloaded 1209 times
Last edited by Villeroy on Sat Apr 06, 2019 3:51 pm, edited 2 times in total.
szd6k8
Posts: 29
Joined: Wed Mar 25, 2015 7:24 pm

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

Post by 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. 
OenOffice 4.1.1 Windows 8.1
idahouser
Posts: 2
Joined: Mon Mar 25, 2019 7:15 am

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

Post by idahouser »

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
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

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

Post by 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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
idahouser
Posts: 2
Joined: Mon Mar 25, 2019 7:15 am

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

Post by idahouser »

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
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

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

Post by eremmel »

Hi Ida houser, You are right I do not succeed either.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

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