Base: run query on connected spreadsheet?

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?

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.

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?

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.

@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.