[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)

Postby Villeroy » Sat Jan 09, 2010 3:26 pm

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 20 times
Last edited by Villeroy on Sat Apr 06, 2019 3:51 pm, edited 2 times in total.
User avatar
Villeroy
Volunteer
 
Posts: 26632
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby szd6k8 » Wed May 27, 2015 3:06 pm

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)

Postby idahouser » Mon Mar 25, 2019 7:21 am

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)

Postby eremmel » Mon Mar 25, 2019 7:05 pm

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.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1033
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

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

Postby idahouser » Mon Mar 25, 2019 9:09 pm

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)

Postby eremmel » Mon Mar 25, 2019 11:11 pm

Hi Ida houser, You are right I do not succeed either.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1033
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

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

Postby Villeroy » Sat Apr 06, 2019 3:55 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26632
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Base

Who is online

Users browsing this forum: No registered users and 1 guest