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

    Comparison Report (A2K Sp3)

    I have been asked to produce a report that will compare Room Revenue (TotalRoom) by location for an imputed period of time (Beg Date-End Date) to a period 1 year earlier.

    I have developed a report by location for an imputed period of time but don

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

    Re: Comparison Report (A2K Sp3)

    You can create a query qryIncomeSummary1YearBefore that will get the data for the same period one year earlier:

    SELECT ...
    FROM ...
    WHERE ... AND ((qryRentCalc0.RentPaidDate) Between DateAdd("yyyy",-1,[ReportBeg]) And DateAdd("yyyy",-1,[ReportEnd])));

    You can then combine qryIncomeSummary and qryIncomeSummary1YearBefore in a new query. You will have to decide on which field(s) the queries should be joined.

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

    Re: Comparison Report (A2K Sp3)

    Thanks for pointing me in the right direction. Have produced the new query and it works fine. Will join the 2 queries tomorrow.

    Thnaks again

    Tom

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

    Re: Comparison Report (A2K Sp3)

    The 2 individual queries and the reports based on the queries work fine. The report layout is currently
    Column 1 Location
    Column 2 Room Revenue

    I have been unable to put the two queries together to produce the desired results. I haven

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

    Re: Comparison Report (A2K Sp3)

    Which field in the query provides the data for the Location column?

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

    Re: Comparison Report (A2K Sp3)

    PCity

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

    Re: Comparison Report (A2K Sp3)

    In tblRental the PropertyID field is the field that associates a particular record with a particular location.

    Tom

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

    Re: Comparison Report (A2K Sp3)

    Try the following:
    - Create a new query.
    - Add qryIncomeSummary and qryIncomeSummary1YearBefore.
    - Join the queries on PCity.
    - Double click the join line.
    - Select the option to return ALL records from qryIncomeSummary.
    - Add the fields you want; give those from qryIncomeSummary1YearBefore suitable aliases to distinguish them from the fields of the same name in qryIncomeSummary.
    - Save the new query and use it as record source for your report.

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

    Re: Comparison Report (A2K Sp3)

    Tried your suggestion. Both option 2 and option 3 returned 596,072 records. Also tried joining PropertyID. Both Option 2 and 3 still returned 596,072 records.

    Tom

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

    Re: Comparison Report (A2K Sp3)

    If you only want the four columns in the report, try creating a Totals query based on qryIncomeSummary that groups by PCity and sums on the revenue field. Do the same for qryIncomeSummary1YearBefore. Then create a query based on the two new queries, joined on PCity. You should get one record per location now.

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

    Re: Comparison Report (A2K Sp3)

    Many Thanks as usual. Works perfectly.

    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
  •