Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help With Union Query? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

    I have the following Union query that returns 494 records

    <pre>SELECT tblBPSubCon.strDescription AS CName,
    tblBPSubCon.lngSubConCodeID AS CID,
    tbl_BP_OtherLookup.strCode AS CAT,
    tblBPermit.lngBPermitID AS PermitID,
    tblBPermit.strPermitNo AS PermitNo,
    tbl_BP_OC.strOutcomeCode AS OC, "S" AS T
    FROM (tblBPermit INNER JOIN tbl_BP_OC ON tblBPermit.lngOutcomeID =
    tbl_BP_OC.lngOutcomeID)
    INNER JOIN (tblBPSubCon INNER JOIN tbl_BP_OtherLookup ON tblBPSubCon.lngAccountTypeID =
    tbl_BP_OtherLookup.lngID) ON tblBPermit.lngBPermitID = tblBPSubCon.lngBPermitID
    WHERE (((tblBPSubCon.lngSubConCodeID)>1)
    AND ((tbl_BP_OC.strOutcomeCode) Between "30" And "32"))

    UNION
    SELECT tblBPermit.strBuilder AS CName,
    tblBPermit.lngAcctNoID AS CID,
    tbl_BP_OtherLookup.strCode AS CAT,
    tblBPermit.lngBPermitID AS PermitID,
    tblBPermit.strPermitNo AS PermitNo,
    tbl_BP_OC.strOutcomeCode AS OC, "P" AS T
    FROM (tblBPermit INNER JOIN tbl_BP_OC ON tblBPermit.lngOutcomeID =
    tbl_BP_OC.lngOutcomeID)
    INNER JOIN tbl_BP_OtherLookup ON tblBPermit.lngAccountTypeID = tbl_BP_OtherLookup.lngID
    WHERE (((tblBPermit.lngAcctNoID)>1)
    AND ((tbl_BP_OC.strOutcomeCode) Between "30" And "32"))
    ORDER BY CName;
    </pre>


    When I run the first query separately as a select query I get 245 records
    When I run the second query separately as a select query I get 250 records

    Why am I not getting 495 records in the Union query?

    Thanks, John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help With Union Query? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

    Union queries by default suppress duplicate records. If you want to return all records from both queries including duplicates, use UNION ALL instead of UNION.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With Union Query? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

    Perfect!

    You told me that before on 06/08/04 Question On Union Query?

    Thanks, John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help With Union Query? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

    Shall I tell you again on 11/21/06? <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •