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

    Select Query Problem (A2K SP-3)

    I have attached a stripped down database.

    I have a rptActiveCustomersWithARoom that looks at active customers (CustomerID=1) who have a room. The report produces the correct results except when the same customer rents more than one room with difference RentBeginDates.

    For example, rooms 203 and 205 are both occupied by Wallace Carney. The 2 latest records (OrderID
    Attached Files Attached Files

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

    Re: Select Query Problem (A2K SP-3)

    Well, YOU will have to indicate what you want instead. You might select records for which RentBegDate+NumberofDays is on or after today, i.e. for which the rent hasn't finished yet. If you want to exclude rents that haven't started yet, add the condition that RentBegDate is on or before today. The SQL for this query (qryActiveCustomerWithARoom) would be

    SELECT tblRental.OrderID, qryCustomerSorted.CustomerID, qryCustomerSorted.PropertyID, qryCustomerSorted.LastName, qryCustomerSorted.FirstName, qryCustomerSorted.MiddleInitial, tblRental.RoomNumber, tblPropertyName.PCity
    FROM tblPropertyName INNER JOIN (qryCustomerSorted INNER JOIN tblRental ON qryCustomerSorted.CustomerID = tblRental.CustomerID) ON tblPropertyName.PropertyID = tblRental.PropertyID
    WHERE (((qryCustomerSorted.CustomerStatusID)=1) AND ((tblRental.RentBegDate)<=Date()) AND (([RentBegDate]+[NumberofDays])>=Date()))
    ORDER BY qryCustomerSorted.LastName, qryCustomerSorted.FirstName, qryCustomerSorted.MiddleInitial;

    If you want something else, please specify what.

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

    Re: Select Query Problem (A2K SP-3)

    Many thanks for your quick response.

    I have been discussing the issue with the operations people to see exactly what the want. The solution objective is to show the room number of everyone who is currently occupying a room as of today.

    Paid To Date = [RentBeginDate] + [Number of Days]
    Rent End Date = [RentBeginDate] + [Number of Days]

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

    Re: Select Query Problem (A2K SP-3)

    What is the question? Or rather, what is the problem?

    If the operations people want to see all customers with a room, including those that are leaving today, the query I posted above should work. Customers who have rented two rooms will show up twice, once with each room number. That seems useful information to me, but if you want to list them only once, make the query into a Totals query and count the number of rooms instead of listing them individually.

    If you want to distinguish rooms being vacated today, you can add a calculated column
    LeaveToday: ([RentBegDate]+[NumberofDays]) = Date()

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

    Re: Select Query Problem (A2K SP-3)

    Many thanks for your assistance. I think your first solution is the best; however, the operations people have not decided exactly what they want.. At least with your assistance I was able to give them several possibilities. In addition, with my focus on the report I made several design chages to the report which should made it more useful by adding the receipt number instead of the customer number, adding the Rent Begin Date and the Paid To Date.

    As usual, many thanks. We loungers can never thank you enough for your williness to share your expertise.

    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
  •