Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Union Query (A2K SP3)

    I have a union query, qryNCTotalTaxExempt whose source comes from qryNCSalesTaxWorksheet and qryNCTaxExemptWorksheet. The purpose of the Union query to calculate those records where the stay is over 90 days from the Initial Rent Date.

    The query been working well for years. In December an error occurred. QryNCSalesTaxWorksheet returns the correct number of records – 8. QryNCTaxExempt returns zero records because there are no tax-exempt records. When the union query, qryNCTotalTaxExempt is run only 6 records are returned when all 8 records need to be returned.

    Tom Jones records – Initial rent date 08/26/07
    Order Rent Begin # of Days Room Cummulative
    ID# Date Current Stay # Days stayed
    6667 12/02/07 7 117 105
    6688 12/09/07 7 117 112
    6689 12/16/07 7 117 119
    6690 12/23/07 7 112 126
    6692 12/30/07 7 112 133

    Joe Smith records – Initial Rent Date 08/12/07
    6696 12/02/07 7 112 119
    6697 12/09/07 7 112 126
    6698 12/16/07 7 112 133

    On 12/23/07 Joe Smith checked out of room 112 and Tom Jones transferred from room 117 to 112. I think the problem must involve the transfer and the same Cummulative Dates, but I can’t figure out to solve the problem.

    I have attach a stripped down version of the database.

    Tom
    Attached Files Attached Files

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

    Re: Union Query (A2K SP3)

    By default, a union query suppresses duplicate records. To make it return all records, including duplicates, use UNION ALL instead of UNION:

    SELECT [PCity], [ReportBeg], [ReportEnd], [RoomNumber],[TotalStay], [TotalRoom], [RoomProration] FROM [qryNCSalesTaxWorksheet]
    UNION ALL SELECT [PCity], [ReportBeg], [ReportEnd], [RoomNumber], [TotalStay], [TotalRoom], [RoomProration] FROM [qryNCtaxExemptWorksheet];

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query (A2K SP3)

    Many Thanks.

    Tom

Posting Permissions

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