5 Replies Latest reply on Mar 11, 2015 9:11 PM by bigtom

    Help with Lookup & Calculation involving 2 Related Tables

    binu.alexander

      I am working on a  solution for a  Book Translation Firm .   The Translators Have to Give Weekly Reports on their work.

       

      I have 2 Source Tables 

      A : List of Books , Total Chapters and Verses

      B : List of Books with Individual Chapters and Verses

       

      Screen Shot 2015-03-10 at 12.16.48 PM.png

       

      This the the Report  Format  C

      Screen Shot 2015-03-10 at 12.25.58 PM.png

      What I managed to create so far is  

      1)  Drop down menu to select a book (math , science ,etc)

       

      2) Display Total Chapters and Total Verses ( green  : are Looked up from TABLE A Automatically)

       

      What I also want to do

       

      1)Have the user enter chapters done (number)

       

      2)Calculate Number of Verses Done (blue ) based on Chapters Done ( By Referring to Table B)

       



      Any Ideas on how to go about it ? I got stuck at this point


       

        • 1. Re: Help with Lookup & Calculation involving 2 Related Tables
          davehob

          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.)

           

          Dave.

          • 2. Re: Help with Lookup & Calculation involving 2 Related Tables
            binu.alexander

            Thank you Dave ,

            Sounds like a good idea , Will try it out and see

            • 3. Re: Help with Lookup & Calculation involving 2 Related Tables
              oliver.boeckmann

              Hi Binu,

              to me your table schema does not appear very useful. From what I understand I would create it like this:

               

              Bildschirmfoto 2015-03-11 um 10.27.44.png

              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.

              • 4. Re: Help with Lookup & Calculation involving 2 Related Tables
                binu.alexander

                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 ..

                • 5. Re: Help with Lookup & Calculation involving 2 Related Tables
                  bigtom

                  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.