Joining Recordsets

[rgbanse]

I've created 2 recordsets
Set Rcdst01 = ThisDatabase.OpenRecordset(RcdSt01SqlString, dbOpenDynaset)
Set RcdSt02 = ThisDatabase.OpenRecordset(RcdST02SqlString, dbOpenDynaset)
I would like to create a 3rd recordsset joining the first 2.
The resulting recordset would contain Fields 1,2,3 from RcdST01 and fields 4,5,6 from both recordsets. the join would be on fields 1,2,3 of both RcdSt's

Here's where I'm at so far - no working
'SELECT RcdSt01.*, RcdSt02.* " & _
"FROM RcdSt01 " & _
"INNER JOIN RcdSt01 AS RcdSt02 " & _
"ON (RcdSt01!Fld1 = RcdSt02!Fld1) " & _
"AND (RcdSt01!Fld2 = RcdSt02!Fld2) " & _
"AND (RcdSt01!Fld3 = RcdSt02!Fld3);"

thx
RGB

 

[PHV]

What are RcdSt01SqlString and RcdST02SqlString ?


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

 

[rgbanse]

SQL strings used to create the recordsets

 

[PHV]

Again, what are the SQL codes ?

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

 

[rgbanse]

RcdSt01SqlString = SELECT dmacs_item.im_division, dmacs_item.im_site, dmacs_item.im_item_nbr, dmacs_item.im_int_desc FROM dmacs_item;
when used with Set Rcdst01 = ThisDatabase.OpenRecordset(RcdSt01SqlString, dbOpenDynaset), it creates a dynamic record set

 

[dhookom]

RGB,
Why did you reply with only one SQL statement? If you want to combine two recordsets, it seems fairly evident you need to provide two SQL statements. A

ttempting to join recordsets like your first post is not going to be successful. PH would like to help you combine the SQL statements of the 2 recordsets. No one can help you with this, if we don't have a clue what the 2 SQL statements are.

Duane
Hook'D on Access
MS Access MVP

 

[Remou]

You cannot do it like that, you would need something on the lines of:

strSQL="SELECT " r1.Fields(0).Name & "," _
& r1.fields(1).Name & "," _
& r1.fields(2).Name & "," _
& r1.fields(3).Name & "," _
& r1.fields(4).Name & "," _
& r1.fields(5).Name & "," _
& r2.fields(3).Name & "," _
& r2.fields(4).Name & "," _
& r2.fields(5).Name _
& " FROM " & r1.Fields(0).SourceTable
& " INNER JOIN " & r2.Fields(0).SourceTable
& " ON " & r1.Fields(0).Name & "=" _
& r2.fields(0).Name & " AND " _
& r1.fields(1).Name & "=" _
r2.Fields(1).Name & " AND " _
& r1.fields(2).Name & "=" _
& r2.fields(2).Name

Which you can see is quite tedious, so why not just write out the correct sql line and use that?