Access VBA Nested For Loops

[Dauntless0522]

I have two tables in an access database.
Table_1 has a [RockBott]](base depth of a type of rock) and [RockTop](start depth of a type of rock), [Rock_Name], and [WellName](not unique)

Table_2 includes [perfTOP](a start depth of perforations in Rock), [perfBott] (base depth of perforations to the rock), and [WellName](not unique)

The final goal is to add the correct [Rock_Name] to table two.
I have attached a document with table examples for visual purposes.

Thanks for any help,

Dauntless0522

 

[PHV]

What about this ?

UPDATE InjProfiles LEFT JOIN TOPS_TABLE
ON InjProfiles.API = TOPS_TABLE.API
AND InjProfiles.Top >= TOPS_TABLE.DEPTH
AND InjProfiles.Bottom <= TOPS_TABLE.BASE
SET InjProfiles.Zone = Nz(TOPS_TABLE.SURFACE,"NO RECORD")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Why are you trying to update this table?

Wouldn't a Select query give you the information? It is not a good idea to have multiple table with derived data (Rock Name).

But that said, you have not used the <= and >= joins in your Left outer Join!!!

Look at my previous post.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[exoscoriae]

Correect me if i am wrong, but what I believe Dauntless0522 is trying to do is the following:

For every well with the same ID (as there will be many matches), compare the top and bottom rock perfs to the t=rock tops & bottoms.

If the well name matches and the perfs meet the crieteria, then copy the rock name over to table 2.

In my experience I used loop statements to do this wort of work, but if there is a way to do it using a left join, that would obviously be easier.

 

[Dauntless0522]

Yes Exo that is exactly what I want to do. Youre a genius interpreter

Thank you for your post PHV, but when I pasted it into my design view it was saying that I cannot use "<" or ">".

Specifically:
the join uses an operator that isnt supported in design view. Is there another area I should be posting this in.

FYI I am in v2007

 

[PHV]

I personally never use the query grid, but the SQL view.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Yes SQL.

Just paste in PHV's SQL

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Dauntless0522]

I got the sql to work, but I am still having the same issue. The querry is just linking one record from one table to the other and ignoring all the other records that also fit the link criteria, therefore I am not getting the right results.

Thanks for everyones help, I have spent way too much time on this and will just look to other ways of doing this.

Dauntless

 

[SkipVought]

Post DATA examples please, along with the actual SQL you are using.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}