Loop and Recordets

[jagstirling]

I am trying to loop through some records (in sequential order based on a field 'Period') and perform some calculations before moving onto the next period and perfoming the same setof calculations.

I have attached the logic I am trying to follow Logic.xls and an attempt that I have made Model.mdb (I like to try before requesting help).

I would be grateful of some code or assistance.

I am sure I should be using a recordest with a loop but despite trying I am getting no where.

 

[RainLover]

It appears that you are attempting to store calculated values.

If so then this method will lead to problems. If the underlying data changes then your stored value become incorrect.

It is best to do the calculation when you need to show the result. Know as Runtime.

It would be best to redesign your structure.

 

[jagstirling]

Thanks ...... but I'm sorry that doesn't mean a great deal to me !

Can you elaborate please ? I am keen to learn.

 

[RainLover]

Ok

If you sell 5 Apples for $2.50 each you store the number of apples sold and you also store the sale price. But you do not store the total cost which in this case would be $12.50.

When you Open a Form or Report you would have a Formula that says TotalPrice:[NumberSold] * [UnitCost]

So if someone down the track changed the number sold or the unit cost the result would always be correct.

Please come back to me if you require further clarification.

I am happy to help.

 

[jagstirling]

Ok, great. now I follow you.

However, in my example (and I refer to the Logic.xls attachment) the Closing number fo one period becomes the ope ning number for the next period.

That is why I was trying to calculate the values, because the values in one Period of time effect the values in the next (ie. Closing value in period 1 becomes the opening value in period 2).

I have toyed with a DLOOKUP or DSUM function but need to reference 2 criteria.

For example, to get the 'Opening' value for period 2 I need to look at the Period 1 and the Action 'Closing'.

Thanks for your help, but I'll struggle on to write some code ...... might take me forever but I'll get there !

 

[midmented]

Are the fields you are using to do your calculation in the same table as the field you want to write the answer to? Would an update query work in place of a VBA loop? The Model.mdb posted will not open for me to view.

Is it possible for you to post the table you want to perform the loop on?

 

[midmented]

I put together a little example of what I think you are talking about doing. You'll have to add a little more to handle the first record in the set.

Function CalcClosing()
Dim rs As Recordset, strSQl As String
Dim intClosing As Integer
strSQl = "SELECT * from tblMyExample ORDER by Opening;"
Set rs = CurrentDb.OpenRecordset(strSQl)
rs.MoveLast
rs.MoveFirst

Do While Not rs.EOF
    intClosing = rs.Fields("Opening")
    rs.MoveNext
        If rs.EOF Then GoTo Exit_Loop
    rs.Edit
    rs!Closing = intClosing
    rs.Update
        Loop
        
Exit_Loop:
    rs.Close
    Set rs = Nothing

End Function

Open the module and use ?CalcClosing in the immediate window to run.

 

[jagstirling]

I am open to any suggestions regarding the structure ......

The Logic.xls file explains the process I am trying to follow.

Fyi, the table I cuurently have has the following fields (and "Options");

FIELD : PERIOD
VALIDATION RULE : 1, 2, 3, 4, 5 ........ etc

FIELD : ACTIVITY
VALIDATION RULE : Opening, Add, Take, Closing

FIELD : VALUE
VALIDATION RULE : << numeric values >>

Thanks for your input, is that enough for you ?

 

[namliam]

It appears that you are attempting to store calculated values.

Ned Is right...

DONT do this, your closing is the result of Opening + Add - Closing.
It IS a calculated value (thought hopefully static), it should not be stored in your table anywhere at anytime. Instead simply calculate it as per the earlier formula

 

[RainLover]

Again I suggest that your approach is incorrect. You are storing needless and possibly incorrect information.

Do not store calculated values is one of those golden rules.

I am sure you will find a work around with the help of others here who do not understand the concept.

I wish you well with the project.

 

[midmented]

I only posted the method in which I have done this in the past. I've only used this method to hold calculated data in a temporary table or do global record updates. NOT for storing calculated values permanently. I will agree that the above situation should not store calculated data. I guess my brain was wrapped around "method" and not "situation". Sorry.
I don't agree with not storing calculated values entirely though. This depends on the definition of "calculated values". There comes a point when older records need archived and a new balance needs to be brought forward. If I tried to calculate a value from 1,000,000 records I might get my answer sometime tomorrow.

 

