In my database I have thousands of records showing a list with 3 columns: date, location and name. In my query I want to order the list by date (this is easy: date, order, ascending) and create a fourth column (OrdAnual) showing the record order per year. The counting goes, of course, from 1 to n, every year. (n=number of records counted in corresponding year).
Ordenação.odb (16.0 KB)
Can you help me? Please, please. Thanks.
Hello,
The issue I see here is that the database you are using, HSQLDB embedded v1.8, is very old and lacks the built in functions present in newer databases such as Firebird embedded or a newer HSQLDB split database.
.
Looked today as possible options such as using a Sequence generation but your request to start over for each year nullifies that.
.
Do not see an immediate answer.
hello Quaresma,
the database you use is extremely old and not very well suited to your issue.
however this is doable.
I made 2 queries “qSetup” and “qOutput”.
it was necessary to create a view from “qSetup”, it’s called “vSetup” and is the data-source for “qOutput”.
because we require sequential values for each row I added 1 record to your table, it has an ID of 57, the date is duplicated. (i did this to ensure/show that duplicate dates will not result in code failure).
Quaresma.odb (16.6 KB)
Oh, my! This is it!!! Thank you cpb, you just did it! I don’t have to readjust the YearPosition manually anymore, each time I have a new input.
However, I would classify (manually) your testing input in number 7, not 8 (in the year 1909) because in case of equal dates (05-09-1909) I would add a second alphabetic order, which would place Maria after GUY. Can you add it to your query? Thank you so much. Quaresma
Please see the form “cpb_Solution” (05-09-1909)
Quaresma.odb (16.2 KB)
this is the line of code which sets the sort order.
on a."Data" > b."Data" or (a."Data" = b."Data" and a."IDRegisto" > b."IDRegisto")
if the field “Data” is duplicated then we execute the OR clause and the order is set by the field “IDRegisto”.
.
why “IDRegisto”? because the field referenced in the OR clause must be unique i.e. no duplicates, “IDRegisto” is the primary key and therefore unique.
.
if you can absolutely guarantee that the field “Participante” will always be unique for a specific “Data” then you can replace “IDRegisto” with “Participante” in both “qSetup” and “vSetup”.
Quaresma2.odb (16.2 KB)
.
PS, i should point out that it is possible to solve this without using the view “vSetup” but that means losing the formatting, changing the code a little and having to double quote every alias in the queries “qSetup” and “qOutput”.
Thank you, cpb, once again. You have my full consideration in this forum. Receive my compliments from Portugal.
Now I will have to break my brains trying to make a report for the output results grouping then by year.
thank you for your kind comments they are very much appreciated.
.
you will struggle to build a report with any of the previous uploads.
.
i have cleaned up and expanded the code in order to make it more versatile.
.
discard the previous attachments and use this, it includes a simple report based on a view which may assist in achieving your objectives.
Quaresma3.odb (22.8 KB)
I don’t know your name but that doesn’t stop me from being very recognized for the extreme help you gave me. And in this case, the help of a stranger is even more appreciated. I could never produce a report as well done as yours, firstly because it answers exactly to what I intended to do and secondly because my basic Libreoffice knowledge is still a long way off. The original table where I will apply your suggestions has about 2900 records, in 18 columns (dead people buried in my village cemetery, since 1890). It’s been an old dream of mine to have this database and to be able to include photos of deceased people in it, since that’s not possible in a spreadsheet. Once again, thank you very much for your help.