Results 1 to 6 of 6
  1. #1
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56

    Need Alternative to Union

    I have this query started my table has the parents and children records. MS Access doesn't allow Cursors, so is there another way I could write this? There could actually be more children of the children, which than require me to make several more Unions.


    SELECT tblEquip.keyEquip, tblEquip.keyShip, tblEquip.strFPN, tblEquip.keyCmpt, tblEquip.strDescription, tblCable.strCableID, tblCable.strCombatID, tblCable.keyEquipUp, tblCable.keyEquipDn,'Parent'
    FROM tblEquip INNER JOIN tblCable ON tblEquip.keyEquip = tblCable.keyEquipUp


    WHERE tblEquip.keyShip=8 AND tblEquip.strFPN='PWR-PL-008'
    UNION
    SELECT tblEquip.keyEquip, tblEquip.keyShip, tblEquip.strFPN, tblEquip.keyCmpt, tblEquip.strDescription, tblCable.strCableID, tblCable.strCombatID, tblCable.keyEquipUp, tblCable.keyEquipDn,'Child'
    FROM tblEquip INNER JOIN tblCable ON tblEquip.keyEquip = tblCable.keyEquipDn
    Where tblEquip.keyShip=8 and
    tblEquip.keyEquip in
    (
    SELECT tblCable.keyEquipDn
    FROM tblEquip INNER JOIN tblCable ON tblEquip.keyEquip = tblCable.keyEquipUp
    WHERE tblEquip.keyShip=8 AND tblEquip.strFPN='PWR-PL-008'
    )

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Provide sample data and desired output. If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    so is there another way I could write this?
    given you say

    There could actually be more children of the children, which than require me to make several more Unions.
    implies the query doesn't actually do what you want. So suggest explain in simple English your table structure and relationships and what is you actually require

  4. #4
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56
    Sorry I did not properly explain myself.

    However I decided to write the SQL in VB and Loop through the data until I find all the records. Now I'm trying to figure out how I can push this data into a report.

    But these are the table Formats.

    tblEquip.KeyEquip is a primary key and tblCable.keyEquipUp and tblCable.keyEquipDn are like Fkeys. The tblCable.keyEquipUp and tblCable.keyEquipDn can be found in the tblEquip as the Primary

    tblEquip
    keyEquip
    keyShip
    strFPN
    keyCmpt
    strLoc
    strID
    strEquipType
    strDescription
    strNotes
    strInputSource
    strCreatedBy
    dtCreatedDate
    strLastUpdatedBy
    dtLastUpdatedDate

    tblCable
    strCableID
    strCombatID
    strCableID
    strCombatID
    strCableType
    keyEquipUp
    keyEquipDn

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Now I'm trying to figure out how I can push this data into a report.
    one way, when you are looping in VBA create a disconnected ado recordset, then when you open the report you assign the recordset to the report

    possible code

    Code:
    dim drst as dao.recordset
    dim arst as ado.recordset
    
        Set aRst = CreateObject("ADODB.Recordset")
        
        With aRst
            .Fields.Append "NameOfField1", 11 'boolean
            .Fields.Append "NameOfField2", 200, 30 ' advarchar
            'etc
            
            .CursorLocation = 3 'adUseClient
            .LockType = 3 'adLockOptimistic
            .cursortype = 3 'adOpenStatic
            .Open
        end with
    
        set drst=currentdb.openrecordset("whatever you are using now")
        while not drst.eof
            arst.addnew
            arst!NameOfField1=arst!NameOfFieldToAssign
            arst!NameOfField2=arst!NameOfAnotherFieldToAssign
            'etc
            arst.Update
            drst.movenext
        wend
                
        'open report and assign recordset
        docmd.openreport "myReport"
        set reports!myReport.Recordset=arst
    Alternatively you might put this code in the report load event

  6. #6
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56
    thank you for you help

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-09-2017, 12:06 PM
  2. Convert Union Query to non-union help
    By Ekhart in forum Queries
    Replies: 2
    Last Post: 01-10-2017, 03:39 AM
  3. Replies: 4
    Last Post: 12-20-2015, 02:35 PM
  4. Dlookup alternative
    By scotty22 in forum Queries
    Replies: 19
    Last Post: 10-26-2011, 06:20 AM
  5. Alternative to mapnetworkdrive?
    By dwcolt in forum Programming
    Replies: 1
    Last Post: 05-11-2011, 08:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums