Combine 3 RecordSets using VBA

[RevJeff]

I have three separate recordsets that have the same fields. Is there a way to combine them into one recordset?

TIA

 

[Gasman]

Create a union query, and use that for your recordset?

 

[RevJeff]

Create a union query, and use that for your recordset?

Let me explain further. The table I am using has all of the fields. Within that table there is a field called "Tiers" and they can be "Tier 1", "Tier 2" and "Tier 3". The current recordset looks at all records from yesterday and assigns them to the users for review. Because of the volume of records the users no longer want to review all of the records. They want to review 90% of Tier 1 records, 85% of Tier 2 records and 75% of Tier 3 records.

So my plan was to create three different recordsets, Tier 1, Tier 2 & Tier 3, select the correct percent of each Tier, then combine the recordsets back into one recordset and then assign them to the users.

 

[Gasman]

Do a dcount for each tier.
Do the math.
Create the sql for the union query.
Use that as your recordset.

 

[sonic8]

So my plan was to create three different recordsets, Tier 1, Tier 2 & Tier 3, select the correct percent of each Tier, then combine the recordsets back into one recordset and then assign them to the users.

If you combine the three queries into one using a UNION query, as @Gasman suggested, you will get the desired result.

With ADO Recordsets it would also be possible to copy the records from multiple recordsets into one new recordset. However, this would be rather slow row-by-row operation. The UNION query is a better alternative.

 

[Gasman]

If you are sketchy writing sql in vba, try out this utility.