Making one column the sum of other two

[capsula4]

Hi!

I have a table called "products"

One column is "Unitary Price", another one is "Quantity" and another one is "Tax"

And I want another column called "Total" that shows:
Unitary Price*Quantity+Tax*Price*Quantity

Is this possible to be made?? I'm a bit newbie to MS Access, so try to explain as most detailed as you can! For example, if I have to write [Unitary Price] between brackets... I tried doing this on the "Default Value" field of the column but I couldn't make it.

Thank you!!
Luis

 

[GaryPanic]

yes you can do this

first - try to steer away from names with spaces in it unitary price should be Unitaryprice otherwise you have to bracket names [unitary price]

now is this in a form or a qry ??
in a form it would be in the after update properties of the quantity field
so it would look something like

[unitary price] + [tax] * [quanity] = [total] on the assumption that its a flat tax for all items

this assumes that you have fields on your underlying table called [xxx] everything in brackets

 

[GaryPanic]

look at the customer form in this and change the money value you will see that it changes the commission value -= two box's below it ...

 

[capsula4]

Thank you for your answer but stil...

now is this in a form or a qry ??

lol, that's what I can't find out!!

Since I'm pretty new to Access (I'd been working with Excel), I tried to asign a Default Value to a column using other columns but I couldn't. If this can be possible I really prefer this rather than using forms since I don't really know how to make them, I just couldn't find the math operation on the file you attached.

A scheme of what I have:

Name of Field | Type of Data | Description
---------------------------------------------
UNIPRICE | Number |
QUANTITY | Number |
TOTAL | Number |

And I want Total to show "uniprice*quantity" in the table itself, without using forms or qry... in case its possible using default value property of fields.

 

[Brianwarnock]

I think that you will be told by experienced database designers that you should only store calculated fields in special circumstances, you can do this calculation whenever it is needed in a report/form/query

Brian

 

[capsula4]

Thanks for the advice Brianwarnock! I will think about it! Althought I still would like to know how to store a calculated field in a table.

In case no one of you know how to make it, how can I make a calculated field in a report? I just can make reports using the report maker...

 

[Brianwarnock]

Reports are usually based on queries as they provide all kinds of additional functions over doing theh report directly off a table, then the calculation is done in the query.

Brian

 

[Brianwarnock]

Likewise any input to a table is normally done via a form and the calculation you are asking about would be done there if you must store it, which normally you shouldn't as stated earlier.

Brian

 

[GaryPanic]

Brian has answered this very precisely .. in 90%-95% of cases you don't store calculated figures (as with all rules there are a few exceptions - I don't think that you are in this postiion at the moment)

get the fields right in your table and get your form to do the work ....

 

[capsula4]

Thank you Gary and Brian for the answer!! I could finally understand how to use it!