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

    Report based on 2 queries (A2k SR1)

    I have a report, rptOccupancy, that prints each property location, the number of days each
    property was occupied and the % Occupied. The original version did not take into account when
    the property opened. If the property was not open or opened during the report period, the divisor
    has to be adjusted. I have corrected the formula so it calculates the individual % Occupied
    correctly based on the [OpeningDate], a field in tblPropertyName.

    Everything is working correctly in rptOccupancy except the grand total %. In an attempt to find a
    solution, I have written qryOccDuration that has a calculated field, [TotalOccDays], that
    calculates the divisor for each property. The divisor consists of the number of units for each
    property times the [OccDuration]. This query accesses data only from tblPropertyName which
    has 1 record for each property.

    RptOccupancy uses data from qryOccupancy that uses tblPropertyName, tblRental,
    qryCustomerSorted(tblCustomer). QryOccupancy returns all the individual rental records for
    each property during the report period.

    Is there a way that rptOccupancy can access the data in both queries so that I can use the
    Sum of [TotalOccDays] in qryOccDuration as the divisor to calculate the Grand Total %
    Occupied in rptOccupancy?

    Any suggestions would be appreciated?

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

    Re: Report based on 2 queries (A2k SR1)

    Tom, you seem to have posted this twice. Maybe you can delete the other post.

    Try DSum("TotalOccDays", "qryOccDuration") as divisor in the control source of the grand total %.

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

    Re: Report based on 2 queries (A2k SR1)

    Sorry abut the double posting. I deleted the duplicate.

    Many thanks for your suggestion. It works great!

    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
  •