I'm setting up a database to keep track of membership and payments. I created different tables with member information, using the 3 rules of normality, and created several nifty forms to fill my tables with actual data. My problem is the computation of expiration date of membership for each member. I'm trying to set this as a Query. I don't want to involve macros, if possible.

The membership could be purchased at any point of the year, and expires on Dec 31 (rules of the society). There are packages of 1 year and 3 years. So, a member could buy a 3 year membership on January 2015 (expiring on Dec 2017), and then another year membership on February 2015 (expiring now on Dec 2018), plus another year at some point of 2016, pushing the expiration to Dec 2019. I can compute an expiration date per each payment, but that is meaningless. I need some form of aggregation that's smart eough to undertand that a Payment is being done when the membership is active, and sum the purchased time to the current expiration date, and that needs to be done per each member.

I'm not handling tens of thousands of members, so using a complex convoluted approach is feasible without fearing a high impact on performance (I think).

You need a query that takes the payment history in account. Such a query can be crafted, but you will need an recursive SQL statements. Such a statement is not possible with the embedded SQL engine shipped with OpenOffice (HSQLDB 1.8). If you like to use the Split database configuration (See tutorials) you will be able to use recursive SQL and we might solve the problem. Note that you are then using HSQLDB 2.4x version or newer. You might also like to use a database like SQLite, PostgreSQL, MS SQL Server, Oracle. MySQL has its own way of doing things, far from standard.

So let us know if you are prepared to take a deep dive in SQL.

It's Microsoft marketing that tells you computers are qualified for non-technicians

Thanks for the reply eremmel. I've been trying to avoid using a split configuration. I wanted portability and simplicity of the result. A single file with all the data, no requiting connections to a database, The fact that I'm using Base intead of Access is already conflictive, but is easier to tell the people that will get the data: Look, install OpenOffice and double click on the file, than telling then, Install OpenOffice, then Install MySQL or MariaDB, and connect to the database using a JDBC...

That being said, I have to admit that I never tried it. How easy is to do it, as an user, not a developer? Meaning, installing the software and checking the connection with the SQL engine?

Probably the best solution is setting a SQL server on website and using html/php to create the forms. A completely different beast. And not sure if there's an easy to trasnlate the logic behind my forms into a web-based form. I use a lot of filtering annd subforms to discriminate data

Your user does not need any of the above macros.A self made database package (Base document, database files, driver and optionally forms in stand-alone documents) can be installed in simple steps:1) Install OpenOffce or LibreOffice (meanwhile I prefer the latter).2) Create a directory and extract the zip file to it.3) Allow macro execution for this directory in Tools>Options>Security>Macro Security>Trusted Sources. Do NOT turn off macro security.4) Open the contained MyDatabase.odb. 3 success messages pop up to inform you that the database has been connected to some files, to a database driver and that the database has been registered.

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.Ubuntu 18.04, no OpenOffice, LibreOffice 6.x

I think I've a solution for you based on the attached 'split database' zip file. I took the route:0 PAYMENTS is a simple table that hold data based on your description.1 A payment in year Y last for year Y or Y+2.2 Multiple payments in the same year can be considered as one payment for the sum-of-periods year. For this I made a view in the database (vPaymentsPerMemberYear)3 To compare the payments over a series of years we have to have an ordering For this I made a view in the database (vPaymentsPerMemberYearRanked)4 Based on this view (3) we can now roll up the payments and calculate the Expire Year with an recursive SQL statement. This is found in query (qPaymentsExpiringPerMember).5 Note that you cannot view the views in the database easy, so a copy of the view-query is also saved (qPaymentsPerMemberYear and qPaymentsPerMemberYearRanked)

Just unpack the zip file and open the base document Payments.zip and open Payments\PaymentsExpireYear_Split_HSQLDB_2.4.0.odb.Note it is on a google drive because it is too large to attach here.

Here is the recursive SQL statement for those who do not want to open the zip file.

