The uploaded database includes an embedded HSQL database, a form and a report.
I copied the 2 columns of item names and values as “TXT” and “VAL” into table “TBL” ignoring all the records where the value was text “#N/A”. That gives 55,158 records for 4,686 distinct item names. A 3rd column “ID” is basically an autommatically generated unique row number. I used that row number as “order of appearance”. Higher row number means “later”.
I copied the 33 calculated results from Joshua’s sheet for comparison.
Table “Filter” stores filter criteria for the form.
The database document weighs 2.7 MB because I added an index on item names “TXT” and values “VAL”. An index increases the lookup performance significantly. However, the database seems to hang for a few seconds when you close an object. This is because the whole database needs to be wrapped back into the document. A database connected to an external database does not show this lag.
The report “qLatest_All_Ag” is based on the equally named query. It dumps the averages and sums of the latest 10 records for each of the 4686 items to 92 print pages. This takes about 1 minute on my bread-and-butter laptop.
The form calculates the sum and average for one selected item at a time. The items can be filtered by a search text matching a part of the name or by a check box “Sheet Result”. When the check box is on, you get the 33 items with corresponding spreadsheet results. Check box off shows the other items. Check box in Null state ignores this criterion.
The filtered item names appear in the grey box.
The white box shows all corresponding values of the selected item.
The records in the 2 orange boxes should be identical. There is a simple method to select the latest 10 values of an item and there is a more complicated one. With HSQL only the complicated one can be aggregated. HSQL refuses to calculate any sum, average, minimum, maximum, deviation etc from the simple one. So the sum and average in the orange boxes are calculated from the second set. The grey box shows the looked up spreadsheet result (if any).
I failed to calculate the averages and sums for the highest 10 values of each item because the highest 10 values may include duplicates. Duplicates make it impossible to get the top 10 items ready for aggregation (sum, average etc.). There might be some different way (certainly there is with another DB engine) but I gave up on that problem for now.
It is very easy to append more pairs of name and value, with a most simple input form, directly to table “TBL” or by means of copy and paste (copy from spreadsheet, select “TBL” icon and paste). New name/value pairs appear instantly in the reloaded form and report without adjusting anything. You don’t even need to save anything. Everything is saved automatically when you give the database enough time for closing. For productive use, I would use another variant where the actual database is separated from the Base document.
