Help calculating VAT rate

[spicegirl]

Hi

I am new to building databases. I have created a materials table which as net cost, VAT rate, calculates total cost per unit etc.

I have a VAT rates table separately. The VAT rate field in the Materials table is supposed to have a look up to the VAT rate table (which it does).

However, when I calculate VAT amount in the Materials table (net cost field x VAT rate) it is multiplying the net cost by the VAT rate unique ID i.e. net cost is £15, using 0% should give a result of £0 VAT, but because 0% is unique ID 4 the result I get is £60!

How can I resolve this? :banghead:

Please give me instructions Janet and John style as Access is quite new to be...I can be a real whizz with Excel and am very logical so have picked most things up very quickly by self teaching but this has me stumped!

Thanks in advance
Mel

 

[gemma-the-husky]

use a query that joins the material table to the vat table, based on the linked vat codes.

the visual query desgn pane will do this.

now use the vatrate field from the vat table, and it should work.

if you do not "get" this - you really need to go back to your primer and re-read it, because this sort of thing is fundamental to the use of a Relational database

one other thing you need to be aware of, is the problem that will happen if and when the VAT rate changes, say from 20% to 22%. If you change the VAT rate in the table to 22% this will affect every record that previously was 20%.

so you may want to actually store the actual vat rate in any transaction that uses vat - or alternatively add a "date" indicator to your vat table, and make the lookup determine the correct vat rate at a given point in time.

it will end up being quite complex.

 

[Chrisopia]

instead of %, use the decimal version. 0% = 0... 22% = 0.22
15 x 0 = 0... I have the VAT on a stand alone table and only reference it. It's not linked to anything. I do this because previous invoices have old VATs so i reference them to date.

Then another query I link the VAT table in and say "if date = now then VAT = rate" if that makes sense... you could use a similar angle with the codes.