Some background…
I’ve used FileMakerPro for a goodly number of years and a couple of databases with historical (but ongoing) data I want to track. My MacBookPro was getting quite old and cranky, and I recently upgraded to a new model. Unfortunately, my old FileMakerPro won’t run on the new laptop.
An updated version, even the “Individual use” version is, um, pricey. So I wondered if I could re-implement my old databases in LibreOffice Base. LibreOffice is my “production” choice for word processing and spreadsheets.
FileMakerPro is quite a powerful tool and does a lot of stuff for you. I’m sure you can do a lot of the same stuff in Base, but I’m struggling. I’ve worked my way through a lot of things, but I’ve run up against something that I haven’t figured a way around.
My Setup
My file tracks historical production of our solar panels. There are two tables. One is simple, just a date and the number of kWH produced by the panels that day. So always one row per day. The other comes from our monthly bill. Each row has two dates representing the beginning and end dates (bills aren’t generated on the same date each month, so a “month” is the dates between bills), then the total kWH sent to the grid (while production exceeds current usage) and total from the grid (when it’s dark or usage is really high).
From those two monthly totals and the total generated during the covered period I compute the actual usage for the month, and from that the average daily usage.
And Here’s Where I’m Stuck
I don’t know how much it’s my very rusty SQL and how much is not understanding how to do things in Base, but I haven’t figured out how to do the “total generation in a month” calculation.
And of course Base just unhelpfully says “There’s an error in your query.”
Here’s my currently query. And I know, this is incomplete, it will sum all production after the start of the month. At this point I’m just trying to get something that works at all.
SELECT SUM( “ProdTab”.“Production” ) AS “TotProd” FROM “ProdTab” WHERE “ProdTab”.“Date” > “Bills”.“StartDate”;
I’m putting the above in the “Field” row of the query and I get a response that the field isn’t recognized. When I define a simpler calculation in the query (no select, just a calculation on another field), that works OK.
What am I missing?