6 Replies Latest reply on Nov 27, 2012 8:35 AM by philmodjunk

    rolling over information year to year

    vmtc

      Title

      rolling over information year to year

      Post

           I'm in the process of overhauling a system of creating annual royalty statements for authors.  I'm trying to imagine a better structure than the current system, but am can't quite grasp the best way to do it.

           Essentially, these statements are produced annually. The first step is to import the relevant books and lookup some figures from the previous statement (eg sales to date, stock figures, unearned income carried foward). The last step is to achive them. (Of course there's lots of other things along the way, but that's not the part I'm concerned about right now)

           So for the 2012 statements, the the look ups occur (eg closing sales to date --> opening sales to date) as book records are imported into a generic table called 'Royalties'. And once it's all done a copy of the table will be saved as 'Royalties 2012'.

           Til now, the TO  used to look up these figures has been updated to match the table for the last round (so  the 'Royalties 2011' match to 'Royalties'  will be updated to 'Royalties 2012' before the lookup occurs), but I reckon there's got to be a better way. For instance I was wondering if it might be an idea to create fields in relevant existing tables or even create a specific holding table for the purpose. Then write a script so that this is automatically updated when the archiving is done. That way you wouldn't need to reference the archived file at all.

           What I would like to be able to do in this scenario is to write a script that would:

           a) Update fields from the finished file, such as closing stock, in given location (ie new generic table or new fields in existing tables)

           b) Save a copy of finished file as 'Royalties 2012' (though not sure how to do this, since it's hosted on a Server)

           c) Delete records from 'Royalties' file, leaving a blank file for the next period.

           Then next period, the figures carried forward from last period would be looked up automatically as the new book records are imported.

           Is there a better way to do this? I just wonder if I'm stuck in old thinking and completely missing something really obvious. Also, I know you can't use 'Save As' for Server files, and I'm wondering if there is an equivalent to this somewhere. It would be nice if it was a one step process, to make sure the figures were updated when archiving was done. There needs to be some check on this, since it wouldn't necessarily be obvious if it wasn't done and could lead to a huge headache. 

           Hope this is clear.

           Thanks. 

            

        • 1. Re: rolling over information year to year
          philmodjunk

               I suggest rethinking your entire approach.

               As you have found keeping your data for each year in a separate file really complicates the process and makes any kind of analysis of your data that spans multiple years very difficult.

               It should be possible to keep all of this data in the same set of tables in the same file, but with date fields or number fields that store the year to enable you to work with groups of records for each subsequent year.

          • 2. Re: rolling over information year to year
            vmtc

                 Thanks Phil, that does sound more sensible. A good starting point.

                 I'm not sure how best to bring in the carry forward figures, though -- any ideas?

            • 3. Re: rolling over information year to year
              philmodjunk

                   Not without a much more detailed understanding of your design and exactly what you need to "carry forward".

              • 4. Re: rolling over information year to year
                vmtc

                      

                     It’s quite a complex system.

                     The royalty statement uses information from about ten interconnected tables. In the first place it has info about the copyright holder for a title, their royalty percentage, any advance they have been paid, their agent, tax, payment and address details. Advances have to be deducted from their earning. There may also be extra income earned by the title, other than through book sales (for example if someone pays a permission fee to reproduce their work). Most of this is straightforward and fields are populated by look up when import is done from Copyright file.

                     Secondly there is basic information about the title and author. Related to this there is stock information, including comps or damaged books and print runs since the last statement etc. Again this can be done via lookup.

                     Thirdly, there is a sales table. Sales figures have been imported from Excel reports from different channels (or sellers) each month and these are summarised by sale type (local print, local digital, export print, export digital and discount sales). In the past these appeared as one line sentences, followed by a payment figure eg

                     "47 local print sales. Royalty of 10% on $937.86:"    $93.79

                     [text in inverted comments is a calculated Case statement with options for negative, zero and positive. They are all versions of this:

                     Local print units total > 0; Local print units total & " Local print book sales. Royalty of " & Royalty % local & "% on $" & 

                     NumToJText ( Int ( Local print dollars ) ; 1 ; 0 )  & Left ( Round ( Mod ( Local print dollars ; 1 ) ; 2) + 10 ^ - 5  ; 3 )]

                     This also used to be a lookup, but now should become dynamic. However, the figures (ie Local print unit totals, and Local print dollars) will all be based on summary fields from the Sales file, and I’m worried this may slow it down to a crawl. If so, wouid love to know how to get around this, other than by importing the figures. Again I think there is something obviously wrong in my approach and am keen to uncover what it is. 

                     Finally, there is a charges table, detailing any expenses (other than the advance) to come off the earnings.

                     The royalty table uses this information to produce a statement with author, title, copyright holder, address etc , and a number of lines describing sales, charges, other income, tax, and total payable. If this last figure is under $100 it is carried forward to the next period. (However this is complicated by the fact that if a copyright holder has more than one title, the amount is only carried forward if payments across all titles are under $100. And unearned income is not generally deducted. So a payee who has two books each paying $60, and and a third with $300 in unearned income to be carried forward would get paid the $120. But that’s by the by)

                     The figures that need to be carried forward from the previous statement are as follows:

                     Closing Sales to Date (2011) = Opening Sales to Date (2012)

                     Closing Stock  (2011) = Opening Stock figure  (2012)

                     Amount carried forward  (2011) = Amount brought forward  (2012). This can be either unearned income, or a positive figure under $100 that has not been paid out.

                     [Opening Sales to Date + Total Sales = Closing Sales to Date

                     Opening Stock + print runs – Total Sales – comps – damaged copies – stocktake adjustment = Closing Stock]

                     For the carried forward figure, the payment totals (local and export) are totaled only if a ‘to pay’ button = no. Depending on whether the result is positive or negative, a statement field generates “Unearned income carried forward”, or “Credit carried forward” and the absolute figure is displayed.

                     Does that help??

                • 5. Re: rolling over information year to year
                  vmtc

                       PS Forgot to say that still using 10 Advanced, upgrade to 12 impending if it makes a difference.

                  • 6. Re: rolling over information year to year
                    philmodjunk

                         I think you need to pay a consultant to sit down with you and your database files and do a top to bottom analysis of what you have now and what you want to accomplish.