updateing a field in a table

[etcrier]

I am having trouble getting records selected from a table that I need to have a price field updated with an addon pct.
basically pprice=paddon_pct* pcost.

MY TABLE named parts
ppartno
pdescrip
pprice
paddon_pct
pcost

my update query is from table parts
1st and only column defined
field = pprice
table = parts
update to = parts!pprice
criteria = IIf(([parts!pprice]=0),0,[parts!pcost]*[parts!paddon_pct])

when I run
i get no records selected and no pprice field updated?
it says i have selected 0 rows and nothing is updated
what stupid thing am i doing wrong?


thanks

 

[Darrylles]

Hi etc,

Post the SQL.


ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[etcrier]

I do not uderstand how to do waht you said at all.

tbanks

 

[Darrylles]

Hi,

In query builder: right-click the form title and select 'SQL-View. This will display the SQL code that the builder has produced for you.

Hang-on, maybe I have this wrong.

How are you attempting to 'get records from a table'?

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[lespaul]

Additionally, storing a calculated field breaks the rules of normalization (see fundamentals document linked below for more on normalization). If you can calculate pprice by multiplying paddon_pct and pcost then those are the only values you need to store, you can calculate pprice on the fly.

One other thing, if you have an item that costs $10 and the addon_pct is 15% your formula above (pcost * paddon_pct) would result in a price of $1.50 (10*.15); instead what you want to do is multiple $10 by 1.15 to get a price of 11.50 (10*1.15).



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[etcrier]

Lespaul, Darrylle
I just have a simple 1 table form for a parts inventory just a few fields. but i need to store the addon % and the calculated price. the user can enter an addon % or just enter the pprice he wants over riding the calculated price. a very common concept in legacy software. right now I show what the calculation is but they have to manually enter it into the price field. i want the results to be a default type of value then user can over ride if needed.

BTY addon does mean 115% =15% margin on cost making A profit of 15% of total price.

thanks very much for your time in this.

david

 

[Darrylles]

Etc,

Lespaul is absolutely correct, but if you want a result however incorrectly implemented - you'll do it.

The logic that you are seeking is simple.
Please list the textbox names in your form - as I'm not sure where the default 'price' is coming from.

ATB

Darrylles


Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[etcrier]

i am nit sure what you mean about "lespaul is absolutely correct" we do agree? 115% is correct thats ewhy its called add_on

here is the table info you ask for

MY TABLE named parts
partno
descrip
price
addon_pct
cost
textbox with calculated field =addon_pct*cost

i just need this "textbox" to copy to "price" as a default but can be over ridden by user by entering a price

thanks for your time

 

[lespaul]

i am nit sure what you mean about "lespaul is absolutely correct"

he's referring to this portion of my previous post:

storing a calculated field breaks the rules of normalization (see fundamentals document linked below for more on normalization). If you can calculate pprice by multiplying paddon_pct and pcost then those are the only values you need to store, you can calculate pprice on the fly.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[etcrier]

Leslie,

in this situation it not applicable because we want to keep track of previous calculations so historical data is not changed. as prices change we do not wnt previous records change even though margins, costs go up and down.

thanks for you input though

david

 

[lespaul]

what you are saying makes no sense. What you sell an item for is its price....what you pay for an item is its cost. If you enter a new item with a new cost you'll get a new price using the addon_pct.

What is comes down to is that storing a calculated field breaks the rules of databases.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[etcrier]

Leslie,
i understand what you are saying, however in this situation its different, so I will just figure out another way to do what i need since i am unable to explain something in your terms.

here let me try again. just thought of this idea!

In this app. the person puts in a percent of say 1.25 for a 25% addon to cost. I then write that value into the price field of the same record or table item.

later in this app (another form) I want to change the price to another value that is not based upon the percent any longer and i want the orginal percent to stay in the addon field. you might say this would be an abritrary override.
so I need a simple way to
enter a value in field A and B and perform a calculation on A & B and write results into C. all on a form. then later with out changing A or B enter some other value into C from another form.

thats the best i can do to explain.
BTY I have done database programming since 1962 in dozens of languages and this is very simple. I am just having a tought learning curve in Access.


thanks again
david