HELP NEEDED ASAP - Updating table row by row (ADP)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lemes_m@yahoo.com - 13 Jan 2006 12:46 GMT

Hi All!

I have very big problem and I can't continue my Work on my project
until i solve it.
Here is the situation:

TblCalculations

FldCalcId FldArticle FldQuantity FldPrice FldAvgPrice
1 1 10.000 0,20 0,20
1 1 15.000 0,10 X

Value X will be (10.000*0,20+15.000*0,10)/(10.000+15.000)=0,14

I need stored procedure, VBA code or anything else that will update
TblCalculations, FldAvgPrice with this formula, but it has to do
row by row because previous row value has to be included in
calculation.

Also, this calculation should be done only for articles which has some
FldCalcId value. I was thinking about FOR EACH Statement but I don't
know how to do it.

PLEASE HELP!

Regards,

Mirnes Lemes

Reply to this Message

teddysnips@hotmail.com - 13 Jan 2006 13:00 GMT

> Hi All!
>
[quoted text clipped - 24 lines]
>
> Mirnes Lemes

First of all, if it's an Access database there's no Stored Procedure.

If it's a SQL Server database, you could use a Stored Procedure.
Better still, you could use an INSERT (or UPDATE) trigger to calculate
the new value and update the column "on the fly".

However, I'm somewhat at a loss as to why you'd want to "store" this
information. The value of the column will change for every value of
fldCalcId as these are added/updated. It's a recipe for disaster, in
the shape of steadily decreasing performance.

If you need the data returned in your recordset, use the SUM and AVG
aggregate functions to return an aliased, calculated column.

Just my 02p.

Edward

Reply to this Message