Amending a Product Price without altering existing orders in Microsoft Access

10 pts.
Tags:
Access 2000
Database programming
Microsoft Access
Microsoft Access 2000
Northwind sample database
VAT
Windows XP
Platform; Windows XP Microsoft Office - Access 2000 Adapting the example database that was supplied with Access - Northwind Sample Database. I adapted the above sample database back in 2003 to my own database but have now hit a snag and i am unable to rememberor or find the solution. Currently, If a product price is amended, then all NEW records will show the amended price but all EXISTING orders prices will remain unaltered, which is fine. However the VAT rate is altering on Monday and if I amend the VAT rate all EXISTING orders aswell as all NEW orders are showing the new VAT rate. How can I lock the VAT rate on existing orders? ie, using the same principal as the Product Price. I'm sure the answer is simple but I am struggling to find it Skipy

Answer Wiki

Thanks. We'll let you know when a new response is added.

It is a little difficult to figure that out, since we don’t know how the data is being stored (tables, fields, etc..) and manipulated (inserted, updated, etc…), but from the description of the problem, I think the VAT rate and totals are not being stored in the orders table, and they are being calculated using the VAT rate stored somewhere else.

If that is the case, the solution would be to add a VAT_Rate field to the orders table, and store each order with the VAT rate that was used when the order was inserted. So when modify the VAT rate, just new orders will be affected as they will get that new VAT rate when created, but old orders won’t be affected.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following