Two recordsets combine data

Posted on 2004-11-18
Last Modified: 2010-05-02
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?
Question by:Victoria_
    LVL 8

    Expert Comment

    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 *************
    SQL =

    SELECT * FROM t_table WHERE < expression >
    UNION [ ALL ]
    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
    LVL 10

    Expert Comment

    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

    Author Comment

    I am afraid this does not work. table X and Y are both (different)tables

    LVL 10

    Expert Comment

    could u just elaborate ur question, then

    with some examples..for values and taking two separate tables for X and Y...
    LVL 8

    Expert Comment

    You can use UNION with different tables.

    SELECT name FROM Table1
    SELECT name FROM Table2
    LVL 1

    Accepted Solution

    IN ADO
    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
    LVL 75

    Expert Comment

    by:Anthony Perkins
    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

    Author Comment

    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.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    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

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    Join & Write a Comment

    There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now