eremmel wrote:Your description is not very clear to me.
But I think that you are looking to have the query extended with a rank per {year, FKServiceID} to be able to reset the rank count per year. This implies that you have to extend the places where FKServiceID is mentioned and add the year to it. But which date do you take for the year?
If you want to calculate the rank over a subset of the data you need to filter in the inner query on both aliasses a and b because you want to exclude records that are e.g. not executed yet; on alias a because you do not want them in the output and on alias b because you do not want to have them an impact on the Rank.
Hi Eremmel,
Thanks for heping me putting my ducks on a row.
The database I use for the examples has a table called tblServiceUsage.
This table provides an example of what the Client has.
If you also look at Query2, you see what happens with the ServiceID from the tblServiceUsage.
The last to digits of the ServiceID are the 3rd and 4th digits of the Project Number, created by the Query2.
The 1st 2 digits of the PN are the last 2 digits of the current year while the last
2 digits of the PN are used for the sequence number.
All of this is based on the requirement by the Client, who wants to stick to a 6 digit coding.
The Client sells a Service (using the ServiceID) and uses that in their accounting system, while all the documents are linked/stamped with the PN, which is related (see above) to the Service they sell.
The Client does not sell just one, but multiple Services to their Customers, and that definitely not in sequence.
The Client has a legal obbligation to keep track on what Services they sell, and how many they sell.
Some of these Services are training programs, and for those programs, they need to be able to identify who took part, and what the outcome of that program was for every individual.
Information about all of the Services must be kept on site at the Client's for a certain set time.
Information must be accumulated and forwarded to the authorities on an annual basis.
All these requirements lead to the following:
Service sold in a certain year.
The PN must be entered in the Project Calendar.
A Project Mager/Instructor needs to be linked to the Service.
A PM/Instructor must be paid.
How many participants, and their results.
Paperwork must de identifiable.
Paperwork must be kept for a certain set period of time.
Information must be forwarded.
Any type of information, related to any Service, needs to be retreivable.
Query2 only provides a total of Services as per date of entry of a Service.
The Client needs to be able to go back in the database and retreive any information. One of the methods is to use the PN.
Here lays the issue for now.
The PN is created via a Query, but what is really needed is some form of counting method on an annual basis for a Service provided, that can be retreived.
The table tblServiceUsage (an idea from DACM), uses the Current TimeStamp as an example of Date and Time. I need to use the ProjectDate, StartTime, and Executed, as the criteria, but that is an easy fix.
This table does hold all the Services entered and executed throughout the years.
The information will be linked to Participants, and must be accounted for.
So those Services that are entered, in a certian year must be traced back.
Hope this long story helps you a little.
Dream.
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.