Finding value from one table closest to value from another table

[Bouldergirl]

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

 

[stopher]

This query will give you what you require (no code needed):

SELECT Table1.GRID, Table1.spc_ID, Table1.name, Min(Table2.doy) AS MinOfdoy
FROM Table1 INNER JOIN Table2 ON Table1.GRID = Table2.GRID
WHERE (((Table2.cum_GDD)>[min_GDD]))
GROUP BY Table1.GRID, Table1.spc_ID, Table1.name
ORDER BY Table1.GRID;

I've also attached the example d/b.

hth
Chris

 

[Bouldergirl]

Thanks stopher,

I just saw your post.... I ended up finding a very similar solution on a different forum site:

SELECT Table_1.GRID, Table_1.name,
(SELECT TOP 1 Table_2.doy FROM Table_2
WHERE (((Table_2.GRID)=Table_1.GRID) AND ((Table_2.cum_GDD)>=Table_1.min_GDD))
ORDER BY Table_2.GRID, Table_2.doy) AS doy
FROM Table_1
ORDER BY Table_1.GRID, Table_1.spc_ID;

I bet both solutions work, though!