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

    Selection Query (A2KSP-3)

    I have a query, qryRentDue which is used to provide data for rptRentDue

    The operator enters the reports dates and the selection criteria is whether or not the
    CheckOutDate is Between [ReportBeg] and [ReportEnd]. This works find unless the guest has
    renewed during the report period.

    For example a quest rents for 3 weeks beginning 5/5/03 thru 05/26/03. On 5/23/03 the guest
    renews for another 3 weeks from 5/26/03 to 6/16/03. If a weekly report is being run for the week
    5/24/03 -5/30/03, the guest shows up on the Rent Due Report because the CheckOutDate for the
    1st rental, 05/26/03, falls between the report dates even though the guest paid for another 3 weeks
    on 05/23/03.

    I need to find a way the have the guest not show up on the rent due report if he has already
    renewed.

    Any suggestions would be greatly appreciated.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Selection Query (A2KSP-3)

    You may have to xreate a query that determines when each guest is rented to, then use this in you current query. You will have to base youe current query on this new query.

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

    Re: Selection Query (A2KSP-3)

    I have been unsuccessful in creating the queries.

    I tried creating a total query, qryRentalMax, based on tblRental, using the CustomerID field and
    RentBeginDate field. Customer ID total=Group By and RentBegDate total=Max

    I then added qryRentalMax to qryRentDue and joined CustomerID and RentBeginDate of
    qryRentalMax to tblRental. This seems to pick up all the rent due records except if a Past Due
    record has been entered during the report period.

    For example, a guest paid for 1 week from 5/20/03 to 5/27/03. The guest is staying for another
    week but had not paid his rent when it was due on 5/27/03; therefore, a new rental record marked past due was
    entered for 5/27/03 to 6/03/03. This record is not showing up on the Rent Due report.

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

    Re: Selection Query (A2KSP-3)

    Am I missing something, or are you now doing something quite different from your first post in this thread? In that post, you wrote about selecting records on the basis of CheckOutDate. Now, you are determining the most recent RentBegDate in qryRentalMax. If you added anew rental record starting on 5/27/03, that date will be returned by qryRentalMax for that customer, but the CheckOutDate 6/03/03 may well be past the "window" of the Rent Due report, I suppose.

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

    Re: Selection Query (A2KSP-3)

    CheckOutDate is a calculated field which is the RentBegDate plus the NumberofDays. QryRentDue is based on the calculated field CheckOutDate. I tried to use the RentBegDate because I didn't know how to join the tables since CheckOutDate is a calculated field.

    Tom

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

    Re: Selection Query (A2KSP-3)

    If query A contains a calculated field, you can create a new query B based on query A and another table or query, and use the calculated field in a join.

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

    Re: Selection Query (A2KSP-3)

    Hans,

    Thanks for your last suggestions regarding joining queries with calculated fields.

    In an attempt to sold the problem, I have created 4 queries

    qryRentDue
    qryRentDue2
    qryRentDueMax
    qryCheckOutMax

    qryCheckOutMax is a total query which has 2 fields:
    CustomerID and
    CheckOutDate3 (calculated field)

    qryRentDueMax has 2 queries
    qryCheckOutMax and
    qryRentDue

    QryCheckOutMax and qryRentDueMax are joined by Customer ID and CheckOutDate3.

    This query returns the last record of each guest whose CheckOutdate3 fall between the 2 report
    dates with the exception of Past Due records or Invoiced records whose RentBeginDate falls
    between the 2 reports dates.

    qryRentDate2 has 2 tables
    tblRental and
    tblPropertyName

    which are joined by the PropertyID.

    This query returns all records which are Invoiced or Past Due (PayID 7 and 9) whose
    RentBegonDate fall between the 2 report dates.

    For the test report period, qryRentDueMax is returning 5 records and qryRentDue2 is returning 1
    record. I need to have access to all 6 records to run rptRentDue.

    Any suggestions

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

    Re: Selection Query (A2KSP-3)

    You can create a Union query to combine the results of the two queries, and use this Union query as record source for the report. Union queries can only be written in SQL, you can't view them in query design mode. Start creating a new query in design mode, but close the Add Tables dialog immediately, without adding a table. Click the View button on the toolbar (it should read SQL now), or select View | SQL.

    If qryRentDueMax and qryRentDue2 return the same fields (in the same order), you can use

    SELECT * FROM qryRentDueMax
    UNION
    SELECT * FROM qryRentDue2

    If the queries don't return the same fields, or in a different order, you must specify the fields you want the Union query ro return:

    SELECT FieldA, FieldB, FieldC FROM qryRentDueMax
    UNION
    SELECT FieldA, FieldB, FieldC FROM qryRentDue2

    In fact, the field names don't need to be exactly the same, as long as they have the same interpretation. The Union query will use the field names from the first table.

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

    Re: Selection Query (A2KSP-3)

    Hans,
    The UNION query solved the problem. Once again many thanks for your invaluable assistance.

    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
  •