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.

Ciquo wrote:
> 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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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.
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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.
>

David F Cox wrote:
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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.
> >
> >How do i identify the record, specifcly the "current" record so that that record is teh only one affected by the querry???

How do i reference the current record ??

David F Cox wrote:
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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.
> >
> >