Base: run query on connected spreadsheet?

English
, , , , , ,
#1

Howdy,

I created a a Base database that links to a huge .ods spreadsheet of weather data organized into date, time, and temperature fields. The link works fine and creates a properly formatted table in which I can see all the data.

The problem occurs when I try to run a query on the table. I want to display the maximum daily temperatures. So I add the date and temp fields to the query. But when I try to access the GROUP or MAXIMUM functions in the drop-down menu, I find the drop-down menu is empty. In fact, there are no functions available in queries at all.

Am I doing something wrong, or is this a limitation of connected databases in Base?

Is there a work-around?

When making a query in base on a table from an ods file, I cann't define any fucntions to the queried fields
#2

There are many limitations with Base tables which are linked from spreadsheets. One is that query functions are limited to COUNT(*) - that’s it.

Turning Base into an actual database by creating a table with the sheet data (have to start with a new Base file) you get all the functions. You register the DB then you can access the data from a spreadsheet via Data Sources.

EDIT:

I should add, the table data in your Base file is basically a text file (internally specified as file-based-database). There is no real database associated with this process. The functions you are looking for are in the database which in the case of a default base file would be HSQLDB.

#3

Okay. Thanks for the answers.

I wanted to use a connected spreadsheet because the dataset is very large (10 fields, 268,000 records, 14Mb filesize) and Base becomes slow and very unstable when I try to bring the data into an HSQLDB embedded database.

I’m guessing by your reply then it is infeasible for me to work with this dataset in Base?

#4

An embedded DB is certainly not the way to go for something this size. And you are correct in its’ instability. However a split DB would be a better solution. See this post to create a new empty split DB - click here. Also, this includes a newer version of HSQLDB providing better SQL functionality.

#5

@DinoGuy In the future, please reply to an answer with a comment to the answer you are replying to (can be multiple answers). If room is needed to further explain a question, edit your question.

#6

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.