Results 1 to 3 of 3
  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

    Union Query Returning Too Mant Records? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have the following Union Query that works except it is returning 30 records with no dates in front of 1,500 records with dates.

    I only want records that have a dtmInsExpDate and dtmDisabExpDate

    If a record has both dates I want 2 records returned.

    If a record only has dtmInsExpDate I want 1 record returned, etc

    Any thoughts?

    John

    <pre>SELECT tblContractor.strBusinessName as BusinessName,
    tblContractor.dtmInsExpDate as Dates, "Liability", tblContractor.lngAccountTypeID FROM tblContractor
    UNION SELECT tblContractor.strBusinessName as BusinessName,
    tblContractor.dtmDisabExpDate as Dates, "Comp", tblContractor.lngAccountTypeID
    FROM tblContractor
    WHERE (((tblContractor.lngAccountTypeID)=39))
    and (((tblContractor.dtmInsExpDate) Is Not Null))
    and (((tblContractor.dtmDisabExpDate) Is Not Null))
    ORDER BY Dates , BusinessName;
    </pre>


  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query Returning Too Mant Records? (a2k (9.0.6926) SP-3 Jet 4.0 S

    I think you should set the where clause in both select part, each for one date. (on the fly code as I have no tables to test it)
    <pre>SELECT tblContractor.strBusinessName as BusinessName,
    tblContractor.dtmInsExpDate as Dates, "Liability", tblContractor.lngAccountTypeID FROM tblContractor
    WHERE (((tblContractor.lngAccountTypeID)=39))
    and (((tblContractor.dtmInsExpDate) Is Not Null))
    UNION SELECT tblContractor.strBusinessName as BusinessName,
    tblContractor.dtmDisabExpDate as Dates, "Comp", tblContractor.lngAccountTypeID
    FROM tblContractor
    WHERE (((tblContractor.lngAccountTypeID)=39))
    and (((tblContractor.dtmDisabExpDate) Is Not Null))
    ORDER BY Dates , BusinessName;</pre>

    Francois

  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: Union Query Returning Too Mant Records? (a2k (9.0.6926) SP-3 Jet 4.0 S

    Right on!

    Thanks

Posting Permissions

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