[Solved] Aggregate functions unavailable in Base

Discuss the database features

[Solved] Aggregate functions unavailable in Base

Postby fow3 » Wed Jan 02, 2013 9:11 pm

I am using Base for the first time and the aggregate functions, Count, Sum, etc., do not appear in the drop-down menu in the query form.
This is happening on a Windows 7 Professional machine, and on another 64-bit PC running Windows Vista. Both had Java updated, with no results.
Is there another driver or special install lacking?
Sorry if this is basic but I don't see this issue addressed in existing threads.
Last edited by Hagar Delest on Fri Jan 25, 2013 6:36 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice.org 3.2
Windows Vista Home Premium Service Pack 2
fow3
 
Posts: 3
Joined: Sun Nov 18, 2012 4:04 am

Re: Aggregate functions unavailable in Base

Postby RPG » Wed Jan 02, 2013 10:10 pm

Hello

fow3 wrote:I am using Base for the first time and the aggregate functions, Count, Sum, etc., do not appear in the drop-down menu in the query form
This can be true when your database is based on a spreadsheet. See left down your window what the kind of database engine you use.

Romke
LibreOffice 6.1.3.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2152
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Aggregate functions unavailable in Base

Postby F3K Total » Wed Jan 02, 2013 10:10 pm

Do you use Java 6 32-bit? That's neccessary if you use Base as "delivered" with internal HSQL Database.
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 7 OOo, AOO, LOLinux Mint OOo, AOO, LO
F3K Total
Volunteer
 
Posts: 898
Joined: Fri Dec 16, 2011 8:20 pm

Re: Aggregate functions unavailable in Base

Postby fow3 » Fri Jan 04, 2013 7:41 pm

Thank you - the original source of the data was a csv file that was pulled into an Excel spreadsheet. Does anyone know if there is some conversion necessary that would allow the functions to appear in the query form? The table looks fine in Base table view .
OpenOffice.org 3.2
Windows Vista Home Premium Service Pack 2
fow3
 
Posts: 3
Joined: Sun Nov 18, 2012 4:04 am

Re: Aggregate functions unavailable in Base

Postby RPG » Fri Jan 04, 2013 10:52 pm

Hello

Your question is not clear to me

http://www.google.nl/#hl=nl&tbo=d&outpu ... 53&bih=470

Maybe the link can help you to find information to make more clear your question.

An other idea is feed your data to a pivot table.

Romke
LibreOffice 6.1.3.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2152
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Aggregate functions unavailable in Base

Postby Villeroy » Sat Jan 05, 2013 2:01 am

The availlable set of functions depends on the backend database and its driver.
This is the minimum set of functions available for file based database types: http://www.openoffice.org/dba/specifica ... tions.html COUNT(*) is the only aggregate for these pseudo databases.
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: 26129
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Aggregate functions unavailable in Base

Postby DACM » Mon Jan 07, 2013 4:49 am

fow3 wrote:Does anyone know if there is some conversion necessary that would allow the functions to appear in the query form?

If your data is consistent, and the first spreadsheet row defines the field names, then you can convert your flat-file database (spreadsheet-based tabular data) into an SQL database where you'll gain all the features and functions of the chosen SQL engine.

The quickest way to test this is to:
1. "Create a new database" using the Base wizard.
2. Open your flat-file database side-by-side with the new HSQL database (which ~adheres to this SQL grammar) in two-separate instances of Base.
3. Click and drag your flat-file table onto the Tables icon in the new database window.
4. Follow the popup wizard selecting 'Definitions and Data' and select "Create primary key" as applicable.
5. Select all applicable fields and "Create..."

    If this conversion process fails, then you may need to select a few fields at a time until you identify the fields with inconsistent data or those with incompatible formats (typically date data-types). Or you may need to try a two-step process of transferring the "Definitions" and then "Append data" separately. In that case, it may be easier to create an 'AutoValue' primary key manually after the table and data are in-place in the new SQL database. A primary key is necessary in Base to edit a table. See also: these links.
6. Place the new .odb file in a DropBox or Google Drive folder so a new version is created and saved in the cloud each time you close the file. This will allow you to retrieve a recent version when Base corrupts the file, since these single-file 'embedded databases' are unstable/unreliable in Base.
7. For a stable configuration for production use, perhaps read my signature links to acquaint yourself with appropriate configurations when using Base.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Aggregate functions unavailable in Base

Postby Villeroy » Mon Jan 07, 2013 9:00 am

RGB's suggests a pivot table in Calc which is a de facto database query like this one ...
Code: Select all   Expand viewCollapse view
SELECT <column fields>,<row fields>,<aggregated numbers>
FROM "Single_Table"
WHERE <filter button criteria>
GROUP BY<column fields>,<row fields>,<page fields>
HAVING <page field criteria>

... plus some extra options and a flexible cross-table layout that can be modified by the end user.
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: 26129
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

SOLVED: Aggregate functions unavailable in Base

Postby fow3 » Fri Jan 25, 2013 5:26 pm

Thank you everyone. The functions work fine on a 32-bit machine.
OpenOffice.org 3.2
Windows Vista Home Premium Service Pack 2
fow3
 
Posts: 3
Joined: Sun Nov 18, 2012 4:04 am


Return to Base

Who is online

Users browsing this forum: No registered users and 2 guests