add query to an existing DAO.Recordset 4

[davyre]

Hi,

dim qry1 as string
dim qry2 as string
dim dbs as database
dim rst as DAO.Recordset

set dbs=currentDb

qry1="SELECT * FROM TblOne"
set rst=dbs.OpenRecordset(qry1)

qry2="SELECT * FROM TblTwo"

My question is can I append the second query to recordset rst? Or maybe can I create another recordset then join both recordset?

 

[Andrzejek]

If TableOne and TableTwo have the same structre (the same number of fields, and the same 'corresponding' fields), you can do:

qry1="SELECT * FROM TblOne [blue]UNION[/blue] SELECT * FROM TblTwo"
set rst=dbs.OpenRecordset(qry1)

Have fun.

---- Andy

 

[davyre]

Hi Andy, what if the TblOne and TblTwo have different field names but the field type is the same (i.e TblOne.UnitID and TblTwo.PartID, but other than that they have the same fields). Can I use UNION?

 

[ClulessChris]

You can still use the UNION but you'll need to match the field names

SELECT 1 as A, 2 as B, 3 as C From table1 UNION SELECT 4 as A, 5 as B, 6 as C From table2

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[Andrzejek]

Yes, you can still use UNION

The best way is to.... try it and see if that works.

ClulessChris - you do NOT need to match the names of fields in UNION - you can if you really want to. The resulting recordset will have the names of fields from first Select


Have fun.

---- Andy

 

[davyre]

I tried to run this query:

selectPartQry = "SELECT TblParts.PartNumber, TblParts.PartDescription, TblParts.VendorID, TblUnitPart.Qty " _
                & "FROM TblParts INNER JOIN (TblUnits INNER JOIN TblUnitPart ON TblUnits.UnitID = TblUnitPart.UnitID) ON TblParts.PartID = TblUnitPart.PartID " _
                & "WHERE TblUnitPart.UnitID=" & UnitID _
                & " UNION " _
                & "SELECT TblAssembly.PartNumber, TblAssembly.PartDescription, TblAssembly.VendorID, TblUnitAssy.Qty " _
                & "FROM TblAssembly INNER JOIN (TblUnits INNER JOIN TblUnitAssy ON TblUnits.UnitID=TblUnitAssy.UnitID) ON TblAsssembly.AssyID=TblUnitAssy.AssyID " _
                & "WHERE TblUnitAssy.UnitID=" & UnitID

but then it gave me error saying "Syntax error in JOIN operation". Any idea where did I make the mistake?

 

[dhookom]

Use debug.print to get the value of selectPartQry. If that doesn't work, then remove the union select and try again. Maybe try just the second select by itself.

This is all basic trouble-shooting 101.

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

ah yes, the problem lies in the second select..Thanks!