Automatic updating of a field

[Ciquo]

I am in the process of constructing a database, and have run into a
problem my limited know-how does not let me solve.

I have a field "line-item-cost" This field is the calculated product of
three other fileds from different tables. the "Price" of a product from
the PRODUCT table multiplied by "Quantity ordered" in the ORDERLINE
table then subtract a "Discount" a percent value form a CUTOMER table.
line-item-cost represents the final cost of a pirticular order. my
desired result is the orderline field to be automaticly calculated upon
the creation of an ORDERLINE record, which consists of the the
order_no, product_no and quatitiy ordered and the line-item-cost.

I have created a update query that does this quite easily, and
currently have it setup to automaticly run after the data entry form
for ORDERLINE is closed. this solution does not fuction the way i want
it to. first running teh querry affects all line-item-cost records,
when i only need it to affect an edited or created record, second i
want the query to run silently so to speak, withought the prompts
access gives you. lastly if discount is changed the query will affect
all records affected by that discount, new or otherwise, an update to
discount should only affect new records affected by that change not
prior ones.

Is there a way to acheive the results i desire with an update query or
is there a better solution to my problem. thank you.

 

[Rick Brandt]

I am in the process of constructing a database, and have run into a
problem my limited know-how does not let me solve.

I have a field "line-item-cost" This field is the calculated product
of three other fileds from different tables. the "Price" of a product
from the PRODUCT table multiplied by "Quantity ordered" in the
ORDERLINE table then subtract a "Discount" a percent value form a
CUTOMER table. line-item-cost represents the final cost of a
pirticular order. my desired result is the orderline field to be
automaticly calculated upon the creation of an ORDERLINE record,
which consists of the the order_no, product_no and quatitiy ordered
and the line-item-cost.

I have created a update query that does this quite easily, and
currently have it setup to automaticly run after the data entry form
for ORDERLINE is closed. this solution does not fuction the way i want
it to. first running teh querry affects all line-item-cost records,
when i only need it to affect an edited or created record, second i
want the query to run silently so to speak, withought the prompts
access gives you. lastly if discount is changed the query will affect
all records affected by that discount, new or otherwise, an update to
discount should only affect new records affected by that change not
prior ones.

Is there a way to acheive the results i desire with an update query or
is there a better solution to my problem. thank you.

What you should be doing is storing in your ORDERLINE table all three values
that are used as operands in the equation and then calculate that result
on-the-fly wherever you need it. The result is not saved anywhere.

 

[David F Cox]

you have to identify the record you want updated by its key, so the key
should be in the WHERE clause (or Criteria cell in query design view.)

 

[JK]

Ciquo,

The way I handle this is by using the AfterUpdate events in the relevant
fields in the order table.

When you select (or change) the product:

Price = Dlookup() in the product table
Discount=DLookup() in the customer table
and calculate Line-Item-cost

If you brought the price and discount into the order form, you simply
recalculate again when you change the quantity, or any other filed that
affects you line total, otherwise you Dlookup them again.

Regards/JK

 

[Ciquo]

you have to identify the record you want updated by its key, so the key
should be in the WHERE clause (or Criteria cell in query design view.)

 

[Ciquo]

How do i reference the current record ??

you have to identify the record you want updated by its key, so the key
should be in the WHERE clause (or Criteria cell in query design view.)