Query to calculate with previous calculated field 1

[marijonas]

Hi,
I trying to create a quary that will calculate an adstock value. However in the adstock value one of the "components" is previous adstock value. How should I write a query to take the get the previous calculated adstock value.
My table with data looks like that:
Week, Value
Then I have a form to that will have per cent value entered.
Calculation for Adstock is something like this: Value from table * per cent value from form + adstock for previous week.

Thanks!

 

[DrSimon]

I think we need a better definition of 'the previous calculated adstock value'. Is this a previous calculation on the same record or one based on another 'historical' record. If the latter, how do you identify that record?

 

[Crowley16]

erm, you would need some sort of recursion for this and that's not possible...

what you should do would be to calculate the adstock and store it in a table somewhere, and then use a query to calculate the newest adstock

--------------------
Procrastinate Now!

 

[marijonas]

Yes it would be some kind of recursion. Such calculation seems to work with no problem in Excel and I would like to implement it in Access.

 

[Crowley16]

access <> excel...

you can nest sql statements into one another, but you can't do recursion...

so the only way you can do this with sql would be to dynamically generate the statement with as many levels of recursion you would need, the entire thing would get very complicated very fast and is NOT worth it...

Just make a new column somewhere, or even a new table, and store the adstock values in there as they are calculated...

--------------------
Procrastinate Now!

 

[marijonas]

The best solution would probably be to calculate adstock for every week and to store it to table after each week's calculation. Could anyone suggest how to save those values best? Thank you all for help!

 

[marijonas]

How to make it SQL calculate and save Adstock for one week at the time and them move further? Is i possible or do I have to calculate it manualy or something.

 

[Crowley16]

create a table with fields adstock, date
or add adstock to an appropriate table, i.e. one containing a date field that relates to when the adstock was generated...

then to calculate adstock, you can just do:
(someCalculation) + (select adstock from tbl where date > #someDate#)

to add the adstock value, use an insert query:
insert into tblName(field1, field2) Values(value1, value2)

--------------------
Procrastinate Now!

 

[marijonas]

Ok, I will try it. I hope it will work for every week.

 

[marijonas]

i am still a little confused as I don't get it to work. Shall I create two different queries - one with calculations and another one with the insert or can I do it in one query.
It looks like that now:
I gave TRPtable that has WEEK, TRP, ADSTOCK
I make have to calculate and add ADSTOCK for every week.

Thanx again!

 

[marijonas]

Hi!
I haven't found any working solution yet. How to make query calculate then save the record one by one. Because I need the previously calculated record for the next row.
I have no solution. It seems like Access is not cabable to make a calculation that is very simple for Excel.

 

[marijonas]

The problem is that I need to calculate adstock value for few weeks. It is just a part of a longer calculation in a model.
For exampel I have the number of weeks with some values in a table. A user enters per cent value in a form and then the adstock level for every week has to be recalculated. The problem is that in that calculation I need the previous value. Maybe it is better to use VBA code?

 

[Crowley16]

yes, from the sound of it you would need to use vba code.

you would have to open the table in question with a recordset, and then step through each record (I'm assuming you've ordered the records already) one by one and with each record, work out the adstock value, and save that value...

it sounds complicated, but it's not too bad, something like:

private sub calcVal()
   dim rs as dao.recordset
   set rs = currentdb.openrecordset("SQL string to retrieve relevant records")

   with rs
      .movefirst
      do while .eof = false
         adstock = someCalc + tmpAdStockValue
         
         .edit
            .fields("adstock") = adstock
         .update

'storing current adstock value to be used for calculating next value
         tmpAdStockValue = .fields("AdStock") 

         .movenext
      loop
   end with
end sub

that's just off the top of my head, and will need lots of tinkering but should give you a good idea of what to do...

--------------------
Procrastinate Now!

 

[marijonas]

Thanks, that is exactly what I was thinking. I will test it right away.
Thanks!

 

[marijonas]

I still get some errors with fields. I have tested your code, then I tried to change something. I will continue tomorrow. It is something with field names.

Here is the code:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT Week, TRP, AdstockTRP FROM tblTrps")
Dim adstock As Variant
Dim tmpAdStockValue As Variant


With rs
.MoveFirst
Do While .EOF = False
adstock = rs![Week] - 1 + tmpAdStockValue

.Edit
rs![AdstockTRP] = adstock
.Update

'storing current adstock value to be used for calculating next value
tmpAdStockValue = .Fields("AdStock")

.MoveNext
Loop
End With

 

[marijonas]

By the way it is just an example of calculation for test not the real calculation for adstock.