access how do i create a formula?

[nawara]

hey everyone..thank for making such a helpful site

i am going to tell you the backround and then proceed in telling you my problem

i studied access about 2 years ago so.. my knowledge is a little limited

ok basically i have created a table for the suppliers which we use in our company...
one of my columns is called Sub Total , the following column is called VAT and thus Amount Including VAT
i was wondering if when entering the Sub total and then clicking the VAT column.. is there a way in which acess can automatically calculate the VAT and also if there was a way in which acess can calculate the amount including the VAT
i am thinking there should be a way such as a formula or a rule which we can enter..
please let me know as soon as possible if anyone knows the answer to my problem
thank you
regards Noor

[hjozinovic]

Hi Noor,

Usualy calculations are used in Queries.
try to make a query based on your table and then you can calculate totals in there.
Also try to search access help for 'calculated fields' or 'calculated controls'

regards,
h.

[nawara]

Hi Noor,

Usualy calculations are used in Queries.
try to make a query based on your table and then you can calculate totals in there.
Also try to search access help for 'calculated fields' or 'calculated controls'

regards,
h.

Hi H,
why thank you very much for ur reply H,
ok if i was to create a query then wat would i need to write in the criteria?
i am assuming along the lines of sumthing like this =[sub total]*[100]/[17.5]?
and also if i was to write that formula... would it automatically work it out for me everytime i enter a new supplier?

[hjozinovic]

Hi H,
why thank you very much for ur reply H,
ok if i was to create a query then wat would i need to write in the criteria?
i am assuming along the lines of sumthing like this =[sub total]*[100]/[17.5]?
and also if i was to write that formula... would it automatically work it out for me everytime i enter a new supplier?

hi N.

You really are 'rusty', aye? :-)
No problem, I'll be glad to help you.

I attached a sample for you here (at the bottom)
and for your secon q. yes it's applied to all new records every time you add or change something.... like excel sheet.

[nawara]

Hi H,
why thank you very much for ur reply H,
ok if i was to create a query then wat would i need to write in the criteria?
i am assuming along the lines of sumthing like this =[sub total]*[100]/[17.5]?
and also if i was to write that formula... would it automatically work it out for me everytime i enter a new supplier?

hi N.

You really are 'rusty', aye? :-)
No problem, I'll be glad to help you.

I attached a sample for you here (at the bottom)
and for your secon q. yes it's applied to all new records every time you add or change something.... like excel sheet.

:O:O:O bloody hell am so thick!!!!
is that all it was
seriously thank you soo much
:):) you r soo sweet
[flower for u]

[nawara]

ryt am stuck again
ufffffffff!
right wen i am writing 17.5% and tabbing onto the next colum which is VatAmount... its not calculating it:S:S:S:S:S
does this site have an instant messanger i need 24 hour support due to my stupidity?
lol
thanks

[Stewart Ross]

Hi Noor. Problem is that you have included the calculated field in your table - the total including VAT - which is not the normal way to do it. Rule is not to store fields which are derived by calculating values from other fields.

A line subtotal can be calculated from the VAT (tax) rate stored, along with the unit price of the goods and the quantity: SubTotal: Qty * UnitPrice* (1+VATrate). Similarly for the VAT subtotal: VATAmount: Qty * UnitPrice * VATrate.

If you do want to store these values in your table as you are doing at present (for some reason) the values cannot change until you update the subtotals using the After Update events of the VAT rate, quantity and unit price controls on your form, a less easy option (though hardly difficult).

-Stewart

[nawara]

Hi Noor. Problem is that you have included the calculated field in your table - the total including VAT - which is not the normal way to do it. Rule is not to store fields which are derived by calculating values from other fields.

A line subtotal can be calculated from the VAT (tax) rate stored, along with the unit price of the goods and the quantity: SubTotal: Qty * UnitPrice* (1+VATrate). Similarly for the VAT subtotal: VATAmount: Qty * UnitPrice * VATrate.

If you do want to store these values in your table as you are doing at present (for some reason) the values cannot change until you update the subtotals using the After Update events of the VAT rate, quantity and unit price controls on your form, a less easy option (though hardly difficult).

-Stewart

right i see..
and were would i do these sums exactaly?
sorry if am buggin you with a million questions
though i really appreciate your replies

[Stewart Ross]

As hjozinovic said in post 2, these calculations are done in Access queries. Base tables are where the data is stored, and should never be accessed directly by users. As developers we can define queries, in which we can include additional calculated fields to do things like calculate totals etc., and we then base user forms, reports and the like on these queries. You have had sample databases provided by posters - you should look at the examples provided, and in the one supplied with Access (the NorthWind example database) which is very useful for showing how to develop simple forms, queries and reports for users.

-Stewart