A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

"Rewrite" an application without affecting old calculations



 
 
Thread Tools Display Modes
  #1  
Old December 2nd, 2009, 08:28 PM posted to microsoft.public.access.tablesdbdesign
ffulmer via AccessMonster.com
external usenet poster
 
Posts: 13
Default "Rewrite" an application without affecting old calculations

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/For...esign/200912/1

  #2  
Old December 2nd, 2009, 09:09 PM posted to microsoft.public.access.tablesdbdesign
Stefan Hoffmann
external usenet poster
 
Posts: 991
Default "Rewrite" an application without affecting old calculations

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/General...ing_Principles


Step 2:
Change your calculation.


mfG
-- stefan --
  #3  
Old December 2nd, 2009, 09:09 PM posted to microsoft.public.access.tablesdbdesign
Stefan Hoffmann
external usenet poster
 
Posts: 991
Default "Rewrite" an application without affecting old calculations

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/General...ing_Principles


Step 2:
Change your calculation.


mfG
-- stefan --
  #4  
Old December 2nd, 2009, 09:09 PM posted to microsoft.public.access.tablesdbdesign
Stefan Hoffmann
external usenet poster
 
Posts: 991
Default "Rewrite" an application without affecting old calculations

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/General...ing_Principles


Step 2:
Change your calculation.


mfG
-- stefan --
  #5  
Old December 2nd, 2009, 09:39 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default "Rewrite" an application without affecting old calculations

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





"ffulmer via AccessMonster.com" u51944@uwe wrote in message
news:9ffffdcafd9f1@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/For...esign/200912/1



  #6  
Old December 2nd, 2009, 09:50 PM posted to microsoft.public.access.tablesdbdesign
Mr. B[_4_]
external usenet poster
 
Posts: 171
Default "Rewrite" an application without affecting old calculations

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/For...esign/200912/1

.

  #7  
Old December 2nd, 2009, 10:23 PM posted to microsoft.public.access.tablesdbdesign
Bernard Peek[_3_]
external usenet poster
 
Posts: 42
Default "Rewrite" an application without affecting old calculations

In message 9ffffdcafd9f1@uwe, ffulmer via AccessMonster.com
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
  #8  
Old December 3rd, 2009, 03:04 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default "Rewrite" an application without affecting old calculations

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.




  #9  
Old December 3rd, 2009, 09:05 PM posted to microsoft.public.access.tablesdbdesign
ffulmer via AccessMonster.com
external usenet poster
 
Posts: 13
Default "Rewrite" an application without affecting old calculations

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200912/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:37 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.