Evaluate Two Fields

[yukonjack]

Hello all,

I am trying to evaluate two fields in a table at the same time depending on what I have in each field. I will try to explain. I have a TO field and a FROM field each contains a number. What I would like to do is look at the TO field for a record and then look at the FROM field for a particular record that matchs that number and then keep going until it finishes that pattern. For example, if the TO field has a 1 and FROM field has 2 I would look for the next TO record that contains a 2 then that record has the FROM as 5 then I would look for the TO field that is 5 and so on. The unique ID in the table is FROM field. I have tried this in SQL but could not get it to work and suggestion would be greatly appreciated. Essentially what I am trying to do is create a stream network using access to evaluate different watershed scenerios.

 

[WildHare]

What you want here is a SELF join, with the table appearing TWICE in the query grid, and the TO field in the first object linked to the FROM field in the Second object:

Table 1                Table 1_1
From        |--------- From
To  --------|          To
.                      .
.                      .
.                      .

I'm not sure exactly what you're looking for as a finished product, but I think this might get you started....

If not, let us know.

Jim





Don't be sexist - Broads hate that.
Another free Access forum:

http://www.forumco.com/jmhare

More Access help stuff at

http://www.jmhare.com/wildhare.htm

 

[yukonjack]

I tried this already but it only evaluates it for one go around I need to list the values for the entire stream and that is sometimes 2000 records. I tried doing this several times over but Access gave me a query is to complicated error. Thanks anyway