Calculating on previous record

[stevebanks]

Hi all,

We are currently creating a Weekly Stock Sales an Intake Report. I'm trying to create a stock level forecast. I have created the forecast for demand, returns and cancellations. But I am having a little trouble with the stock level. For example:

¦ itemcode ¦ year ¦ week ¦ stocklevel ¦ demand ¦ returns ¦ cancelled ¦
¦ 1234x ¦ 2006 ¦ 16 ¦ 100 ¦ 20 ¦ 5 ¦ 3 ¦
¦ 1234x ¦ 2006 ¦ 17 ¦ 78 ¦ 12 ¦ 4 ¦ 3 ¦

I'm having trouble trying to get the 100 - 20 + 5 - 3 and end up as the stocklevel in the record after - all the way down the table.

I was thinking of something like this

SELECT fcast_details.itemcode, fcast_details.Date, fcast_details.[Week No], fcast_details.fcastdemand, fcast_details.fcastreturns, fcast_details.fcastcancellations,IIf([Week No]=1,[fcast_details].[fcaststocklevel],(select fcast_details.fcaststocklevel FROM fcast_details As stlevelfcast where stlevelfcast.[Week No]=((fcast_details.[Week No]-1)) And stlevelfcast.itemcode=fcast_details.itemcode))AS s
FROM fcast_details;

however after the first record, the stock level is blank so it returns a 0 everytime. If I could update the table every line first, would that do it???

Every little bit of help will be so much appreciated, I'm a little stump.

Hope I've given enough information,

Thanks

Steve
But the trouble is then the

 

[fneily]

Take a look at:

http://support.microsoft.com/?id=210504

 

[Golom]

I think that you have a referencing problem

SELECT fcast_details.itemcode, fcast_details.Date, fcast_details.[Week No], fcast_details.fcastdemand, fcast_details.fcastreturns, fcast_details.fcastcancellations,
IIf([Week No]=1,[fcast_details].[fcaststocklevel],

(select [COLOR=red]fcast_details[/color].fcaststocklevel FROM fcast_details As stlevelfcast where stlevelfcast.[Week No]=((fcast_details.[Week No]-1)) And stlevelfcast.itemcode=fcast_details.itemcode))AS s

FROM fcast_details;

I think the bit in red should be stlevelfcast. As it is, the sub-query is returning the same value for the fcaststocklevel field as the main query.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[stevebanks]

Thanks,

That gets the first 100 into the week afters stock, if i use an update query, will that update through the table and fill all the stock levels up then?
Steve

 

[Golom]

I'm not sure how to respond.

If you build the update query to do that then, indeed, it will do that.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[stevebanks]

By doing this:

UPDATE fcast_details SET fcast_details.itemcode = "itemcode", fcast_details.[Date] = "Date", fcast_details.[Week No] = "Week No", fcast_details.fcastdemand = "fcastdemand", fcast_details.fcastreturns = "fcastreturns", fcast_details.fcastcancellations = "fcastcancellations",  (select stlevelfcast.fcaststocklevel FROM fcast_details As stlevelfcast where stlevelfcast.[Week No]=((fcast_details.[Week No]-1)) And stlevelfcast.itemcode=fcast_details.itemcode)-[fcastdemand]+[fcastreturns]-[fcastcancellations] = "fcaststocklevel"));

I keep getting SYNTAX error in UPDATE statement... Any ideas????

 

[Golom]

The syntax for an UPDATE query is

UPDATE <table expression> SET Fld1 = Val1, Fld2 = Val2, etc.

It does not have a "FROM" clause.

I assume that you do not want to set your field values to text strings containing the field name as you appear to be doing.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[stevebanks]

And... using DLookup in the Update Query causes an error too: It says:

Dlookup(xxx,x,x,x,x,) is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

If i use a different query it works, but won't capture data from the previous week because it is obviously not there yet. Any suggestions???

Thanks


Steve

 

[lespaul]

just to clarify, you are trying to update the field StockLevel in a table with a calculated value? Why store a calculated value, breaks normalization rules.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[stevebanks]

Thanks guys,

I sorted the problem using a function:

Function updfcaststocklevel()

Dim rs As DAO.Recordset
Dim db As Database

Set db = CurrentDb()
Set rs = db.OpenRecordset("fcast_details")

rs.MoveFirst
For i = 1 To rs.RecordCount
If i = 1 Then
stklevel = rs("fcaststocklevel")
Else
stklevel = stklevel - rs("fcastdemand") + rs("fcastreturns") - rs("fcastcancellations")
End If
rs.Edit
rs("fcaststocklevel") = stklevel
rs.Update
rs.MoveNext
Next
MsgBox ("Done!")
End Function

All seems to work great!.... Thanks again!

 

[lespaul]

again, storing a calculated value usually means a breaking of the rules.....

 

[stevebanks]

but i need to use the value to create a stock history!!!

 

[lespaul]

so calculate it when you need it.

 

[stevebanks]

once a week it needs calculating an STORING so it can become a HISTORY... GET ME NOW?????