"Rewrite" an application without affecting old calculations

[ffulmer via AccessMonster.com]

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

 

[Stefan Hoffmann]

hi,

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 <--

 

[Steve]

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
(e-mail address removed)

 

[Mr. B]

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

 

[Bernard Peek]

In message <9ffffdcafd9f1@uwe>, ffulmer via AccessMonster.com

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.

 

[Fred]

This is a rare casse where I disagree with the other respondents.

An invoice is an entity, and, roughly speaking, a one time event. At the
time of the event it is derived from other data, but, after that, the result
becomes an entity. Like taking a photograph of an event ....at that moment,
it is "derived" from the scene, but then the photograph becomes and entity to
be stored. You can't "re-derive" it later. Your math change is one example
of why not. Another example is if a company changes their address and you
update your db accordingly. Your invoice is a record of where it was
mailed to, which is the old address.

Strictly speaking, DB's such as your current one (and Northwind) do not
store invoices, they merely create and export them as paper or electronic
documents. Under those DB's, if you want to "store" invoices, you need to
store the invoices, which are the electronic or paper documents.

Ditto for transactions and orders.

If the mission of your database is to store invoices within itself, then you
will have to restructure it to do so which will require setting up some new
tables and probably changing your definition of one or two existing tables.

If this route is of interest, you could tell us that and get more help.

 

[ffulmer via AccessMonster.com]

Thank you very much for all of you to reply my question and widening my view
in this topic.

I will make the final decision after discussing it with the rental shop owner
for whom I made this application.

Once again, your help is greatly appreciated.

ffulmer