Best way to create a revenue log?
I have a table with roughly a dozen A and B accounts, where revenue is tracked and projected on a monthly basis. A new field for every month, every quarter is a calculation of the months, and the same for yearly.
I would like to create a monthly snapshot of every record (A and B accounts) in a new table, say Revenue Log, with the purpose of comparing and analyzing the change in revenue projections on a per-account basis. So one record in Revenue would be related to one or more records in Revenue Log.
I was thinking of writing a script that will create a new record in Revenue Log from every record in Revenue, with additional field indicating the date of the record 'snapshot'. At this point I haven't explored automating the script to run on a regular basis, just want to have it working manually at this point.
Any suggestions or advice is appreciated!
-- FM 12.0v4