473,326 Members | 2,114 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Finding value Table 1 near value from Table 2

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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Function CalculateStartDate()
  4. Dim db As Database
  5. Dim tdfNew As TableDef
  6. Dim rout, spc_start As Single, spc_GDD(1037, 20), species(1037, 20)
  7. Dim cell_ID As Integer, GDD_cum As Single,  spc_ID As Integer, spc_thresh As Single
  8.  
  9.  
  10.   ofilename = "Results_Table"
  11.   Set db = CurrentDb()
  12.     For i = 0 To db.TableDefs.Count - 1        '   Delete  table
  13.         If db.TableDefs(i).Name = ofilename Then
  14.           DoCmd.DeleteObject A_TABLE, ofilename
  15.           Exit For
  16.         End If
  17.     Next
  18.    Set tdfNew = db.CreateTableDef(ofilename)
  19.        With tdfNew
  20.         .Fields.Append .CreateField("GRID", dbInteger)
  21.         .Fields.Append .CreateField("name", dbText)
  22.         .Fields.Append .CreateField("doy", dbInteger)
  23.         db.TableDefs.Append tdfNew
  24.         End With
  25.  
  26.    cell_ID = 1 'set this number to be = to the first ID# of Table 2
  27.    ID = 1 'this represents the 1st name within Table 1 
  28.  
  29.   Set rinc = db.OpenRecordset("Table_1 ", dbOpenDynaset)
  30.   Set rinwd = db.OpenRecordset("Table_2", dbOpenDynaset)
  31.   Set rout = db.OpenRecordset(ofilename, dbOpenDynaset)
  32.  
  33.  
  34.     rinwd.Sort = "[ID],[doy]" 
  35.     rinc.Sort = "[GRID],[ID]"
  36.  
  37.   'the section below reads the records from Table 1  into an array
  38.  
  39.     rinc.MoveFirst
  40.     Do While rinc.GRID = cell_ID
  41.      name(rinc.GRID, rinc.ID) = rinc! name
  42.      min_GDD(rinc.GRID, rinc.ID) = rinc!min_GDD
  43.     rinc.MoveNext
  44.     Loop
  45.     'the section below is supposed to keep moving through the runningsum table (while the value of the 
  46.     ‘GRID is the same as cell_ID, until the cum_GDD value is above the ID-specific min_GDD threshold 
  47.     ‘from Table 1, then print the doy from Table 2 based on each name and each GRID from Table 1
  48.  
  49.     rinwd.MoveFirst
  50.     Do While rinwd.ID = cell_ID
  51.        For ID = 1 To 20 ‘to accommodate up to 20 unique "names: per "GRID" in Table 1
  52.         spc_start = 1
  53.         spc_thresh = min_GDD(cell_ID, ID)
  54.         GDD_cum = rinwd.cum_GDD
  55.       If GDD_cum < spc_thresh Then
  56.          rinwd.MoveNext
  57.  
  58.         spc_start = rinwd.doy ‘not sure if Dlookup would be better here?
  59.  
  60.  
  61.           rout.AddNew
  62.           rout![GRID] = rinwd.GRID
  63.           rout![name] = name(cell_ID, ID)
  64.           rout![doy] = spc_start
  65.           rout.Update
  66.  
  67.         Next ID
  68.  
  69.       cell_ID = cell_ID + 1
  70.  
  71.       ID = ID + 1
  72.  
  73.     rinwd.MoveFirst
  74.    Loop
  75.  
  76.    rinwd.Close: rinc.Close: rout.Close
  77. End Function


Attached Images
File Type: jpg Tables1_2.jpg (65.3 KB, 109 views)
File Type: jpg Results_table.JPG (55.7 KB, 67 views)
May 6 '11 #1

✓ answered by NeoPa