The trick is in the calculation of EXPIREYEAR. For the very first payment it is for the current year. When a new payment is received in a year before the ExpireYear ends, the ExpireYear is increased with the amount of periods, else we correct 1 period for the current year.

Edit: Experienced SQL programmers might ask why I used UNION iso the common seen UNION ALL in recursive SQL (e.g. Oracle, MS SQL Server,...). It has to do with the offical standard occording this post Recursive query regression and Recursive query runs forever. I also ran into this isue.

Last edited by eremmel on Fri Sep 14, 2018 10:04 am, edited 2 times in total.

It's Microsoft marketing that tells you computers are qualified for non-technicians

Villeroy wrote:https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=86071 with a macro installer for ExtractHSQLDB.py which extracts and reconnects your database automatically.https://forum.openoffice.org/en/forum/v ... 21&t=77543 provides a GUI to connect a Base document to an external HSQLDB and to any hsqldb.jar (the single driver file). Also included is a customizable Basic module which allows you to distribute database packages.

Your user does not need any of the above macros.A self made database package (Base document, database files, driver and optionally forms in stand-alone documents) can be installed in simple steps:

Thanks Villeroy for the reply. I still need to check the content of those files. But my understanding is that it's possible to distribute in a single file (a database package) a LibreOffice Base document that uses a more up-to-date version of HSQLDB, and the final user won't need to do the manual intall of the jar file (the jar file needs to be included in the db package). To build this file, is a simple as creating a ZIP and changing the extension to .odb? Also, from the description of the links, it seems like I could do the migration process only with the 2nd link. And that could also provide help to create the aforementioned database package

Thanks again, and sorry for being so newbie in this issue. I've been reading a lot these past few weeks, and your name always comes up in my searches for documentation. It was a nice surprise to see the pluto icon in a reply of my first post in this forum.

No, simply you create a zip (or any other archive) with an odb, a database folder and a driver. Tell the user to extract the archive in some dedicated directory rather than in his download folder.Then open the extracted odb where you have set up my auto-run macro which adjusts the paths to the driver, to the database and the registration path when necessary.

The user opens the odb and sees 2 or 3 messages:"Successfully connected this document to database backend C:\charlie\PholloxDB\database\Phollox""Successfully connected this document to jdbc driver C:\charlie\PholloxDB\driver\hsqldb.jar""This database has been registered under the name "Phollox""

Where "PholloxDB" is the dedicated folder created by your user."database" is a subfolder containing database files whose names start with "Phollox" (Phollox.script Phollox.properties Phollox.data)The driver "hsqldb.jar" is located in subfolder "driver".And Tools>Options>Base>Databases (or the data source window) shows "Phollox" as a database name pointing to the database document, possibly C:\charlie\PholloxDB\Phollox.odb

1) Apart from the optional registration, this structure of files and subfolders is what you get when using my Python macro.2) Then you add the Basic modules "AutoInstall" and "Helpers" from my "FreeHSQLDB" library to your database document.2a) Tools>Macros>Organize>Basic... [Organizer...], tab:Modules.2b) Dbl-Click your database document name on the left side. With AOO you may need to select your document and push the [New] button in order to get hold of the Standard lib2c) Then navigate to FreeHSQLDB and drag one module after the other to your document's standard library [b]holding the Ctrl-Key[/u] in order to perform a copy rather than a shift of the module.3) Edit your copy of AutoInstall to adjust the constants on top of the module.4) Finally you make your document run its "onDocumentOpen" routine when the database document is opened (Tools>Customize... tab:Events).5) Put everything (Base doc, database folder and driver folder) in a zip.Example database: [HSQL2] Self Made Easter Sunday in SQL----- STANDALONE FORMS --------------If your database is registered under some name, running my form extraction macro of the FreeHSQLDB library stores all your forms in stand-alone Writer documents which can be accessed throuh OS desktop links and hyperlinks. Add a forms folder to your zip package and use the same registration name in the AutoInstall module.

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.Ubuntu 18.04, no OpenOffice, LibreOffice 6.x