Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Access VBA Nested For Loops

Status
Not open for further replies.

Dauntless0522

Technical User
Dec 8, 2011
11
US
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
 
Many of us can't download documents because of restrictions within our companies, if we can't see the code in your post we won't know what it says.
 

hi,

Welcome to Tek-Tips!

Please explain exactly how the data in these two table relate?

Could you say this???
Code:
select  b.[perfTOP], b.[perfBott], b.[WellName], a.[Rock_Name]

from Table_1 a, Table_2 b

where a.[RockTop]  = b.[perfTOP]
  and a.[RockBott] = b.[perfBott]
  and a.[WellName] = b.[WellName]


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Thank you for both replies. I havent written any code yet since I have never done anything like this before due to it being above my skill level.
The reason it is so hard is that neither tables do relate.
In Skip's example it would have to be:

Where b.[WellName] = a.[WellName]
and b.[perfTOP]>= a.[RockTop]
and b.[perfBott]<=a.[RockBott]

If the perf top and bottom fit in the rock top and bottom range then update Rock_Name in b.[Rock_Name]

If they dont match go to the next record where b.[WellName]=a.[WellName] until it finds a match or if it doesnt then go to next well name in table_2.

Again thanks for the help.

Dauntless0522
 


Did you try joining the tables using the WHERE clause as you stated?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Well the issue is the WellName is not unique in either table. So if I joined them it would grab the first record and thats it. I need it to start with the firs record in table two and find that wellname in table one. If the criteria fits then apply the wellname to table two. If not then look for the next record in table one where the well name matches table two and check the criteria again, and loop til it finds it or runs out of records where the two tables well name matches. IF so then move on to the next well name.

Thanks for your time.

Dauntless
 



Do you understand how table joins occur?

Join them as you have described. Then use THAT resultset to do your row by row analysis.

If it were me, I'd do the Access queies from Excel and then do the row by row with a sheet formula, unless you are prepared to write VBA code to do the analysis.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
I was hoping someone had already written the VBA for something similar so I could edit it to my circumstance.

Maybe I dont understand joins as much as I did. I was under the impression if I were to create a many to many relationship it would link the first record of table 1 to the first record of table 2 and not even consider the other possible records. When I join them on well name and add the criteria most of my Rock Names do not update because I thought the criteria didnt match the first record. I need to keep the integrity of table 2. Again, thank you for your help.

Dauntless
 
first record of table 1 to the first record of table 2
There is no "first record" in relational tables. Tables are unordered relations.
Maybe you need to post a coherent example of data in two tables that demonstrates what your issues are and how you expect to process to occur.


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Table 1

Wellname PerfTop PerfBottom Rock Name
1 50 150
2 250 275
2 450 475
3 600 700
3 875 900


Table 2

Wellname RockTop RockBottom Rock Name
1 100 200 Shale
2 200 300 Sandstone
3 400 500 Dolomite
3 600 700 Quarts
3 800 900 Salt



Table 1(After VBA ran)

Wellname PerfTop PerfBottom Rock Name
1 50 150 Shale
2 250 275 SandStone
2 450 475 No Record
3 600 700 Quartz
3 875 900 Salt


Hope this helps
 
Thanks for the article Skip. This is what I meant that it is random. It links a record to another randomly as long as it fits the join criteria, but if there are several records that fit the join criteria it only chooses one to run through the rest of the criteria.
 



I do not understand why this row qualifies, as perftop is NOT >= rocktop
[tt]
Wellname PerfTop PerfBottom Rock Name
1 50 150 Shale
[/tt]
You have not explained YOUR process logic, how you get to the result, as requested.

Please carefully think this thru before posting inconsistent information.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 



If I use a left outer join using your criteria...
[tt]
Wellname PerfTop PerfBottom RockName
1 50 150
2 250 275 Sandstone
2 450 475
3 600 700 Quarts
3 875 900 Salt
[/tt]
where no rock name is no record.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Sorry for the error it was suppose to be 150 to 200. Thanks for your help skip....unfortunatley I tried a left outer join before I came to this forum.

Because I have more then one well 2 when it goes to table 2 it just looks at one well 2 record. If it randomly grabs the right record with the right depth range it is ok, but most of the time it doesnt so I get no Rock Name.

In the article you sent me, it mentions breaking up the many to many relationship by creating a third linking table. I am going to try that.

I was just hoping someone else had written some vba for something similar already. If I could write a vba module that had a for loop in it I think it would be much easier.

I wrote something similar in excel, but dont know how to write it in access vba and I need it in access.

Dim table2Row as integer, table1Row as integer, rockTopTable2 as integer, rockBottomtable2 as integer, perfTopTable1 as integer, perfBottomTable1 as integer


For table2row=rockTopTable2 to rockBottomtable2

For table1row=perfTopTable1 to perfBottomTable1

If cells(table2row,T2WellNameCOLUMN)=cells(table1row,T1WellNameCOLUMN) then

If cells(table2row,T1PerfTOPCOLUMN)>=cells(table2row,T2RockTOPColumn) and cells(table1row,T1PerfBottomCOLUMN)<=cells(table2row,T2RockBOTTOM) then

Cells(table2row, T2RockNAMECOLUMN)=cells(table1row, T1RockNAMECOLUMN)



End if

exit for

End if

Next

Next





Thanks.
 


Did you look at the result I posted?

Is there a problem with a result like that?

If not, why not?

So far you have not CLEARLY, CONCISELY and COMPLETELY stated your requirements & logic.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Yeah, your results look great, but when I use that process I do not get the same results.

I am a newby to this stuff so patience is required. If I did not state what I needed clearly I am sorry.

I want my results to look exactly the way you last posted. I dont know how to further explain my logic, as I am not very keen to most the terminology.

I just know I have to get the query to loop through the different records until it finds the right depth range. It is my understanding the way it is currently working is it is randomly grabing table1.well 2 (record 1 0f 2)then linking it to table2.well2(record 1 of 2). The issue with it is that record 1 of 2 in table 2 may not be the right depth range, record two could be the correct depth range.

Hope this is more clear.
 


Yeah, your results look great
Post the Outer Join SQL that you are using that is not working.

Come on -- help yourself!

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Anyway, absolutely NO VBA required but SQL.

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


Bottom line, in your Outer Join From Clause...
Code:
.....
   ON b.[WellName] = a.[WellName]
  and b.[perfTOP]>= a.[RockTop]
  and b.[perfBott]<=a.[RockBott]


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Professionalism is appreciated.

UPDATE InjProfiles LEFT JOIN TOPS_TABLE ON InjProfiles.API = TOPS_TABLE.API SET InjProfiles.[Zone] = IIf([InjProfiles]![Top]>=[TOPS_TABLE]![DEPTH] And [InjProfiles]![Bottom]<=[TOPS_TABLE]![BASE],[TOPS_TABLE]![SURFACE],"NO RECORD");



***InjProfiles=Table1
TOPS_TABLE=Table2
TOPS_TABLE.API=Table2_WellName
InjProfiles.API=Table1_WellName
InjProfiles.[Zone]=Table_1RockName
[InjProfiles]![Top]=Table1_PerfTop
[InjProfiles]![Bottom]=Table1_PerfBottom
[TOPS_TABLE]![DEPTH]= Table2_RockTop
[TOPS_TABLE]![Base]= Table2_RockBottom
[TOPS_TABLE]![SURFACE]= Table2_RockName
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top