VBA Nested Loop controlling fields when cycling thru table

[CAMsk]

Hi All,
I am new to Access and trying to set up a nested loop to cycle thru a table. I have a skeleton of the Sub created and can't control the second field, not knowing how to point the cursor at it. I have a field "Symbol" with about 3,000 entries, and a field "MktDate" with perhaps 70 entries for each value of Symbol. I want to cycle thru the entire table by incrementing Symbol, then run thru the MktDate field for that Symbol, go back and increment Symbol again, run thru MktDate again, each time doing some calculations, and will ultimately do an Edit Update to save some of the results. I believe I will need to convert from a Sub to a Function when I take that last step.

I've tried setting the sort order of the table, which doesn't seem to change anything. I can only get the first or last value of MktDate entries, so I know I am not controlling the cursor to increment that field with the value of Symbol fixed on any selection.

Performance isn't an issue. I will run this once a day, at day's end, on a powerful computer. It can run all night.

Any comments greatly appreciated.

Option Compare Database

Dim i As Integer
Dim j As Integer

Sub Single_Table_Nested_Loop()

Set dbs = CurrentDb
Set rs1 = dbs.OpenRecordset("ExportAAA", dbOpenDynaset)
Set rs2 = dbs.OpenRecordset("ExportAAA", dbOpenDynaset)

'Outer Loop to cycle thru "Symbol" field
With rs1
i = 2
'will replace with either the SymbolCount from the table query or EOF
'for now we will use a low value of i for testing
.MoveLast
.MoveFirst

Do While i > 0
Debug.Print "outer loop"; ";"
Debug.Print rs1.Fields("Symbol")
'we will loop thru the symbols and use the inner loop for each to calc ma's

j = 2
Do While j > 0
Debug.Print "inner loop"; ";"
With rs2
.MoveFirst
Debug.Print rs2.Fields("MktDate")
rs2.MoveNext
j = j - 1
End With
Loop

i = i - 1
rs1.MoveNext
Loop
End With
rs1.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing
End Sub

 

[MarkK]

I have a field "Symbol" with about 3,000 entries, and a field "MktDate" with perhaps 70 entries for each value of Symbol.

This describes a one-to-many relationship and is impossible to accurately represent in a single table. The minimum unit of storage or retrieval in a database is a row, so one row represents one thing. So if one thing has 70 related things, then we would expect to see one row in TableA, and 70 related rows in TableB. Any other way to model this reality in a database will be practically unworkable.

 

[CAMsk]

Thanks MarkK, that explains why all of the examples I have seen on the forums were based on 2 tables. I will revise accordingly. I can confirm the "practically unworkable" remark. Sure seemed that way to me. Much appreciated.

 

[MarkK]

So minimally, to make those links, you want tables like...

tblSymbol
SymbolID (Primary Key - Autonumber)
Symbol

tblMarketDate
MarketDateID (PK)
SymbolID (Foreign Key - Contains the SymbolID of the related parent row)
MarketDate
Amount

See how in this design, many date rows might contain a link to a single row in tblSymbol? But at the same time, a Symbol knows nothing about a MarketDate. It's a one-way arrow of connection.

 

[stopher]

Try this:

Sub Single_Table_Nested_Loop()

Dim i As Integer
Dim j As Integer

Set dbs = CurrentDb
Set rs1 = dbs.OpenRecordset("SELECT DISTINCT Symbol FROM ExportAAA", dbOpenDynaset)

'Outer Loop to cycle thru "Symbol" field
With rs1
    i = 2
    'will replace with either the SymbolCount from the table query or EOF
    'for now we will use a low value of i for testing
    
    Do While i > 0
        Debug.Print "outer loop"; ";"
        Debug.Print rs1.Fields("Symbol")
        'we will loop thru the symbols and use the inner loop for each to calc ma's
        
        Set rs2 = dbs.OpenRecordset("Select * from ExportAAA where Symbol=" & rs1.Fields("Symbol"), dbOpenDynaset)
        With rs2
            j = 2
            Do While j > 0
                Debug.Print "inner loop"; ";"
        
                
                Debug.Print rs2.Fields("MktDate")
                rs2.MoveNext
                j = j - 1
            Loop
        End With
    
        i = i - 1
        rs1.MoveNext
    Loop
End With
rs1.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing
End Sub

