• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

Two recordsets combine data

Hi I have two recordsets X and Y

X is the basic recordset, items in Y are retrieved with another recordset.
Some of the data in Y has to be combined(added field) with values from  recordset X. If a certain record in X is 0 some data from Y has to be retrieved.  What is the best way to do this in the recordset itself or in an array. Output of recordst or array has to go to Excel.

I hope this question is clear to you?
  • 2
  • 2
  • 2
  • +2
1 Solution
I would create a UNION query to combine your data from X and Y into a single recordset then use ADO to push it to EXCEL. Below will give oyu the general idea but you'll need to build the SQL statement.

Public DB As New ADODB.Connection
Public myRS As New ADODB.Recordset

Private Sub Command1_Click()
Dim DBPath As String
Dim startCell As Range
Dim objXL As New Excel.Application
Dim wbXL As New Excel.Workbook
Dim wsXL As New Excel.Worksheet

DBPath = "C:\Documents and Settings\Desktop\db1.mdb"

Set DB = New ADODB.Connection
DB.Open "Provider=Microsoft.Jet.OleDB.4.0;Data Source=" & DBPath

'************* SELECT and UNION statement *************

SELECT * FROM t_table WHERE < expression > 
SELECT * FROM t_table WHERE < expression >

strsql = SQL

'Debug.Print strsql

Set myRS = DB.Execute(strsql)
objXL.Visible = True
Set wbXL = objXL.Workbooks.Add
Set wsXL = objXL.ActiveSheet
Set startCell = wsXL.Range("A2")                                

startCell.CopyFromRecordset myRS

    Application.StatusBar = "Database Error"
    MsgBox Err.Description

End Sub
One Way could be:

Step1: Fetch Data For X
Step2: Fetch Data for Y In Correspondence to Another Recordset object
Step3: Fetch Data For Z (a Recorset which is equal to Y)
        for above Step do this Set Z = Y
Step4: Open The Excel Sheet Where You Want to Store the Data

while not X.EOf
  if X!SomeField = 0 then
    'Filter  Recordset Z with whatever Criteria u want to
    Var1 = Z!SomeFieldValue
    Var1 = X!SomeField + Y!SomeOtherField
  end if
  'Transfer the Value of Each Variable into Excel Sheet

Close the Excel Sheet
And Other Objects
Victoria_Author Commented:
I am afraid this does not work. table X and Y are both (different)tables

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

could u just elaborate ur question, then

with some examples..for values and taking two separate tables for X and Y...
You can use UNION with different tables.

SELECT name FROM Table1
SELECT name FROM Table2
Dim x as  ADODB.Recordset,y as  ADODB.Recordset

Set x = New ADODB.Recordset
Set y = New ADODB.Recordset
xSQL = "Query"'Your SQL QUERY to get the X Records
ySQL = "Query"'Your SQL QUERY to get the Y Records
g_Conn 'Your connection

x.Open xSQL, g_Conn
y.Open ySQL, g_Conn

While not x.EOF
If x.Field(1)=0 Then
sID = x.Field(1)'Identifies this record

y.Filter = "ID='sID'"     on your y recordset search for the record relating to your x record

'get whatever data from the y record
y.Fields (0) etc
y.Fields (1) etc

'remove the filter
y.Filter = adFilterNone

End IF
Anthony PerkinsCommented:
Despite repeated reminders the questioner has ignored all requests to maintain these abandoned questions:
1 10/12/2004 250 passing value from child form to parent ...  Open Visual Basic
2 10/07/2004 333 Select language column from resource fil...  Open Visual Basic
Victoria_Author Commented:
Dear acperkins
Thank you very much for mentioning my open questions. I immediately tried to close as many open questions as possible.
I am glad you are so interested in my question history. I find it a shame that you did not point out all my questions that are closed/PAQ.
Nevertheless your answer on my question about Two recordsets combine data was "very usefull". I hope you have an answer on my 2 open questions instead of being the COP without an answer.
Regards and again thank you for your answer.
Anthony PerkinsCommented:
Thank you for closing one of your questions, however you appear to have overlooked this one:

You will find experts here more responsive if you take the time to follow up on your open questions.  If you feel the answer offered in that thread was not correct than say so.  We are here to help you, unfortunately we cannot do that on our own we need your help to do that.


Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now