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

    Query Selection (A2000 SP 3)

    I have a query, qryActiveCustomersWith A Room, that uses 3 tables:
    tblCustomers
    tblPropertyName
    tblRental

    The report that uses the query has the following fields:
    CustomerID from tblCustomers
    GuestName from tblCustomers
    RoomNumber from tblRental

    TblRental containsthe individual rental records
    TblCustomers contains the Customer information

    The reports work find except if a customer has moved from one room to another, both room
    numbers are listed.

    The selection criteria for the query is based on the CustomerStatusID (from tblCustomers) being
    Active. Total: is GroupBy

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

    Re: Query Selection (A2000 SP 3)

    I assume that tblRental contains CustomerID, RoomNumber and also a date field or date fields. If that is correct, you could retrieve the data you want in a few steps:

    1. Create a query based on tblRental. Add CustomerID and the date field to the query grid, but not the RoomNumber (if you have a FromDate and a ThroughDate or something like that, take the ThroughDate.) Make it into a Totals query, and set the Totals option for CustomerID to Group By and that for the date field to Max. This query will retrieve the most recent date for each customer. Save this query.

    2. Open the query you already had in design view. Add the query you just created, and join it to tblRental on CustomerID and the date field you used. This will limit the records returned to those for the most recent date per customer; you should get only one room per customer now.

    If my assumptions are off the mark, please post back and give some more details.

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

    Re: Query Selection (A2000 SP 3)

    Many thanks. Your solution worked 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
  •