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

[lemes_m]

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

 

[Joseph Meehan]

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

I suggest you build a query and use use your calculation there. Don't
store calculated figures. Normally storing those figures is a bad idea.
First Access works well re-calculating them when needed and second if a
factor in the calculation changes and you don't remember to re-run the
calculation the result will be wrong. Using a query (or a calculation on a
form or report) eliminates that problem

The only time that you should really store a calculation is when some
factor may change and you want to keep the original result.

 

[lemes_m]

Thanks for your response.

I understand the solution which you propose but there is a problem (I
think that I didn't explain well)

User enters data in another table TblSupInvoices, not TblCalculations.
I need to sum data for one article from TblCalculations and then add
new quantities and prices from TblSupInvoices and it is not a problem
if I have one article only one time. But problem happens when I have
one article more than one time. If we take table values which I showed
in my question it will result with following:

DSum - Article: 1, Quantity: 25.000, Amount: 3500, AvgPrice: 0,14

User enters new record, RECORD1: Article 1, Quantity: 10.000,
UnitPrice: 0,30 then
avgPrice will be: 0,1857 and it is correct.

But if user enters one more record, RECORD2: Article 1, Quantity 5.000,
UnitPrice 0,20
and when storing those records, first I have to store RECORD1 and then
make new DSUM and then calculate and store RECORD2.

I know it is litle bit complicated explanation but I hope someone can
help.

Regards,

Mirnes Lemes

 

[Joseph Meehan]

Thanks for your response.

I understand the solution which you propose but there is a problem (I
think that I didn't explain well)

User enters data in another table TblSupInvoices, not TblCalculations.
I need to sum data for one article from TblCalculations and then add
new quantities and prices from TblSupInvoices and it is not a problem
if I have one article only one time. But problem happens when I have
one article more than one time. If we take table values which I showed
in my question it will result with following:

DSum - Article: 1, Quantity: 25.000, Amount: 3500, AvgPrice: 0,14

User enters new record, RECORD1: Article 1, Quantity: 10.000,
UnitPrice: 0,30 then
avgPrice will be: 0,1857 and it is correct.

But if user enters one more record, RECORD2: Article 1, Quantity
5.000, UnitPrice 0,20
and when storing those records, first I have to store RECORD1 and then
make new DSUM and then calculate and store RECORD2.

I know it is litle bit complicated explanation but I hope someone can
help.

Regards,

Mirnes Lemes

If I understand correctly you will need to identify each set of data,
maybe with a unique ID number. That can be set up using autonumber or if
there is some sort of existing unique record number already that will work.
Note: Autonumber normally should be used only by Access as it may give good,
but confusing numbers and when people start looking at the numbers they may
confuse them and look wrong. The tables should be linked by this number
and all the work can be completed on a single form or form/subform using
both tables and with the user being unaware that there are two or more
tables involved.

I am not sure but it still appears that there may not be a need for the
calculation result being stored. It appears to me that they can be computed
whenever needed.