Access VBA project.. Creating a loop to go via a recordset

Question

I have an Access VBA project that I am working on and it involves Looping. I have three fields Fund #, Effective date, and Total Amount. I am trying to create a VBA code that will loop via the account ID and sum up the Total Amounts based off the Fund Activity in the last 5 days (effective Date). Basically, my in input from my table looks like this.

Fund:   Effective Date, Total Amount 
586     01/02/2015       -454
586     01/03/2015      -454
586     01/04/2015     -454
586     01/05/2015     -454
586     01/06/2015    -854
586     01/07/2015     -954
586     01/08/2015     -254
586     01/09/2015    -154
586     01/10/2015    -654
586     01/13/2015    -354
486 01/02/2015    -954
486 01/03/2015    -954
486 01/05/2015    -954
486 01/07/2015    -954
486 01/09/2015    -954
486 01/010/2015    -954

The VBA will identify the fund number. Will look at the date and sum up funds total outflow in the last five days using the effective date. Then the loop will go back to the second date and add the next five days total outflow for that fund, once it's done looping st 5 days it will move to the next date etc. Once it completes looping via that fund #, it will move to the next fund # and do the same thing as mentioned fund above

The goal is to insert the final output into another table and will look something like this

Id Date , ID Amount1, ID Amount1, ID Amount1, ID Amount1, Largest Redemption week date 
Fund #      Id Date ,   01/02- 1/07 01/03- 1/08 01/04- 1/09 01/05- 1/10, 
Etc Largest Redemption
Date    Largest Redemtption Week Amount 
44      -20788  -5788   -10500  5885    12/20/2014  -45855
88      -10788  -2788   -8500   3885    06/30/2014  -50000
60      -13788  -2788   -2500   1885    06/30/2014  -25000

So the final output will be inserted into another table and will look something like this

This is what I have so far.. I need to create a Access vba code so will give me the out put above. Thanks for you help

Option Compare Database
Function OpenRecordset()

Dim dbsArchive As Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim i As Double
Dim x As Double
Dim StrSQL As String
Dim strFund As String
Dim strCriteria As String
Dim AddFundCriteria As Double
Dim FirstMark As Variant
Dim IDFund As Double
Dim IDDate As Double
 

   Set dbsArchive = CurrentDb
   Set rs = dbsArchive.OpenRecordset("2014")

   For i = 0 To rs.RecordCount - 1
       strFund = rs.Fields("Link_Fund")
   
       
       StrSQL = "SELECT [USysD07366-2014].LINK_FUND, [USysD07366-2014].SUPER_SHEET_DATE," & _
                 "Sum([USysD07366-2014]![REDEMPT]+[USysD07366-2014]![EXCHANGE OUT]) AS RedemptionTotal " & _
                "FROM [USysD07366-2014] " & _
                "GROUP BY [USysD07366-2014].LINK_FUND, [USysD07366-2014].SUPER_SHEET_DATE " & _
                "HAVING ((([USysD07366-2014].LINK_FUND) = " & strFund & ")) " & _
                "ORDER BY [USysD07366-2014].SUPER_SHEET_DATE; "

                Set rs2 = dbsArchive.OpenRecordset(StrSQL, dbOpenSnapshot)
   
   For x = 0 To rs2.RecordCount - 1

'   strCriteria = "Link Fund = " & rstCategories![Link Fund]
   
                           
                Debug.Print rs2.Fields("Link_Fund")
                Debug.Print rs2.Fields("SUPER_SHEET_DATE")
                Debug.Print rs2.Fields("RedemptionTotal")
                    
                rs2.MoveNext
                      
                   Next x
                
                   rs.MoveNext
                
                   Next i

                rs.Close
                rs2.Close
                Set rs = Nothing
                Set rs2 = Nothing
                dbsArchive.Close


ErrorHandler:
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description         
            
End Function

What is your question? What have you tried so far?

Fjodr
– Fjodr

2015-04-17 17:21:18 +00:00
Commented Apr 17, 2015 at 17:21 — Fjodr
– Fjodr, Commented Apr 17, 2015 at 17:21

dfader2 · Accepted Answer · 2015-04-17 20:19:16Z

1

I don't have enough reputation to add a comment so I have to ask here:

There's a number of things you might want to clear up first. When you say "the last five days", do you mean within the last 5 days, or for the last five records? If a date is skipped how do you want it to be handled?

486 01/02/2015 -954
486 01/03/2015 -954
486 01/05/2015 -954
486 01/07/2015 -954
486 01/09/2015 -954

If we're analyzing for 01/09/2015, should it sum all of these records, or only the dates > 01/04/2015? What about for dates where there are less than 5 days of data (i.e. 01/02/2015)? Should it still sum up whatever data is available, or not perform any analysis for that date?

I'm at work now and it's been a while since I've used VBA so I can't write out the actual code syntax, but my logic would be something like this:

var j = 2   # used for tracking row in solution sheet
var k = 0   # used for tracking column in solution sheet
var prevFund = Cells(2,1).Value
For each Row:
  var fundID = Cells(ActiveCell.Row, 1).Value
  k = k + 1  
  if (fundID <> prevFund)
    j = j + 1
    k = 0
  end if 
  var currentDate = Cells(ActiveCell.Row, 2).Value  
  var minDate = {currentDate - 5}  # not sure of the correct function for this  
  var sum = Cells(ActiveCell.Row, 3).Value
  for (int i =0; i < 5; i++)   
    var checkDate = Cells(ActiveCell.Row + i, 2).Value  
    if (checkDate > minDate)  
      sum = sum + Cells(ActiveCell.Row + i, 3).Value
    else
      break
    end if
  end for
  Sheets("Sheet2").Cells(j,1 + k).Value = sum
end for

This will just take care of summing up the weeks and as I said the syntax probably isn't all correct. You'll still need to handle the headers and getting the max weeks. Is the # of weeks consistent for all of the data, or can they be different depending on the fund? I'll try to update with more later if you can't figure out but please post if you do it yourself.

edited Apr 17, 2015 at 20:19

answered Apr 17, 2015 at 17:50

dfader2

14111 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Steve Nganga Over a year ago

Thank you for responding to my question. When I say the last five days, yes, I mean within the last 5 days and not the last five records. The Funds # has to be sorted out by ascending (effective Date) to get the most recent date. Then I will look at the fund # and add all the redemption amount within the last 5 days . Then next iteration will be the second date from the first date, add the redemption amounts, within the last 5 days. Once all iterations are complete within that fund #, then we move to the next fund #, etc

Steve Nganga Over a year ago

The final table will look like something like this.

Steve Nganga Over a year ago

Fund The final table will look like something like this Fund # 01/02- 01/07 01/03- 01/08 01/03-01/08 01/04- 01/09 01/05- 01/09 Largest Redemption Week Starting Largest Redemption Week Amount 44 -11000 -45551 -8222 -8766 -5662 01/3/2014 -11000 45 -13000 -25551 -32222 -5766 -5662 01/06/2014 -32222

Steve Nganga Over a year ago

Thanks for the help. I am working with access and not excel sheet but the logic you provided works, if I can translate into access VBA..

Collectives™ on Stack Overflow

Access VBA project.. Creating a loop to go via a recordset

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related