How to make inner join (SQL) in VBA ?

[Flippertje]

Does anyone knwo how to make an inner join in VBA between two recordsets?

I have to recordsets (rst1 and rst2).

rst1
Field1: Id_Test
Field2: Description

rst2
Field1: Id_Test

rst2 is a grouped query on a table with lots of Id_Test. However not all Id_Test in rst2 are in rst1.

I'm trying to show only the Id_Test (that are in rst2) joined with the escription in rst1. I'ld rather solve this in SQL so that i can create "rst3".
The answer will be something like:

dim rst3 as recordset

rst3="SELECT ...."

I hope i made it clear. It's hard trying to do so while you're dutch

Anyone?

Many thanks!

grtz Flippertje

 

[addy]

Easiest way, create a new query which produces the results you want and then change the view to SQL. This will give you what you need

 

[ddiamond]

select b.Id_Test, a.Description
from table1 a inner join table2 b on a.Id_Test = b.Id_Test

You didn't mention the name of your tables, so I used table1 and table2.

 

[Zion7]

Both addy and ddiamond are correct but,
your question, "...between 2 recordsets...", NO!

You make the join first, then create the recordset.

Dim SQL As string, rec As New ADODB.Recordset

SQL = "select b.Id_Test, a.Description " & _
"from table1 a inner join table2 b on a.Id_Test = b.Id_Test"

rec.open SQL, currentProject.Conn.....

begrijp je, wat ik bedoul?