Bouldergirl
Technical User
I have 2 tables, Table 1 and Table 2 (see attached images)….Although in the example tables (attached), only a few records are shown, they both have about 1000 GRID values: Table 1 contains anywhere from 1-20 records (i.e., rows) per GRID value containing unique “spc_ID” and “name” values, while Table 2 has 365 unique “cum_GDD” and “doy” values within each GRID value (“cum_GDD” is a running sum of something, calculated as a function of “GRID” and “doy”).
I’d like to create a table that contains the fields “GRID” and “name” from Table 1, but which also has the field “doy” (from Table 2) where “doy” corresponds to the record in Table 2 (as a function of GRID) for the first occurrence of “cum_GDD” is = or > “min_GDD” from Table 1 (order matters and so I sort both tables at the beginning of the module).
At the moment, I’ve been trying to read one of the two tables into an array, after which I try to scroll through the other table looking for the appropriate occurrence of “doy” (but I’m not sure which Table should be scrolled through and which table should be read into the array). This hasn’t been working too well, so maybe I could somehow read both Tables in based on their relationship (“GRID” is the same between the 2), though I’ve no idea how to do this.
I’m thinking I need a loop within a loop, but I’m stuck on how to do this successfully. It seems to me that while the value of “GRID” remains the same, each “name” from Table 1 with that value of GRID should be processed, and the “Results” table updated, one at a time. I’ve tried to “MoveNext” in a looping fashion until the criteria that “cum_GDD” is = or > “min_GDD” is met, at which point the “MoveNext” process is terminated and the value of “doy” in the current record gets written to the Results table, but I keep getting a “no current record” message, as if only the 1st record exists.
If anyone can look at my code (see below) and comment or offer advice, I would be very grateful!
Here’s the code I have thus far:
Option Compare Database
Function CalculateStartDate()
Dim db As Database
Dim tdfNew As TableDef
Dim rout, spc_start As Single, spc_GDD(1037, 20), species(1037, 20)
Dim cell_ID As Integer, GDD_cum As Single, spc_ID As Integer, spc_thresh As Single
ofilename = "Results_Table"
Set db = CurrentDb()
For i = 0 To db.TableDefs.Count - 1 ' Delete table
If db.TableDefs(i).Name = ofilename Then
DoCmd.DeleteObject A_TABLE, ofilename
Exit For
End If
Next
Set tdfNew = db.CreateTableDef(ofilename)
With tdfNew
.Fields.Append .CreateField("GRID", dbInteger)
.Fields.Append .CreateField("name", dbText)
.Fields.Append .CreateField("doy", dbInteger)
db.TableDefs.Append tdfNew
End With
cell_ID = 1 'set this number to be = to the first ID# of Table 2
ID = 1 'this represents the 1st name within Table 1
Set rinc = db.OpenRecordset("Table_1 ", dbOpenDynaset)
Set rinwd = db.OpenRecordset("Table_2", dbOpenDynaset)
Set rout = db.OpenRecordset(ofilename, dbOpenDynaset)
rinwd.Sort = "[ID],[doy]"
rinc.Sort = "[GRID],[ID]"
'the section below reads the records from Table 1 into an array
rinc.MoveFirst
Do While rinc.GRID = cell_ID
name(rinc.GRID, rinc.ID) = rinc! name
min_GDD(rinc.GRID, rinc.ID) = rinc!min_GDD
rinc.MoveNext
Loop
'the section below is supposed to keep moving through the runningsum table (while the value of the
‘GRID is the same as cell_ID, until the cum_GDD value is above the ID-specific min_GDD threshold
‘from Table 1, then print the doy from Table 2 based on each name and each GRID from Table 1
rinwd.MoveFirst
Do While rinwd.ID = cell_ID
For ID = 1 To 20 ‘to accommodate up to 20 unique “names: per “GRID” in Table 1
spc_start = 1
spc_thresh = min_GDD(cell_ID, ID)
GDD_cum = rinwd.cum_GDD
If GDD_cum < spc_thresh Then
rinwd.MoveNext
spc_start = rinwd.doy ‘not sure if Dlookup would be better here?
rout.AddNew
rout![GRID] = rinwd.GRID
rout![name] = name(cell_ID, ID)
rout![doy] = spc_start
rout.Update
Next ID
cell_ID = cell_ID + 1
ID = ID + 1
rinwd.MoveFirst
Loop
rinwd.Close: rinc.Close: rout.Close
End Function
I’d like to create a table that contains the fields “GRID” and “name” from Table 1, but which also has the field “doy” (from Table 2) where “doy” corresponds to the record in Table 2 (as a function of GRID) for the first occurrence of “cum_GDD” is = or > “min_GDD” from Table 1 (order matters and so I sort both tables at the beginning of the module).
At the moment, I’ve been trying to read one of the two tables into an array, after which I try to scroll through the other table looking for the appropriate occurrence of “doy” (but I’m not sure which Table should be scrolled through and which table should be read into the array). This hasn’t been working too well, so maybe I could somehow read both Tables in based on their relationship (“GRID” is the same between the 2), though I’ve no idea how to do this.
I’m thinking I need a loop within a loop, but I’m stuck on how to do this successfully. It seems to me that while the value of “GRID” remains the same, each “name” from Table 1 with that value of GRID should be processed, and the “Results” table updated, one at a time. I’ve tried to “MoveNext” in a looping fashion until the criteria that “cum_GDD” is = or > “min_GDD” is met, at which point the “MoveNext” process is terminated and the value of “doy” in the current record gets written to the Results table, but I keep getting a “no current record” message, as if only the 1st record exists.
If anyone can look at my code (see below) and comment or offer advice, I would be very grateful!
Here’s the code I have thus far:
Option Compare Database
Function CalculateStartDate()
Dim db As Database
Dim tdfNew As TableDef
Dim rout, spc_start As Single, spc_GDD(1037, 20), species(1037, 20)
Dim cell_ID As Integer, GDD_cum As Single, spc_ID As Integer, spc_thresh As Single
ofilename = "Results_Table"
Set db = CurrentDb()
For i = 0 To db.TableDefs.Count - 1 ' Delete table
If db.TableDefs(i).Name = ofilename Then
DoCmd.DeleteObject A_TABLE, ofilename
Exit For
End If
Next
Set tdfNew = db.CreateTableDef(ofilename)
With tdfNew
.Fields.Append .CreateField("GRID", dbInteger)
.Fields.Append .CreateField("name", dbText)
.Fields.Append .CreateField("doy", dbInteger)
db.TableDefs.Append tdfNew
End With
cell_ID = 1 'set this number to be = to the first ID# of Table 2
ID = 1 'this represents the 1st name within Table 1
Set rinc = db.OpenRecordset("Table_1 ", dbOpenDynaset)
Set rinwd = db.OpenRecordset("Table_2", dbOpenDynaset)
Set rout = db.OpenRecordset(ofilename, dbOpenDynaset)
rinwd.Sort = "[ID],[doy]"
rinc.Sort = "[GRID],[ID]"
'the section below reads the records from Table 1 into an array
rinc.MoveFirst
Do While rinc.GRID = cell_ID
name(rinc.GRID, rinc.ID) = rinc! name
min_GDD(rinc.GRID, rinc.ID) = rinc!min_GDD
rinc.MoveNext
Loop
'the section below is supposed to keep moving through the runningsum table (while the value of the
‘GRID is the same as cell_ID, until the cum_GDD value is above the ID-specific min_GDD threshold
‘from Table 1, then print the doy from Table 2 based on each name and each GRID from Table 1
rinwd.MoveFirst
Do While rinwd.ID = cell_ID
For ID = 1 To 20 ‘to accommodate up to 20 unique “names: per “GRID” in Table 1
spc_start = 1
spc_thresh = min_GDD(cell_ID, ID)
GDD_cum = rinwd.cum_GDD
If GDD_cum < spc_thresh Then
rinwd.MoveNext
spc_start = rinwd.doy ‘not sure if Dlookup would be better here?
rout.AddNew
rout![GRID] = rinwd.GRID
rout![name] = name(cell_ID, ID)
rout![doy] = spc_start
rout.Update
Next ID
cell_ID = cell_ID + 1
ID = ID + 1
rinwd.MoveFirst
Loop
rinwd.Close: rinc.Close: rout.Close
End Function
![[thumbsup2]](/data/assets/smilies/thumbsup2.gif "[thumbsup2] [thumbsup2]")
It works perfectly!
