How to make accumulated data correct all the time?

How to make accumulated data (e.g., total amount, balance) correct all the
time? Many years ago, I did an order system and found the total amount was
not right sometimes. The order system is pretty complicated and itâ??s not easy
to find where it is wrong. I know I can add transaction log for this field.
But I donâ??t think thatâ??s the best way. The original code was something like
TotalAmount= TotalAmount-OldItemAmount+NewItemAmount. I changed it to Select
Sum(NewItemAmount) as TotalAmount. That means recalculate it every time. And
then the total value has never been wrong. The biggest problem for this
method is performance. We may not able to recalculate everything, say stock
on hand. The biggest problem is balance for every account in general ledger.
Do you have any ideas to make them correct all the time?
TIA.

Cindy
Tue Oct 11 12:26:40 CDT 2005

Hi William,

Keeping a total value in a row where it depends on other values is tricky,
and, as you've discovered, it's easy to get wrong values.

When you use SQL to get the total (as for a report), performance depends on
good indexing. Do you have indexes on your Account field?

Here's one idea, but it depends on denormalizing the data a little: keep the
totals in a separate table that's 1-1 to the Accounts table (and Stock
table). Use Insert/Update/Delete triggers on the line items table(s) to
update the values in the Totals table. You can also run a batch job
overnight to recalculate the totals, just in case they're wrong.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden


"William" <William@discussions.microsoft.com> wrote in message
news:AE9ECCF1-7721-4D05-8BBC-86595A2BE7CE@microsoft.com...
> How to make accumulated data (e.g., total amount, balance) correct all the
> time? Many years ago, I did an order system and found the total amount was
> not right sometimes. The order system is pretty complicated and it's not
> easy
> to find where it is wrong. I know I can add transaction log for this
> field.
> But I don't think that's the best way. The original code was something
> like
> TotalAmount= TotalAmount-OldItemAmount+NewItemAmount. I changed it to
> Select
> Sum(NewItemAmount) as TotalAmount. That means recalculate it every time.
> And
> then the total value has never been wrong. The biggest problem for this
> method is performance. We may not able to recalculate everything, say
> stock
> on hand. The biggest problem is balance for every account in general
> ledger.
> Do you have any ideas to make them correct all the time?
> TIA.
>
>

Paul
Tue Oct 11 12:41:17 CDT 2005

You really have no other choices than to either store the amount (fast, but
subject to error) or recalculate every time (accurate, but slow). Storing
the amount isn't really so bad if you keep it updated. And recalculating
every time isn't so bad either, if you avoid doing recalculations that you
don't really need.

There are a few tricks you can try, though. Instead of using the table
directly, you could use a view and include in that view a calculated field
that contains what you want. That's a variation on the calculate-on-demand
theme, but easier to use and perhaps faster. Something like this:

CREATE SQL VIEW myview AS ;
SELECT olditemamount, newitemamount, newitemamount - olditemamount AS
totalamount ;
FROM sometable

Of course if you're updating data, you'll need to handle view updates, which
are more trouble (although more reliable in multi-user situations).


A variation on the stored-value theme would be to put an update trigger on
the table, so that whenever olditemamount or newitemamount get changed, it
automatically updates the field totalamount. That will make each update a
little bit slower, but retrieving a large number of totalamounts would still
be fast.




"William" <William@discussions.microsoft.com> wrote in message
news:AE9ECCF1-7721-4D05-8BBC-86595A2BE7CE@microsoft.com...
> How to make accumulated data (e.g., total amount, balance) correct all the
> time? Many years ago, I did an order system and found the total amount was
> not right sometimes. The order system is pretty complicated and it's not
> easy
> to find where it is wrong. I know I can add transaction log for this
> field.
> But I don't think that's the best way. The original code was something
> like
> TotalAmount= TotalAmount-OldItemAmount+NewItemAmount. I changed it to
> Select
> Sum(NewItemAmount) as TotalAmount. That means recalculate it every time.
> And
> then the total value has never been wrong. The biggest problem for this
> method is performance. We may not able to recalculate everything, say
> stock
> on hand. The biggest problem is balance for every account in general
> ledger.
> Do you have any ideas to make them correct all the time?
> TIA.
>
>