Rather than working it out on the fly, you could have an additional calc field, "versesCumulative", in Table B which, for each chapter, holds the no. of verses up to and including this chapter. You could then just display the versesCumulative value for the number of Chapters Done. (That relies on the chapters being done in order, which seems to be the case from your example.)
Thank you Dave ,
Sounds like a good idea , Will try it out and see
to me your table schema does not appear very useful. From what I understand I would create it like this:
You can than create layouts for translators, showing them books/chapters/verses they are responsible for, and have them report which ones are done. You can then easily create time based reports that show overall progress, or translator productivity, etc.
Hope that helps.
Thank you Oliver for taking time out to give a good response .... Let me see how I can incorporate your idea into my solution. I am working on my schema to make it give me the desired output ..
Oliver has a good direction.
As well I would suggest you could simply have two tables. One for the source data (Table A) and another with fields to store the translation results that has the book,chapter, and verses assigned as well as how many complete and who they were assigned to. I am assuming you are storing the translated text as well.
You can use ExecuteSQL to generate the reports and allow the translator to see only their assigned work. This essentially does all the same things as the related model, but it is more record/data heavy while allowing you to assign individual chapters and verses to different translators easily. For example if you had one book that was on a rush and you needed to get the work load spread over 10 translators to get it done more quickly. This would allow you to transfer partially completed work to another translator while still having a goof accounting of what was already done. You could also use this to easily recompile all the translated data back into one document. You would also have a little more flexibility in your administrative reports.
You can do the same small accounting thing with relationships if you have a table for the smallest piece of work you wish to track (verses), that has fields for the book, and chapter included.
There are a few ways to get it done. Just find one that will work well for you.