[Rabbie]

See this link from Allen Browne's webste to see when it is OK to store a calculated value and when it is not.

 

[jagstirling]

I will defer to superior knowledge and experience regarding the calculation of numbers although I cannot say I agree with it !!!!!

If the opening and closing were fields then I could understand that calculating woudl be the best option, but they are not. WOudl it help to know that there may be up to 250 periods and that these values will in turn be used to drive other calculations ... ?

Can somebody maybe help me with some of the logic around the construction of my table then ?

I currently have three fields (please see my original attachment model.mdb)

PERIOD
Containing the period number

ACTION (to classify the type of record ............ it might be easier to understand that this could relate to an inventory balance forecast situation, where items are Added and taken from a store).
Choices are from;

• Opening
• Add
• Take
• Closing

INPUTVALUE
This is the value associated with the ACTION

**************************************************

Should I possibly have five fields;

PERIOD

OPENING
Calculated value entered here

ADD
Manually inputted value here

TAKE
Manually inputted value here

CLOSING
Calculated value entered here

Again, any solutions (sample databases) more than welcome !

 

[RainLover]

http://allenbrowne.com/AppInventory.html

It you are trying to control inventory then this link may help.

 

[jagstirling]

If you are trying to control inventory then this link may help

............ I'm not (and was reluctant to mention ithe 'inventory' word as I know there are alot of examples out there).

I just thought it the best way to explain what I am trying to do.

Never the less I am grateful for your input and I will see what lessons I can draw from the example provided.

Thanks again.

 

[jagstirling]

OK guys ..... I've done it and here is the code I used.

It works but I'd appreciate a critical eye over it.

As and eperienced programmer would notice, I'm a bit of a 'noob' at this so I'll welcome any constructive criticsm (or praise)!

Personally, I'm qute happy that I cracked it !

Option Compare Database

Private Sub Command0_Click()


'Clear out tblOutput
'-------------------
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE tblOutput.* FROM tblOutput"
DoCmd.SetWarnings True


'Insert P1 Opening as 0
'----------------------
Dim mySQL100 As String

mySQL100 = "INSERT INTO tblOutput ( Period, Activity, OutputValue ) "
mySQL100 = mySQL100 + "VALUES (1, 'Opening', 0)"

DoCmd.SetWarnings False
DoCmd.RunSQL mySQL100
DoCmd.SetWarnings True

'----------
'Start Loop
'----------

Dim myCounter As Integer
Dim myPeriod As Integer

For Counter = 1 To 9

myPeriod = 0 + Counter

'Insert Add and Take into tblOutput
'----------------------------------
Dim mySQL200 As String

mySQL200 = "INSERT INTO tblOutput ( Period, Activity, OutputValue ) "
mySQL200 = mySQL200 + "SELECT tblInput.Period, tblInput.Activity, tblInput.InputValue "
mySQL200 = mySQL200 + "FROM tblInput "
mySQL200 = mySQL200 + "WHERE (((tblInput.Period)=" & myPeriod & "))"

DoCmd.SetWarnings False
DoCmd.RunSQL mySQL200
DoCmd.SetWarnings True

'Calculate Closing Value and Insert Into tblOutput
'-------------------------------------------------
Dim mySQL300 As String

mySQL300 = "INSERT INTO tblOutput ( Period, Activity, OutputValue ) "
mySQL300 = mySQL300 + "SELECT tblOutput.Period, 'Closing' AS Activity, Sum(tblOutput.OutputValue) AS SumOfOutputValue "
mySQL300 = mySQL300 + "FROM tblOutput "
mySQL300 = mySQL300 + "GROUP BY tblOutput.Period, 'Closing'"
mySQL300 = mySQL300 + "HAVING (((tblOutput.Period) = " & myPeriod & "))"

DoCmd.SetWarnings False
DoCmd.RunSQL mySQL300
DoCmd.SetWarnings True

'Calculate Opening Value Base On Previous Period's Closing Value
'---------------------------------------------------------------

