Nested looping within records (1 Viewer)

sbrown106

Member
Local time
Today, 17:33
Joined
Feb 6, 2021
Messages
77
Hi - I'm trying to understand how to pull out values from a within related tables. I have a test example below to try and show this problem.
If for example I have 2 tables titled Table1 and Table2. I loop through a particular field in the first table, and with each value, I want to find the record in the second table where this value appears - but then display a different field within that record in table2. Ive tried the code below but it keeps on stopping on the first row. I think I 'm getting confused with excel in vba. I'm new to this and might have done something stupid, but there must be a better way. Should I be using the Dlookup instead? thanks for any help
Code:
Dim rs As DAO.Recordset

Dim rs1 As DAO.Recordset

Dim my_value As Variant



Set rs = CurrentDb.OpenRecordset("Table1", dbOpenSnapshot, dbReadOnly)

Set rs1 = CurrentDb.OpenRecordset("Table2", dbOpenSnapshot, dbReadOnly)



Do Until rs.EOF
' my value a value in a 'Test1ID' in Table1'
my_value = rs![Test1ID]

    Do Until rs1.EOF

        If my_value = rs1![Test2ID] Then
' display a value in a different field in a record in Table2 that satisfy the condition
'
        Debug.Print rs1![fldtest_inRecord2]

        End If

    rs1.MoveNext

    Loop

rs.MoveNext

Loop
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:33
Joined
Feb 28, 2001
Messages
26,999
If looking for matching records, you want to do an INNER JOIN (which is the keyword you should look up.) Basically, you build a query for the two tables and pick the fields that you want to match. From your code sample,

Code:
SELECT T1.Test1ID, some other fields of interest
FROM TABLE1 AS T1 INNER JOIN TABLE2 AS T2 ON T1.Test1D = T2.Test2ID ;

Make this a named query, then you can open it as a recordset and you will ONLY see those records that matched up from the two tables. Then you can pick out the other fields of interest, prefixed by either T1 or T2 depending on which table they were in. Don't need to look at T2.Test2ID because the JOIN assures that it matches T1.Test1ID - so the T2 value in that case would be redundant.
 

sbrown106

Member
Local time
Today, 17:33
Joined
Feb 6, 2021
Messages
77
What happens if you tried that?
I only seem to keep getting the first record hit and then nothing. I cant loop through the recordset.I think there must be something wrong with the code, I'll try the SQL approach from the The_Doc_Man
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:33
Joined
May 7, 2009
Messages
19,169
Code:
Dim rs As DAO.Recordset
Dim my_value As Variant

Set rs = CurrentDb.OpenRecordset( _
    "Select A.Test1ID, B.fldtest_inRecord2 FROM Table1 AS A " & _
    "INNER JOIN Table2 AS B ON A.Test1ID = B.Test2ID", dbOpenSnapshot, dbReadOnly)





Do Until rs.EOF

'
        Debug.Print rs![fldtest_inRecord2]


    rs.MoveNext

Loop
rs.Close
Set rs = Nothing
 

sbrown106

Member
Local time
Today, 17:33
Joined
Feb 6, 2021
Messages
77
If looking for matching records, you want to do an INNER JOIN (which is the keyword you should look up.) Basically, you build a query for the two tables and pick the fields that you want to match. From your code sample,

Code:
SELECT T1.Test1ID, some other fields of interest
FROM TABLE1 AS T1 INNER JOIN TABLE2 AS T2 ON T1.Test1D = T2.Test2ID ;

Make this a named query, then you can open it as a recordset and you will ONLY see those records that matched up from the two tables. Then you can pick out the other fields of interest, prefixed by either T1 or T2 depending on which table they were in. Don't need to look at T2.Test2ID because the JOIN assures that it matches T1.Test1ID - so the T2 value in that case would be redundant.
Hi The_Doc_Man, Ive done as you mentioned and I now have a single list (Test1ID) of values in a qry that match values in the same field (Test2ID) in the second table. So to get the field values in the second table that correspond to these matching fields doesnt that mean I'm still looping through 2 recordsets - the qry and Table2 in vba? Sorry I may be misunderstanding your answer
 

sbrown106

Member
Local time
Today, 17:33
Joined
Feb 6, 2021
Messages
77
Code:
Dim rs As DAO.Recordset
Dim my_value As Variant

Set rs = CurrentDb.OpenRecordset( _
    "Select A.Test1ID, B.fldtest_inRecord2 FROM Table1 AS A " & _
    "INNER JOIN Table2 AS B ON A.Test1ID = B.Test2ID", dbOpenSnapshot, dbReadOnly)





Do Until rs.EOF

'
        Debug.Print rs![fldtest_inRecord2]


    rs.MoveNext

Loop
rs.Close
Set rs = Nothing
Thanks that's worked !
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 04:33
Joined
Jan 20, 2009
Messages
12,849
Looping through recordsets is incredibly inefficient and should be avoided wherever possible. Database engines are designed to process the entire dataset. Use joins between tables like Doc and arnelg show.

Avoid repeatedly using DLookups. Every time you call one is like running another query.
 

sbrown106

Member
Local time
Today, 17:33
Joined
Feb 6, 2021
Messages
77
Code:
Dim rs As DAO.Recordset
Dim my_value As Variant

Set rs = CurrentDb.OpenRecordset( _
    "Select A.Test1ID, B.fldtest_inRecord2 FROM Table1 AS A " & _
    "INNER JOIN Table2 AS B ON A.Test1ID = B.Test2ID", dbOpenSnapshot, dbReadOnly)





Do Until rs.EOF

'
        Debug.Print rs![fldtest_inRecord2]


    rs.MoveNext

Loop
rs.Close
Set rs = Nothing
Is it possible to include an additional field from table A in the output that meets these conditions ?- I thought it might be A.test2ID included in the SELECT statement and then A.Test2ID in the debug - but that not working
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:33
Joined
May 7, 2009
Messages
19,169
add Test2ID to your query:
Code:
Set rs = CurrentDb.OpenRecordset( _
    "Select A.Test1ID, B.Test2ID, B.fldtest_inRecord2 FROM Table1 AS A " & _
    "INNER JOIN Table2 AS B ON A.Test1ID = B.Test2ID", dbOpenSnapshot, dbReadOnly)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:33
Joined
Feb 28, 2001
Messages
26,999
ArnelGP beat me to the answer, but then again I think he has a time-zone advantage here.

The "SELECT bunch-of-fields" is where you list the fields you want to see. If you have the A and B aliases (declared by the "AS x" sub-clause), then you can specify fields from EITHER table simply by qualifying in that SELECT field-list. I.e. A.field-name or B.field-name - for all of the fields as you wish.
 

sbrown106

Member
Local time
Today, 17:33
Joined
Feb 6, 2021
Messages
77
ArnelGP beat me to the answer, but then again I think he has a time-zone advantage here.

The "SELECT bunch-of-fields" is where you list the fields you want to see. If you have the A and B aliases (declared by the "AS x" sub-clause), then you can specify fields from EITHER table simply by qualifying in that SELECT field-list. I.e. A.field-name or B.field-name - for all of the fields as you wish.
Thanks for that Doc, its working now - the explanation in english helps I was struggling to understand the statement
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:33
Joined
Feb 28, 2001
Messages
26,999
Glad to have contributed. (Sorry I'm late checking in, but today became a grandson day, and I DO have my priorities.)
 

Users who are viewing this thread

Top Bottom