[Solved] Aggregate functions unavailable in Base

[fow3]

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.

[RPG]

Hello

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

[F3K Total]

Do you use Java 6 32-bit? That's neccessary if you use Base as "delivered" with internal HSQL Database.

[fow3]

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 .

[RPG]

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

[Villeroy]

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.

[DACM]

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.

[Villeroy]

RGB's suggests a pivot table in Calc which is a de facto database query like this one ...

Code: Select all

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.

[fow3]

Thank you everyone. The functions work fine on a 32-bit machine.