By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,079 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Jan 13 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a

le*****@yahoo.com wrote:
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


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

Jan 13 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.