Calculating new field

[dbmanalser84]

Hi I'm building database for a car shop. One of my table (Cars) has these two fields in it intModelPrice (which stores factory price of a model) and intPriceWithTax (which value should mathematicaly be intModelPrice+[(intModelPrice)*(18/100)]).

Now, can I set my default value of intPriceWithTax to be calculated by this formula and how? I tried some combinations but I got various error messages.

I red Tips section on your site and saw that default value can't be calculated. I know how to get what I want in the select query but I want this table field (intPriceWithTaxt) to be acctualy filled with number value each time I enter a new record in this table (for a new car model). How do I acomplish this? Thanx.

P.S. - I searched the forum for answer but I had no luck.

 

[KenHigg]

a. Don't store this but calculate it on the fly whenever you need it
b. Wouldn't it be easier to use .18 ?

 

[dbmanalser84]

Yes but...

a. Don't store this but calculate it on the fly whenever you need it
b. Wouldn't it be easier to use .18 ?

a. I need to show the calculated value because the whole database is for my school project.

b.Yes, thanx

 

[KenHigg]

a. Calculating this on the fly as suggested would demonstrate an advanced methodology ( I would think )

 

[gemma-the-husky]

you cant set a default value for the tax inclusive price, based on another field in the table.

what you have to do is when you enter the tax exclusive price (in a FORM), (because users shouldnt see or use tables directly) is calculate the tax inclusive price and store it at that point, in the BEFOREUPDATE event of the form

now because users shouldnt see tables, they dont really know how you are achieving things. and really, all you should be saving in your table is

a) the base price
b) the tax rate (and some would say, dont even store this, as you can generally look this up in another table when you need it ... and what if it changes before you sell the car, and youve stored an out of date tax rate ...)

the point is, if you have the base price and the tax rate, you can work out the inclusive price when you need it

really, whatever your teachers say

BUT if they want you to store both, then dont argue, just work it out and store it, with the BEFOREUPDATE event

 

[dbmanalser84]

Thanx

Thanx for the answer Gemma. Now when I now I canot do it in the table directly I wanted to ask one more thing:

Can I do it via Append Query, and what would be the right syntax for doing this?

 

[neileg]

No, you could do it withan update query, but it is better to bind the control in the form to your intPriceWithTax field. The the After Update event of the control bound to intModelPrice you put

[Name of the control bound to intPriceWithTax] = [name of the control bound to intModelPrice]*1.18

However, I strongly agree that you should not be storing this value, you sould calculate it when you want to use it. The calculation can be in a form or query or report.

 

[Rich]

Surely it's the net price and tax rate at the point of sale that should be stored?

 

[dbmanalser84]

Yes

Thank you guys for your answers you've been very helpfull.

The reason I'm asking could it be done by queries is that in my fianl exam from Databases we are only covering the ground up until the queries (its not much but its fine by me ), I understood it would be best done by Forms, but I don't need to build forms for my work, and since the Queries are oly thing I should be working with, I'm exploring all possibilites how can something be done because I want to show as much as possible via queries It doesn't matter its better to only show something and not store it, I need to store it in order forit to bee seen on Paper work because the teacher looks at paper works not am acctual database on computer, so if I don't put it somewhere to be seen He won't see it. Thanx again.

 

[neileg]

How can you assess a database if you don't look at it. Teachers, hah!

 

[mlai08]

Why don't you create a selected query based on the table and create a new field - PriceWithTax:[intModelPrice]*1.18

When ever you run this query, field PriceWithTax will calculate the total price.

Rule of thumb is never try to enter calculated fields in tables.

Mike

Hi I'm building database for a car shop. One of my table (Cars) has these two fields in it intModelPrice (which stores factory price of a model) and intPriceWithTax (which value should mathematicaly be intModelPrice+[(intModelPrice)*(18/100)]).

Now, can I set my default value of intPriceWithTax to be calculated by this formula and how? I tried some combinations but I got various error messages.

I red Tips section on your site and saw that default value can't be calculated. I know how to get what I want in the select query but I want this table field (intPriceWithTaxt) to be acctualy filled with number value each time I enter a new record in this table (for a new car model). How do I acomplish this? Thanx.

P.S. - I searched the forum for answer but I had no luck.

 

[dbmanalser84]

Thnx

Why don't you create a selected query based on the table and create a new field - PriceWithTax:[intModelPrice]*1.18

When ever you run this query, field PriceWithTax will calculate the total price.

Rule of thumb is never try to enter calculated fields in tables.

Mike

I'll do that, I just wanted to explore all possibilities for future refference. Thank you all for answering.