Kath Pelletti
KP at sdsonline.net
Tue Nov 9 21:26:10 CST 2004
Jim - the business is in stockbroking. The main purpose of the system currrently is to provide valuations of portfolios to approx. 300 clients who can each hold up to 19 accounts. At the moment the system is fairly lean on data storage because it uses a daily refresh principal, ie. each day all stocks, holdings and prices are discarded, and live market data is read in to the system. Queries and reports read the new data to provide up to date valuations. This process will continue to take place, but every 3 months the client wants to 'freeze' that position and keep it as a position for that quarter. Every quarter a new position will be 'frozen' and a history will be built. My client requires the historical positions to be editable. The 6 tables will hold details of clients at that point in time, accounts held by them at that time, and all investments held at that time, as well as prices held for those stocks at that time. For this reason, a summary just won't be enough. I agree that the ongoing maintenance and speed / efficiency has to be uppermost on the design decision. I would like to keep as few tables as possible - but if I throw all historical data out to one set of 6 history tables is it the most efficient? Another option is to only hold a table of report cycle id's in the BE - with a link to an external mdb which holds all the actual data. I am still thinking it through and look forward to seeing what others think... Kath ----- Original Message ----- From: Jim Lawrence (AccessD) To: Access Developers discussion and problem solving Sent: Wednesday, November 10, 2004 2:06 PM Subject: RE: [AccessD] Adding history cycles of data Hi Kath: Just a comment; 6 new tables a quarter? Why? I can see adding some historical summary data to a special set of tables, to maybe aid in fast processing for a complex set of calculations but continually generating table after table seems to be a waste. That is where quering are used. A number of years ago I took over a site with about ninety forms and I can not remember how many queries, macros but the list was pretty staggering. The system is now running on less than twenty non-duplicated forms and the only thing that increased has been the data and number of reports. My recommendation is try to keep the application as lean as possible. Think of the poor programmer that has to manage it all...and it may be you! :-) Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Kath Pelletti Sent: Tuesday, November 09, 2004 5:55 PM To: AccessD at databaseadvisors.com Subject: [AccessD] Adding history cycles of data I need to add a historic reporting cycle to an existing database. The BE currently has around 65 tables. The FE has around 150 queries, and approx the same no. of forms, with approx 40 reports. The decision has been made to store some historic data, so on a quarterly basis I need to create 6 new tables of data which will be held permanently in the BE, ie. 6 x 4 quarters = 24 new tables per year. This system also has a linked mdb called Update.mdb to allow me to rollout updates automatically, as well as a linked web mdb which holds data which gets uploaded to the website. As I work for myself, before I create this new logic to create 6 new tables per quarter, I wanted to run this by the list - is there any argument for storing these history tables outside the main BE? Is there any advantage to creating 6 large history tables with dates, rather than 6 per quarter? If you were needing to add a historic cycle to a system, would you use the approach I am suggesting? I have worked hard to keep this system running fast and the clients are happy with the speed - so I don't want to slow it down... (And does anyone have some code to create tables directly into the BE and link them straight back to the initiating FE?) TIA Kath -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com