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

    Join Query ? (A2K SR3)

    I have two queries, qrySCSalesTaxWorksheet and qrySCSalesTaxExempt.. QrySCSalestaxWorksheet selects records based on the report period, certain payment codes, if tax proration is greater than zero and the rental is not tax exempt. The tax proration field is the amount of revenue which is tax exempt i.e. rentals over 90 days except for rentals that are tax exempt from day 1. I had to exclude tax exempt remtals here because you could have a tax exempt rentals that are tax exempt from day 1 but would also be tax exempt because it is over 90 days.

    Under certain conditions rentals are tax exempt from day 1. When tax exempt from day 1, there is a check box name TaxCode in tblRental that was checked on the input form, frmCheckIn during data entry. Thus, qrySCSalesTaxExempt selects only those records that are tax exempt, i.e. where TaxCode=-1, during the report period regardless of the rental duration. I want to be able to create a third query that will take the Proration field from the 1st query and add to it Total Room field from the 2nd query to get a Total Tax Exempt amount.

    The source for both queries is qryRentCalc3 which gets its data from several tables.

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

    Re: Join Query ? (A2K SR3)

    Does qryRentCalc3 return *all* records? If so, create a new query based on qryRentCalc3, qrySCSalesTaxWorksheet and qrySCSalesTaxExempt; join the first to the two others by an outer join (display ALL records from qryRentCalc3). The amount you want can be calculated as:

    Total Tax Exempt: Nz([qrySCSalesTaxWorksheet].[Proration],0)+Nz([qrySCSalesTaxExempt].[Total Room],0)

    The Nz function replaces Null values by 0; this is necessary because Null+something = Null.

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

    Re: Join Query ? (A2K SR3)

    Hans,

    Thanks for your reply.

    QryRentCalc3 selects certain records from the database that fall within the report period and have certain pay codes. For the month of October there were 70 records selected by qryRentCalc3.

    QrySCSalesTaxWorksheet further selects 24 records from the 70.

    QrySCSalesTaxExempt selects 3 records of the 70, none of which are in the 24.

    What I am trying to do is get the new query to display the 24 records from qrySCSalesTaxWorksheet plus the 3 records from qrySCSalesTaxExempt

    If I can get the 27 records returned by the new query, I think I can get the TotalTaxExempt field to return the correct values I need.


    I tried to create a new query with the outer join as suggested. I used the OrderID (automuber) field as the join field. The new query returned all the records in the database, i.e. 842. Also, I was not sure which query to use to display the data.

    Tom

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

    Re: Join Query ? (A2K SR3)

    Tom,

    I have obviously misunderstood your original question. From
    <hr>take the Proration field from the 1st query and add to it Total Room field from the 2nd query<hr>
    I concluded that there was overlap in the records returned by the queries, and that you wanted to add the two amounts for those records.

    From the description in your reply, I would say that you need a completely different type of query: a union query. Union queries can only be created in SQL view, not in design view. Create something like this, substituting the appropriate field names:

    SELECT [Field1], [Field2], ..., [Proration] As [Total Tax Exempt] FROM [qrySCSalesTaxWorksheet]
    UNION
    SELECT [Field1], [Field2], ..., [Total Room] FROM [qrySCSalesTaxExempt]

    Note that corresponding fields must be in the same position, and that you have to provide the new name for the combined field only in the first SELECT statement.

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

    Re: Join Query ? (A2K SR3)

    The UNION query works fine. As usual. 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
  •