Question Archiving of old records when changing to new VAT Rate (1 Viewer)

[paulml]

I have read several posts regarding changing of vat rates but am still struggling to find an answer.
I wonder if anyone can please help shed some light on my dilemma.
I'm fairly inexperienced at Access although i did write a database for my former company several years ago. This still handles customer orders, stock control and delivery schedules.
My problem is this:
Each customer in our Customers Table has a set Delivery Charge based on their location but this value includes whatever the current VAT rate is. Delivery Charge is shown and stored in the Customers Table as the gross amount. The products we sell are not subject to VAT so this only affects the Delivery Charge
Customers wish to see the VAT content of their Delivery Charge shown separately on their invoice and so I have used calculated aliases in order to show the net charge and the VAT charge separately.
The following parts of my query use the alias “Delivery” to show the net amount and the alias “VAT on Delivery” to show the VAT content at 15%

Delivery: [DeliveryCharge]*0.8695
VAT on Delivery: [DeliveryCharge]*0.1305

Its easy enough for me to change these calculations but as the VAT is actually calculated in the query (OrdersInvoiceQuery), whenever the calculation for the rate changes, all the previous invoices change to the new VAT rate.
If anyone requires a copy invoice from a previous delivery, (before new VAT rate) it will show an incorrect VAT figure even though the gross figure has remained unchanged. (My company have absorbed any VAT differences)

Not only is this obviously wrong, but it appears to be an incorrect way of calculating the VAT content in the first place.

I’m thinking that archiving the old data is possibly the way to go but we need to be able to refer to customer’s order histories fairly regularly so any archived data must still be fairly accessible.

When the UK VAT rate changed from 17.5% to 15% last year, the calculations were just changed in the relevant query but this is not satisfactory as it changed all the historical values too.

I’m keen to do it properly this time and really would appreciate any pointers? J

 

[rainman89]

I would create a new table that holds the year and the vat rate for that year. Then tie that into your other table as VATID or something of the sorts. That way your old data would not change, and your new data can be assigned a diff vat rate every year.

 

[Rabbie]

I would create a new table that holds the year and the vat rate for that year. Then tie that into your other table as VATID or something of the sorts. That way your old data would not change, and your new data can be assigned a diff vat rate every year.

Good advice but remember the UK VAT rate changed on 1st December 2008 and went back up on 1/1/2010. so you need to cater ro the 13 month period it was at 15%

 

[rainman89]

Good advice but remember the UK VAT rate changed on 1st December 2008 and went back up on 1/1/2010. so you need to cater ro the 13 month period it was at 15%

Honestly, I don't even know what VAT Rate is , so I was just going by how I would change the DB for data changes like that....

 

[paulml]

Thank you very much for your input. I've added a VAT table and am now looking at how to tie that into my work. I'm not exactly sure of how it will work but will puzzle over it a while.

 

[gemma-the-husky]

Delivery: [DeliveryCharge]*0.8695
VAT on Delivery: [DeliveryCharge]*0.1305

the problem is that these expressions effectively "hard-code" a particular VAT rate. (of 15%) - The VAT and VAT exclusive elements will be different for a VAT rate of 17.5%

In order to manage both situations - ie VAT rates of 15% AND 17.5%, your system needs to work differently, selecting and using a VAT rate based on the date. This change may affect other areas of your system, and may quite far reaching to fix properly.

 

[neileg]

Although a purist may hold the VAT rate with its effective dates and do the calculation on the fly, pragmatists will store the amount of the VAT with the transaction so that historic data doesn't change.

 

[Simon_MT]

You should always store the Vat Code and Vat Amount or Sales Tax Code / Sales Tax Amount in a table. This may seem a little heavy handed.

Simon

 

[gemma-the-husky]

incidentally, the problem of changiong data isnt limited to VAT rates - take a customer name and address - say the address changes - now if you reprint an old invoice, it will print with a different address to that which was on the original document - so the problem comes to a more general issue of deciding

a) which changes are important to track, and
b) a suitable mechanism to track them

 

[Simon_MT]

With address changes, one could always store the delivery address for each and every transaction so no matter what happens to the Customers address the original documnebt will be unchanged. You have to take a view, whether or not that is important to keep that information and balance that with how often a Customer moves address? In the UK paper copies of invoices have to be kept anyway for compliance purposes.

I would always maintain Stock values with the current sales tax information and transactions with the prevailing sales tax at the tax date.

Simon

 

[paulml]

again, thank you all for your time and thoughts.

incidentally, the problem of changiong data isnt limited to VAT rates - take a customer name and address - say the address changes - now if you reprint an old invoice, it will print with a different address to that which was on the original document - so the problem comes to a more general issue of deciding

a) which changes are important to track, and
b) a suitable mechanism to track them

We already have a seperate delivery address which stays with the historic invoice/order details even if the customer's billing address changes. These details are stored in an "Order Details" table. That seems to work fine but i'm still struggling to tie the tax issues into things.

As a temporary measure, to keep the wheels of the poultry industry turning, I've updated all of the users front ends with the new VAT code to produce new invoices but have left one machine with the old VAT code to print off any historic invoices that may be required. This, i'm sure, seems a terrible way of doing things but it does buy me some time to be able to do the job properly.

As i only work on this project when the office is quiet, it may take me until the next Tax Rate change to figure it out!

 

[Simon_MT]

In this case you could store the old Vat Amount and then calculate the rate or the old VAT rate and then tag the transaction using the old rate or set-up system wide dates to trigger the old calculation. If the Order date does not fall into this rate use the prevailing Vat Rate.

Simon