Hi, I am working on an Access database for tracking stocks. The application is built on two tables. One is called tblSYMBOL with a single field SYMBOL (primary key) with a list of around 3000 stock symbols. The other table is named tblStockData with field MktDate as primary key and SYMBOL as a foreign key. The intent is these two keys are a composite key for tblStockData.
I have built a VBA module to test the logic. As I cycle thru stock symbols with the other loop (rs1), I want to hold that stock symbol fixed and cycle thru the date range (around 70 dates) in the inner loop (rs2) and do calculations. I cannot get the dates to cycle in the inner loop, rs2. I can bring up the first or the last date, but the only cycling that occurs in the inner loop, is to cycle thru symbols. I have tried tweaking the field order, properties, relationships, and nothing helps. I cannot figure out what I am missing. :banghead:
Here is the code:
Option Compare Database
Dim i As Integer
Dim j As Integer
Sub Two_Table_Nested_Loop()
Set dbs = CurrentDb
Set rs1 = dbs.OpenRecordset("tblSYMBOL", dbOpenDynaset)
Set rs2 = dbs.OpenRecordset("tblStockData", dbOpenDynaset)
'Outer Loop to cycle thru tblSymbol
With rs1
i = 5
'will replace with EOF after testing complete
'for now we will use a low value of i for testing
rs1.MoveLast
rs1.MoveFirst
Do While i > 0
Debug.Print "outer loop"; ";"
Debug.Print rs1.Fields("Symbol")
'use outer loop (rs1) to cycle thru symbols and inner loop (rs2) to cycle thru dates
'and perform calculations on each symbols price and volume data
j = 3
With rs2
rs2.MoveFirst
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 dbs = Nothing
End Sub
Example of tblSYMBOL:
tblSYMBOL
Symbol (primary key)
A
AA
AAAP
AAC
AAL
AAN
Example of tblStockData
tblStockData
MktDate Symbol
10/14/2016 A
10/14/2016 AA
10/14/2016 AAAP
10/14/2016 AAC
10/14/2016 AAL
10/14/2016 AAN
10/14/2016 AAOI
10/14/2016 AAON
There are 14 more fields of data to the right of Symbol. MktDate is the primary key and Symbol is the foreign key. My assumption is that selecting both of these fields as keys in the design view makes them part of the composite key. Do I need to make any entry on the Property Sheet to designate SYMBOL as a foreign key or that together they form a composite key?
Here is a partial look at the Immediate Window output from running the module.
outer loop;
A
inner loop;
10/14/2016
inner loop;
10/14/2016
inner loop;
10/14/2016
outer loop;
AA
inner loop;
10/14/2016
inner loop;
10/14/2016
inner loop;
10/14/2016
outer loop;
AAAP
inner loop;
10/14/2016
inner loop;
10/14/2016
inner loop;
10/14/2016
outer loop;
Some of you have helped me with an earlier rendition of this problem based on a single table. I have moved to the two table version as being conceptually simpler. All comments and questions appreciated and thanks for your help.
I have built a VBA module to test the logic. As I cycle thru stock symbols with the other loop (rs1), I want to hold that stock symbol fixed and cycle thru the date range (around 70 dates) in the inner loop (rs2) and do calculations. I cannot get the dates to cycle in the inner loop, rs2. I can bring up the first or the last date, but the only cycling that occurs in the inner loop, is to cycle thru symbols. I have tried tweaking the field order, properties, relationships, and nothing helps. I cannot figure out what I am missing. :banghead:
Here is the code:
Option Compare Database
Dim i As Integer
Dim j As Integer
Sub Two_Table_Nested_Loop()
Set dbs = CurrentDb
Set rs1 = dbs.OpenRecordset("tblSYMBOL", dbOpenDynaset)
Set rs2 = dbs.OpenRecordset("tblStockData", dbOpenDynaset)
'Outer Loop to cycle thru tblSymbol
With rs1
i = 5
'will replace with EOF after testing complete
'for now we will use a low value of i for testing
rs1.MoveLast
rs1.MoveFirst
Do While i > 0
Debug.Print "outer loop"; ";"
Debug.Print rs1.Fields("Symbol")
'use outer loop (rs1) to cycle thru symbols and inner loop (rs2) to cycle thru dates
'and perform calculations on each symbols price and volume data
j = 3
With rs2
rs2.MoveFirst
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 dbs = Nothing
End Sub
Example of tblSYMBOL:
tblSYMBOL
Symbol (primary key)
A
AA
AAAP
AAC
AAL
AAN
Example of tblStockData
tblStockData
MktDate Symbol
10/14/2016 A
10/14/2016 AA
10/14/2016 AAAP
10/14/2016 AAC
10/14/2016 AAL
10/14/2016 AAN
10/14/2016 AAOI
10/14/2016 AAON
There are 14 more fields of data to the right of Symbol. MktDate is the primary key and Symbol is the foreign key. My assumption is that selecting both of these fields as keys in the design view makes them part of the composite key. Do I need to make any entry on the Property Sheet to designate SYMBOL as a foreign key or that together they form a composite key?
Here is a partial look at the Immediate Window output from running the module.
outer loop;
A
inner loop;
10/14/2016
inner loop;
10/14/2016
inner loop;
10/14/2016
outer loop;
AA
inner loop;
10/14/2016
inner loop;
10/14/2016
inner loop;
10/14/2016
outer loop;
AAAP
inner loop;
10/14/2016
inner loop;
10/14/2016
inner loop;
10/14/2016
outer loop;
Some of you have helped me with an earlier rendition of this problem based on a single table. I have moved to the two table version as being conceptually simpler. All comments and questions appreciated and thanks for your help.
