Changing VAT but saving old records (1 Viewer)

[Duracell]

Hi,

I have created a simple invoice database that works fine, however, I if the VAT rate changes I want to save the existing records with the old VAT rate (for audit purposes) and let any new records reflect the new VAT rate.

The VAT calculation comes from a 'Company Details' form (and coresponding table) and I have a finance form/table that contains all of the information for quotations queries etc

Is there a way of making new records (in the finance form) use the new VAT figure in the 'Company Details' form when it is changed but keep the existing quotes and invoices at the old rate.

At the moment if I change the VAT rate, it changes all of the existing records to the new rate.

Many thanks for any assistance.

 

[Steve R.]

Create a table of VAT rates that has a begin and end date for each VAT rate. Of course you will need to have a special code to identify a VAT rate that is still in effect. Also your "Company Details" will need a date field that is used to "find" the correct VAT rate.

Ideally, both "old" records and "new" records would then retain the correct VAT rate.

 

[neileg]

Create a table of VAT rates that has a begin and end date for each VAT rate. Of course you will need to have a special code to identify a VAT rate that is still in effect. Also your "Company Details" will need a date field that is used to "find" the correct VAT rate.

Ideally, both "old" records and "new" records would then retain the correct VAT rate.

This is the purist approach and technically correct.

However, most of us accept that where calculations are time sensitive, as yours are, you can break the rule about not saving calculated data. To do this you would add a VAT field to your finance table, do the calculation and store the value. Alternatively, you could store the rate and then do the calculation when you display the data.

If you actually use the database as part of your accounting records, HM Revenue & Customs will expect to be able to interrogate your VAT account to see all of the transactions. It will be much easier to convince a VAT Inspector that you have got it right if you can show a simple list of transactions with the VAT recorded at the time the transaction was created.

 

[Duracell]

Thanks Neil

If I added the VAT field to the Finance table would that not mean that I would have to enter the VAT rate for every record I created? It's no big deal if I do but it would be nice to be able to set it once (until the rate changed)

 

[boblarson]

If you are going to store something I would store the VAT percent.

You also can store the current VAT percent in a table which you then use when storing what you have sold.

 

[Duracell]

Thanks All,

I have solved the problem (with your help).

For reference:I created a VAT field in the finance table and set the default value to 17.5%.
Created a 'VAT form' so that different VAT figures can be added.
Added a 'VAT' drop down Combo box to the Finance form so that different rates can be selected.
'Double Clicking' on the Combo box brings up the VAT form to make it easier to add a new rate.

As the default value is 17.5%, any new record automatically calculates at this rate without any input, but as per above, the rate can be changed if the government decide that we are not paying enough in taxes already.......

Thanks again for your replies.