Access Update Query

  • Thread starter Thread starter Ron
  • Start date Start date
R

Ron

Hi

I have two tables in my database one has style numbers the other purchase
orders. There is a one to many relationship styles to purchase orders.

I am trying to add the quantity fields from the purchase order table and
have it inserted the total into the related field in the style table.

I have been trying to use an update query but can't get a result.
 
I have tried in my form as well but can't get the calculation to work. Any
suggestions on how the calculation should read?
 
Hi

I have two tables in my database one has style numbers the other purchase
orders. There is a one to many relationship styles to purchase orders.

I am trying to add the quantity fields from the purchase order table and
have it inserted the total into the related field in the style table.

I have been trying to use an update query but can't get a result.
Click to expand...

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

Typically you would use a Form for Styles (the "one" side table) and a Subform
for POs (the "many"); if the PO table has a Quantity field you can put a
textbox in the subform Footer with a control source

=Sum([quantity])

to total the quantities for that style.
 
Back
Top