Summing in an update query

  • Thread starter Thread starter L. Woodhouse
  • Start date Start date
L

L. Woodhouse

I have an invoicing module on the go that uses an order
table attached to an ordersubform.

I want to calculate the subtotal of all the lines in the
order on the ordersubform and update that to the subtotal
line in the order table.

What is the best way to do this?

I thought that I might use an update query but wasn't sure
of the calculation.
 
Dear Woodhouse:

Why would you want to total "all the lines in the order" and put that
total in the database? It is always a much better policy to not store
calculated totals like this.

If you store such a calculated total and you want to keep it
up-to-date then you must react to every change in the order. This
means insertions, deletions, and updates.

It is a common experience that it is much simpler to just add up the
total of the order whenever you need it.

So, the best way to do it is not to do it at all.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
You could use a DSum() expression to get the total from the related table,
but is it really necessary to store this value? Can you be absolutely
certain that you will always update this value, every time any row of any
order is added, modified, or deleted? Would it not be safer and better to
calculate the total when needed. (One of the basic rules of data
normalization is not to store a dependent value.)

To calculate the total of each invoice instead:
1. Create a query containing the Order and Order Details tables.

2. Depress the Total button on the toolbar. (Upper sigma icon).
Access adds a Total row to the query grid.

3. Drag the fields from the Order table into the subform.
Accept Group By in the Total row.

4. Drag the Amount field from the Ordr Detail table into the grid.
Choose Sum in the Total row.

If the amount is a calculation, you can type the expression into the Field
row of the query, and still choose Sum in the Total row. Example:
Amount: [Quantity] * [UnitPrice]
 
Back
Top