Reporting and Charting record history
I am currently designing an investment management database and there is one record for each account. I am trying to find the BEST SOLUTION to creating data history for charting and reporting purposes. My current solution has been creating a snapshot of the account at specific intervals (monthly) and creating a new record in a different table for the snapshot of each account. I tried this out with Version 1 and it seems to work great except I think the only downfall is the STATIC information. The snapshot is frozen and cannot be changed. Reports cannot be modified to view certain trends or data ranges; limited anyways.
The account is based on different transactions that are all calculated. I think there may be a way to create a realtime charting system that would flow data from the child table to the parent table and generate the proper data for charting. My thought process has been OVERWHELMING to say the least. Would I be using loop scripting and data calcuations to create specific intervals? Would I generate snapshots of a specified account in a different table for every report I run and then delete upon exit?
Any input on this matter is greatly appreciated.