rolling over information year to year
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.