Tax calculations

[Hellfire]

Hi All,

I have posted this q in the tables area as well, but have had no response yet. I have deleted that post, and decided to place it in here.

What I have is a table with the following fields:

Client
Description
Gross Price
Tax
Nett Price
Status

On the form used to enter a new record, all the fields will be displayed, except for the Tax and Nett Price.

I want this to be calculated from the value entered in the Gross Price field.

Tax should be calculated as: [Gross Price]x[17.5/117.5]
Net Price should be calculated as: [Gross Price]+[Nett Price]

Where do I have to add this functions?

I don't think it's possible to add in in the table itself, so I should probably add it on the form. Where on the form do I add this, and how do I do it?

Can anyone help me please?

Thanks
H

 

[Fizzio]

It is better not to store these calculations unless you need to retrospectively look at a previous price then you will need to set up a price audit table.

Calculate the values on a form. Use an unbound textbox and in the controlsource of txtTax, =[NettPrice] * (17.5/100)
txtGross = [NettPrice]+([NettPrice] * (17.5/100))

Are your calculations correct though? I've changes the values as I'm assuming that you are calculating VAT (I may be wayyy of mark here but I know nowt about finance, so excuse my ignorance and feel free to correct me)

 

[Hellfire]

More HELP!!

Thanks Fizzio, BUT...

I have allready created my table with the fields in it.

I want the VAT to be added in the field that I have created on my form. Where does the Unbound text box come in to the picture? I'm confused!

As for my calculations, I beleive that they are correct, I will be using the Gross Price to work back to the Nett Price.

Desperately awaiting your reply...!

Thanks,
H

 

[Fizzio]

It doesn't matter if you have already created the fields, as you can always delete them! It is generally better practice not to store information that you can calculate from other values unless you need to audit this data, then use a price audit table as I said otherwise calculate the details 'on the fly' either in the query supplying the form or on the form directly.

an unbound textbox is a textbox that is not linked to a field in a table.
therefore Gross price -> Net price

Gross Price you already have.
Controlsource for txtTax =[Gross Price] * (17.5/117.5)
Controlsource for txtNettPrice = =[Gross Price] -([Gross Price] * (17.5/117.5))

 

[Rich]

The problem with hardcoding VAT calculations is that it's a variable, you should store the rate applicable at the time of invoice not the amount, that can be calculated at any time as Fiz pointed out

 

[Hellfire]

Ok, I kind of understand, BUT...

Ok, thaks for all that, I know how do do it now.

The problem is, if I do not save the calculated tax in a field on my form, where will I find it again?

I want to use the db to log all the purchases I mak for my company. At the end of every month when I do my audit sheets, I want to get all the information I need from the db.

The information I need is:

Suplier - the total of all purchases from him
- the total VAT on all the purchases from him

I can get this very easily if it is stored in my table with the actual record.

Do you have a beteer suggestion of doing this. I don't know how to use a price audit table, so the best thing would probably be to calculate the value 'on the fly' on my form.

Please help me in doing this.

Thanks
H

 

[Fizzio]

As Rich was saying, VAT could potentially change in the future so it would be better to have a simple table with date and VAT Rate in it so that if you need to calculate an old invoice, the correct VAT rate will be applied. Similarly with the price of a product, you may keep the same product, but prices may change with time. This is what I mean by an audit table. Essentially, each time a price/VAT rate is changed, the date of the change and new price/rate is stored in a table which you can refer to in providing the correct price to the invoice.

You do not need to save the calculated tax field. If you calculated it once, you can calculate it again so it need never be stored.

Now you've expanded on your application a bit, you may want to look at your data structure. Only store information in a table that is relevant to that table. Look in the microsoft Northwind example database for how to structure your tables for a similar setup ie Clients, Purchases, Products etc.

You've opened up a bit of a can of worms here but if you use search here and the microsoft examples, it will make the process much easier for you.