Try this (untested) SQL and see if it produces the right data for you :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [GRID]
  2.        , [Name]
  3.        , (SELECT [Doy]
  4.           FROM   [Table 2] AS t2
  5.           WHERE  (t2.GRID=t1.GRID)
  6.             AND  (t2.cum_GDD=(SELECT Min(cum_GDD)
  7.                               FROM   [Table 2] AS t2B
  8.                               WHERE  (t2B.GRID=t1.GRID)
  9.                                 AND  (t2B.cum_GDD>=t1.min_GDD))))
  10. FROM     [Table 1] AS t1
  11. ORDER BY [Name]

4 2655
Rabbit
12,516 Expert Mod 8TB
You don't have to use a special function for this. You can accomplish the same results using either a subquery to return the result or a cross join to the same effect.
May 7 '11 #2
NeoPa
32,556 Expert Mod 16PB
Try this (untested) SQL and see if it produces the right data for you :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [GRID]
  2.        , [Name]
  3.        , (SELECT [Doy]
  4.           FROM   [Table 2] AS t2
  5.           WHERE  (t2.GRID=t1.GRID)
  6.             AND  (t2.cum_GDD=(SELECT Min(cum_GDD)
  7.                               FROM   [Table 2] AS t2B
  8.                               WHERE  (t2B.GRID=t1.GRID)
  9.                                 AND  (t2B.cum_GDD>=t1.min_GDD))))
  10. FROM     [Table 1] AS t1
  11. ORDER BY [Name]
May 7 '11 #3
Thanks NeoPa. I found a similar solution (that definitely works) on a different forum site:

Expand|Select|Wrap|Line Numbers
  1. SELECT Table_1.GRID, Table_1.name, 
  2. (SELECT TOP 1 Table_2.doy FROM Table_2 
  3. WHERE (((Table_2.GRID)=Table_1.GRID) AND ((Table_2.cum_GDD)>=Table_1.min_GDD))  
  4. ORDER BY Table_2.GRID, Table_2.doy) AS doy INTO Results_Table
  5. FROM Table_1
  6. ORDER BY Table_1.GRID, Table_1.spc_ID;
Thanks so much for the help though!
May 9 '11 #4
NeoPa
32,556 Expert Mod 16PB
Good for you :-) and well done for posting what you found.
May 10 '11 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Harag | last post by:
Hi all I've not used Access 2k for a few years and can't remember how to get information from one table that is not in several other tables. I have 4 Tables in my DB tbl 1 - TblMaster -...
4
by: Marco Lazzeri | last post by:
I've got a table called 'main' described as follow CREATE TABLE main ( id_other_table INT, value CHAR ); and a table called 'other' described as follow CREATE TABLE other (
1
by: amraam35 | last post by:
Here is the problem. I have made a log in script that prompts you to log in with a username and password as the "splash screen" It is an unbounded forum that checks that the username and password...
0
by: northshore | last post by:
Hello, I am creating a windows application database. I have a primary table 'Individuals' and a lookup table 'Prefixes.' In the Individuals table, I have a column 'PrefixID' that references...
5
by: Rex | last post by:
Hi, I want to change a value in one table depending on the value(s) in another table. I am trying to achieve this in a form. to elaborate I have a many-to-many relationship between tables...
1
by: rhepsi | last post by:
HII all, im working on postgresql database where i want to copy the data from one table to other table.... when im trying to write the sql query.. ERROR: 42601: syntax error at or near...
13
by: spicster | last post by:
I need help with finding an entire inventory value for a java assignment. This is what I have so far. I am new to Java and am struggling. In need of help, thanks import...
2
by: toodlez | last post by:
Hi, I'm pretty new to C++ and I want to find the min and max value in a collection of values. The pairs are in a text file and contain a descriptor and a value. For example: Bob 120 Jack 7 Larry -90...
2
by: qwedster | last post by:
Folk! How to programattically check if null value exists in database table (using stored procedure)? I know it's possble in the Query Analyzer (see last SQL query batch statements)? But how...
10
by: Vivekneo | last post by:
Hi I am very new to web programming, trying to achieve a task, I have a form with 3 dropdown box and a text field(which is readonly). My problem now is, after selecting the above 3 dropdown boxes,...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.