calculating VAT and total

[abz_y2k]

Hi,
Am creating a database on MS Access 97,

I have got different prices for various items without vat, what i want it to do is, when i enter a value on certain item (without vat), i want it to automatically generate total value including vat.

Secondly, i want it to total up all the items prices on the different field, like its done on excel, sorry for stupid questions but am not to familiar with access..

can anyone help, thanks in advance

 

[John_W]

(VAT = value added tax, or sales tax)

On a form you can have a calculated field just like in Excel. So if your base price is in a text box called Price, you can create a second box called (say) PriceIncVAT and set its Control Source property to =[Price] * 1.175. You can also calculate the sum of several fields using the same principle.

Of course you might prefer to have the rate of VAT as a variable parameter held in another table...

 

[abz_y2k]

hi thanks for your reply.

sorry maybe i wasnt to clear. what i want is to input a value on a field without vat, and once i did that i want it to automatically generate a value including vat on the same field.

 

[gemma-the-husky]

you will probably need a VAT table to store the VAT rates

VAT rates may change in the future, or some items are zero rated, or non-standard rated. Therefore you need to allow for this, so your sales can automatically find the correct VAT rate.

Therefore you need to lookup the VAT rate, and use it for your calculations - You may want to store the rate you looked up (this involves some redundancy, but otherwise the VAT rate you look up will depend on the date or other parameters)

you can then calculate the VAT by multiplying the Price/quantity by the VAT rate. Again, you dont HAVE to store this, but the VAT is likely to involve rounding off pence fractions, and you may therefore decide to store the rounded VAT

Note that if you have several lines on an invoice, the VAT calculated line by line may be different to the VAT that would be calculated if you worked it out on the whole invoice. This is then made more awkward if not everything on the invoice is rated at the same VAT rate.

Really its applying the same principles that you do in Excel, but in Access, you wont "see" any obvious errors as its all done behind the scenes. Therefore your system needs to allow for any eventuality.

If you dont want a VAT table, you could just input the VAT rate each time you enter an item, but this is awkward for anything but a minor number of invoices.