Calculating on calculated values

[Guest]

I have a 3-section quote form, each section containing 5 cost lines.
Each line is calculated (item x unit cost) = cost.
Then I have a section cost subtotal for each section, so this needs to add
up the 5 preceding calculated amounts.
Finally there's a grand total for the quote, which needs to add up the 3
section subtotals.
All of these values need to be stored in the underlying quotes table. (I
know all about the dangers of this but for this particular situation it is
something I HAVE to do)
It's fine doing the individual cost lines, those store without any problem
using the AfterUpdate event on item and unit cost (Me.Cost =
Me.Item*Me.UnitCost).
However, try as I may, endlessly, I CANNOT get the subtotals or total to
store in
the table.
Is it not possible to do calculations on calculated amounts??
This is really urgent as I have to demo the system next week and have lost
loads of time - I had no idea this particular bit would be so
time-consuming!!!
Many thanks
CW

 

[Guest]

If we accept the fact that it is necessary to store the subtotals and totals
as values in a table (I'd be interested to hear the rationale behind this)
then you should store them not in the same table as individual cost lines,
but in separate related tables. This would then guard against update
anomalies which could otherwise give rise to inconsistent subtotal and total
values if the values were repeated per cost item, though it would still leave
the door open to update anomalies which might result in the subtotals and
totals being inconsistent with the individual cost items values (but you say
this is necessary).

This means you will need to identify foreign keys in the cost lines table
which can reference the subtotals and totals table's primary keys. These
might well be multiple columns.

Having identified the keys you have a number of choices of how to update the
subtotals and totals tables:

1. You could execute 'append' queries to insert the aggregated values from
the cost items tables into the subtotals and totals tables when all 3
sections in the form have been completed. If you also need to cater for
editing the cost items subsequently the code would first need to determine if
the relevant row already exists and, if so, update rather than insert a row.

2. You could execute an 'append' query after entering the rows for each
section to insert the aggregated values from the cost items tables into a
single row in the subtotals table, and then do the same to insert the values
into the totals table when all three sections have been completed. Again you
might need to cater for subsequent edits of the cost items rows.

3. You could execute an 'append' query after entering the first row for
each section to insert the aggregated values from the cost items tables into
a single row in the subtotals table and into the totals table after entering
the first row for the first section, and then update queries for subsequent
rows per section. The code for updating would also cater for subsequent
edits as it would update the tables if the relevant row already exists.

If at present you are trying to set the values of columns in a single cost
items table in the same way as you are setting the value of the Cost column,
by a simple expression, then that won't work as you need not simply to set
the value of columns in the current row of the table, but the values of the
columns in multiple rows, so even putting everything in a single table it
would still be necessary to execute an update query when inserting the second
and subsequent cost items rows for the current quote.

Ken Sheridan
Stafford, England

 

[Guest]

Ken -
Glad you popped up with a reply - it's very quiet today (understandably).
Thank you so much for all that info and advice, really most helpful.
I think I'll do a couple of little test mdbs to see which route works best
for me.
Now I'm beginning to see why people don't store calculated values - not only
is it obviously dangerous, but it's a heck of a hassle, too!!
Thanks again
CW
Beaconsfield, England