Dim mySQL400

mySQL400 = "INSERT INTO tblTemp ( Period, Activity, OutputValue ) "
mySQL400 = mySQL400 + "SELECT tblOutput.Period, tblOutput.Activity, tblOutput.OutputValue "
mySQL400 = mySQL400 + "FROM tblOutput "
mySQL400 = mySQL400 + "WHERE (((tblOutput.Period)=" & myPeriod & ") AND ((tblOutput.Activity)='Closing'))"

DoCmd.SetWarnings False
DoCmd.RunSQL mySQL400
DoCmd.SetWarnings True

Dim mySQL500

mySQL500 = "UPDATE tblTemp SET tblTemp.Period = " & myPeriod + 1 & ", tblTemp.Activity = 'Opening'"

DoCmd.SetWarnings False
DoCmd.RunSQL mySQL500
DoCmd.SetWarnings True

Dim mySQL600

mySQL600 = "INSERT INTO tblOutput ( Period, Activity, OutputValue ) "
mySQL600 = mySQL600 + "SELECT tblTemp.Period, tblTemp.Activity, tblTemp.OutputValue "
mySQL600 = mySQL600 + "FROM tblTemp"

DoCmd.SetWarnings False
DoCmd.RunSQL mySQL600
DoCmd.SetWarnings True

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE tblTemp.* FROM tblTemp"
DoCmd.SetWarnings True

Next Counter

End Sub

 

[RainLover]

In all your modules you should declare.

Option Compare Database
Option Explicit

Add the second line and try to compile.

 

[namliam]

Yes add Option Explicit like RianLover said...

'Clear out tblOutput
'-------------------
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE tblOutput.* FROM tblOutput"
DoCmd.SetWarnings True

Be weary of bloating when doing deletes...
Also instead of these three commands, you can simply do
Currentdb.Execute "Delete..."

mySQL100 = "INSERT INTO tblOutput ( Period, Activity, OutputValue ) "
mySQL100 = mySQL100 + "VALUES (1, 'Opening', 0)"

OK... but.... this would be 'slightly' better
mySQL100 = ""
mySQL100 = mySQL100 & "INSERT INTO tblOutput ( Period, Activity, OutputValue ) "
mySQL100 = mySQL100 & "VALUES (1, 'Opening', 0)"

It makes your SQL be on one line, thus be more readable and as a result maintanable.

For Counter = 1 To 9
myPeriod = 0 + Counter

Indent!

For every For.. next, if then else, do while etc... Indent

For Counter = 1 To 9
    myPeriod = 0 + Counter
next Counter

Again this goes towards long term readability and maintenance.

Dim mySQL200 As String

No need to make seperate mySQL variable, just recycle the one you have used above.
Also + is for adding, while & is for concatinating... few exceptions excepted...

I have not looked in detail at your queries, but in general I would say this whole temp table/output table is not required at all...
Then again if it works it works, good job !

Just be carefull of your database size (bloating) it tends to grow and grow if you do this unless you (automaticaly) compact it on a regular basis.

 

[gemma-the-husky]

i do a similar thing (with some stock records as it happens)

what i do is calculate the final balance, and treat it as both a closing and opening balance, and actually insert BOTH of them into the file, so that the closing balance is dated the last day of the previous month, and the opening balance the first date of the next month. (as part of a month end procedure). The closing balance is posted as a minus, and the opeing as a plus, so they self cancel

(if you are used to accounting systems - similar to an reversing accrual)

doing it this way means you store all values of any movement type in a single column, and adding these values to get a period movement/total is much simpler.

 

[jagstirling]

Some good advice there ........... thanks alot.

One thing that I woudl appreciate more info. on is the tblTemp that i have uised.

I use it to take the periods closing value and insert it as the next periods opening value ....

How else could I do this ?

An update query wouldn't work as this would change the original record.

I tried to look for info that would allow me to;

• 
  Copy the record
  Change the period (from 1 to 2 ..... or 99 to 100 .... ie. the 'next period)
  Change the description (from Closing to Opening)

I suppose recordsets are the key here ........... but I'm only just managing to get my head around SQL strings !