combining two recordset together

[vikram_singh]

Hi,
I have two recordset (rst1 and rst2) and basically is there any way by
which i can combine two results together.

FYI: both recordsets are having same columns.

Vikram

 

[ruralguy via AccessMonster.com]

Look into a UNION query. It is only available in the SQL View of the Query
Builder.

 

[vikram_singh]

Hi,
Thanks for your reply!

things that i am doing cannot be possible with union. So havn't had
any other option and gone thru with making two resultsets. I am
wokring with ms access and excel and making cross tab and producing
into excel. my corss tab has group by clause and as far as i know
union clause is not possible with group by clause.

Kindly do let me know if there is a possibility to combine two result
sets.

Regards,
Vikram Singh

 

[Scott McDaniel]

Hi,
Thanks for your reply!

things that i am doing cannot be possible with union. So havn't had
any other option and gone thru with making two resultsets. I am
wokring with ms access and excel and making cross tab and producing
into excel. my corss tab has group by clause and as far as i know
union clause is not possible with group by clause.

You can use temporary tables. Combine your two recordsets into a single temporary table, then base your crosstab on that
table.

Kindly do let me know if there is a possibility to combine two result
sets.

Regards,
Vikram Singh

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Michel Walsh]

Not really (short of appending in a loop rows from rst2 to rst1), but can't
you open the recordset with an UNION statement:

Dim sql AS string
sql="SELECT *, 1 AS source FROM source1 UNION ALL SELECT *, 2 FROM
source2 ORDER BY source"


and open your single recordset (ADO or DAO) based on that statement.


I assumed both 'sources' have the same number of fields, and are compatible.
The order by statement I added is to be sure the records in the first
'source' will be before those of the second.




Vanderghast, Access MVP