"Rewrite" an application without affecting old calculations
in [Tables]
|
From: ffulmer via AccessMonster.com on 2 Dec 2009 14:28 Hi, I would like to ask advice on updating, rewriting an application database file. I created an application (1 file accde includes tables) that keeps track of the works of a rental shop. It consist of tables where different data is stored and it has forms where a user can input data then it has queries that calculate rental prices and finally it has reports that print invoices. It works well. But The rental shop wants to change the way they calculate the prices. I can easily change the queries to make new calculations but it creates a problem. The new queries will change the old invoices by recalculating them. And that should not happen. I am sure it is a very common problem and must be a solution for that. I can think of three possible ways: 1,Keep the old application for historical purposes and use the new version for the new jobs. That looks the easiest. 2, Save all the old data in the database and stop recalculating them. There is a problem with this because it would save calculated data in the database. 3, Create complex queries that checks data and recalculate old data with the old method and new data with the new calculations. It looks a bit time consuming task. Does any one have suggestion what way to go? or other possible solution? Thanks in advance -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200912/1
From: Stefan Hoffmann on 2 Dec 2009 15:09 hi, ffulmer via AccessMonster.com wrote: > The new queries will change the old invoices by recalculating them. And that > should not happen. > I am sure it is a very common problem and must be a solution for that. > Does any one have suggestion what way to go? or other possible solution? Despite the fact, that you shouldn't store data which can be calculated, storing invoices is a must. Otherwise you never can guarantee that the old invoices will be correct. As this is what happens in your case. Step 1: Create a table (structure) for storing your invoices with the dependent data. Store the old invoices. New invoices will be stored when being issued to the customer. Basically the point when to store it, is when this invoice becomes tax relevant. http://en.wikipedia.org/wiki/Generally_Accepted_Accounting_Principles Step 2: Change your calculation. mfG --> stefan <--
From: Steve on 2 Dec 2009 15:39 In a very simplified way, your existing tables presumably look something like: TblCustomer CustomerID FistName LastName TblRentalItem RentalItemID RentalItem RentalItemPrice TblRental RentalID RentalDate CustomerID TblRentalDetail RentalDetailID RentalID RentalItemID Invoices are then created from the four tables. Rental Price is gotten from RentalItemPrice in TblRentalItem. In your new system, you need to add a new field, RentalPrice, to TblRentalDetail to store the current rental price. Then you need to add RentalPrice to your query and to your invoice report. Steve santus(a)penn.com "ffulmer via AccessMonster.com" <u51944(a)uwe> wrote in message news:9ffffdcafd9f1(a)uwe... > Hi, > > I would like to ask advice on updating, rewriting an application database > file. > > I created an application (1 file accde includes tables) that keeps track > of > the works of a rental shop. > It consist of tables where different data is stored and it has forms where > a > user can input data then > it has queries that calculate rental prices and finally it has reports > that > print invoices. > It works well. > > But > > The rental shop wants to change the way they calculate the prices. > I can easily change the queries to make new calculations but it creates a > problem. > The new queries will change the old invoices by recalculating them. And > that > should not happen. > > I am sure it is a very common problem and must be a solution for that. > > I can think of three possible ways: > > 1,Keep the old application for historical purposes and use the new version > for the new jobs. > That looks the easiest. > > 2, Save all the old data in the database and stop recalculating them. > There is a problem with this because it would save calculated data in > the > database. > > 3, Create complex queries that checks data and recalculate old data with > the > old method and new data with the new calculations. It looks a bit time > consuming task. > > Does any one have suggestion what way to go? or other possible solution? > > Thanks in advance > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200912/1 >
From: Mr. B on 2 Dec 2009 15:50 I am assuming that you are using queries to populate forms and if this is true, my initial thought is that you might create a new query that has the new calculation method. Then I also have to assume (you did not say) that you are recording the date of the invoice. If this is true, then you can simply have code that will check to see if the current invoice is newer that the date you switched to the new calculation method and if so, have the code to set the new query as the record source for the form other wise, just use the old query. If any of my assumptions are incorrect, the the entire thought process may be incorrect. ----- HTH Mr. B http://www.askdoctoraccess.com/ Doctor Access Downloads Page: http://www.askdoctoraccess.com/DownloadPage.htm "ffulmer via AccessMonster.com" wrote: > Hi, > > I would like to ask advice on updating, rewriting an application database > file. > > I created an application (1 file accde includes tables) that keeps track of > the works of a rental shop. > It consist of tables where different data is stored and it has forms where a > user can input data then > it has queries that calculate rental prices and finally it has reports that > print invoices. > It works well. > > But > > The rental shop wants to change the way they calculate the prices. > I can easily change the queries to make new calculations but it creates a > problem. > The new queries will change the old invoices by recalculating them. And that > should not happen. > > I am sure it is a very common problem and must be a solution for that. > > I can think of three possible ways: > > 1,Keep the old application for historical purposes and use the new version > for the new jobs. > That looks the easiest. > > 2, Save all the old data in the database and stop recalculating them. > There is a problem with this because it would save calculated data in the > database. > > 3, Create complex queries that checks data and recalculate old data with the > old method and new data with the new calculations. It looks a bit time > consuming task. > > Does any one have suggestion what way to go? or other possible solution? > > Thanks in advance > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200912/1 > > . >
From: Bernard Peek on 2 Dec 2009 16:23
In message <9ffffdcafd9f1(a)uwe>, ffulmer via AccessMonster.com <u51944(a)uwe.?.invalid> writes >2, Save all the old data in the database and stop recalculating them. > There is a problem with this because it would save calculated data in the >database. The rules on normalisation are a good guide but an experienced designer knows when to break them. This is one of those situations. Start by using a make-table query to store all of the old calculated values. Having done that you can make the changes you need. In the new system make sure that the calculated invoice values are stored at the point where the invoice is printed or emailed. Check with your auditors what else they want you to store. They may ask for the unit prices and any taxes charged to be stored as well. -- Bernard Peek |