rs1 is a list of the unique values for symbol (numeric for the code I've given)

rs2 is a list of records for the current symbol value. Note how rs2 is recreated on each outer loop

hth

 

[stopher]

Having read MarkK's response, perhaps you could show some sample data.

 

[The_Doc_Man]

You could do this with a query for your recordset wherein you have ORDER BY clauses for the Symbol first and the MktDate second. Query-based recordsets can be updated as long as they don't contain SQL aggregates (like SUM, COUNT, etc. - anything that requires a GROUP BY clause).

You should learn to use queries for this kind of processing. Queries can be used to impose any sort order including those that is a pain in the toches with explicit recordset operations.

Even if you wanted to do a recordset because you wanted to trap things "on the fly" you could still do this on a query rather than a table. Access doesn't care where the recordset originated most of the time (exception: Linked Excel tables are problematic sometimes). Anything that presents an ordered set of records is all the same.

 

[CAMsk]

MarkK, in your note about the two tables, does the field order make any difference on the suggested tblMktDate?

"tblMarketDate
MarketDateID (PK)
SymbolID (Foreign Key - Contains the SymbolID of the related parent row)
MarketDate
Amount

 

[MarkK]

You asked...

...does the field order make any difference on the suggested tblMktDate?

Make any difference to what? Obviously operations that depend on field order will be affected. Those that don't, won't.

 

[CAMsk]

Stopher, fascinating adaptation. You may have figured out how to defy gravity with your solution. I will make the suggested changes and give it a try. As requested, I will try to post data. Thanks, I would never had figured this out.

 

[CAMsk]

Thank you The_Doc_Man. Learning queries is on the list. Nested loop seemed like a good starting point. Appreciate the suggestions.

 

[The_Doc_Man]

Yep, queries are your friend. In this case, your loop would be just a single loop through the query's recordset and the ORDER BY clauses of the query would do the inner-loop sequencing for you. Just one more reason why you need queries on your "to-do" list. Keep on truckin', CAMsk. It is a lot to learn, but Access can take you places you never thought you would go.

 

[CAMsk]

I am seeing that Access can do amazing things, so queries will be in the learning queue for sure. Thanks all.

 

[CAMsk]

Stopher, I have tried your suggestion. Added a sub name, compiles fine. Getting the Run-time error '3061': Too few parameters. Expected 1. This line highlighted; Set rs2 = dbs.OpenRecordset("SELECT * from ExportAAA where Symbol= " & rs1.Fields("Symbol"), dbOpenDynaset)
I have tried single quotes in various places, but other ideas I have found on the web don't seem relevant. Anything jump out? Thanks.

 

[stopher]

Might be a good time for you to provide an example of your database. I'm kind of making a few assumptions about you table structure.

 

[CAMsk]

Stopher, here are 5 rows from my database and the column names. The database is for tracking stock market data and the object is to apply screening criteria across a large selection of stocks. The last 2 columns will be computed from daily closing prices. Hope this spreadsheet shag posts ok.
ID MktDate Symbol Name Current Price Volume (1000s) 50-Day Avg Vol (1000s) Up/Down Vol A/D Rating A/D Rating - Pr Wk EPS Rating RS Rating Comp Rating Ind Group Rank % Off High 8dma 21dma
11827 8/22/2016 A Agilent Technologies Inc 47.58 1626 1.2 B+ B+ 59 79 79 63 -2.16
8898 8/23/2016 A Agilent Technologies Inc 47.82 1645 1.2 B+ B+ 59 79 79 59 -1.67
5985 8/24/2016 A Agilent Technologies Inc 46.7 2634 1.3 B- B+ 59 75 79 59 -3.97
3103 8/26/2016 A Agilent Technologies Inc 47.32 1581 1.2 B- B+ 59 77 75 64 -2.69
1 8/29/2016 A Agilent Technologies Inc 47.62 1325 1.2 B- B+ 59 78 75 64 -2.08
17576 9/1/2016 A Agilent Technologies Inc 46.99 1257 2022 1.2 C+ B- 59 78 70 69 -3.37
14666 9/2/2016 A Agilent Technologies Inc 47 1424 2021 1.2 C+ B- 59 77 68 73 -3.35
I've attached a snag of the design view so you can see the column data types. Hope this helps.

 

[Cronk]

Stopher was assuming your Symbol was numeric. As it is text, you will need to include the value in the first record set in quotes.
Set rs2 = dbs.OpenRecordset("Select * from ExportAAA where Symbol= ' " & rs1.Fields("Symbol") & "'", dbOpenDynaset)

 

[CAMsk]

Cronk, this changed everything, and it seems to be a breakthru. I am now working on the counters to make the loops do what I want. Thanks to you and Stopher for a big assist.