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

    Linking Records (A2K SP3)

    I need to produce a report that will shown the amount of the Security Deposits that were refunded for each guest during a report period. The amount of the Security Deposit refund ([DepositRefuntAmt]) is stored in the same record in which the Security Deposit ([SecurityDeposit]) was initially paid.

    As currently designed the query that is used to produce the report selects those records where the CheckOutDate falls within the report period. If the guest stays for only one rental period, the Security Deposit amount and the Security Deposit refund Amount are in that one record so the Security Deposit Refund amount and the Original Deposit amount show up in the query.

    In most cases, however, the guest has multiple records so both the check out record and the record that has the Security Deposit Refund amount need to be accessed.

    QryDepositRefundPaid is as follows:

    SELECT qryCustomerSorted.PropertyID, tblPropertyName.PCity, tblRental.CustomerID, qryCustomerSorted.CompanyOrDepartment, tblPropertyName!PropertyID & [OrderID] AS ReceiptNumber, tblRental.RoomNumber, tblPropertyName.ReportBeg, tblPropertyName.ReportEnd, tblRental.InitialRentDate, tblRental.DepositRefundAmt, tblRental.CheckOutDate, tblRental.SecurityDeposit
    FROM tblRental INNER JOIN (tblPropertyName INNER JOIN qryCustomerSorted ON tblPropertyName.PropertyID = qryCustomerSorted.PropertyID) ON tblRental.CustomerID = qryCustomerSorted.CustomerID
    WHERE (((tblRental.CheckOutDate) Between [ReportBeg] And [ReportEnd]));

    Any assistance would be greatly appreciated.

    Tom

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

    Re: Linking Records (A2K SP3)

    Apparently, you want to include more records, but you will have to explain how to determine which records.

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

    Re: Linking Records (A2K SP3)

    Data converted to table by HansV - see <!post=Making Tables in the Lounge,162644>Making Tables in the Lounge<!/post> and <!post=Copying a Word or Excel table to a post,164109>Copying a Word or Excel table to a post<!/post>

    1. The query posted returns all records for each guest with a check out date within the report period.
    2. What I need to do is match the most recent record for those guests that have an amount in the Security Deposit field

    For exapmle,

    <table border=1><td>Receipt #</td><td>Last Mame</td><td>First Name</td><td>CheckOut Date</td><td>Security Depost</td><td>Security Deposit Refund</td><td align=right>1234</td><td>Jones</td><td>John</td><td align=right>02-25-05</td><td align=right>$0.00</td><td align=right>$0.00</td><tr><td align=right>1244</td><td>Jones</td><td>John</td><td align=right>

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

    Re: Linking Records (A2K SP3)

    Do we have to match customers by last name and first name, or do they have a unique identifier?

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

    Re: Linking Records (A2K SP3)

    There is an autonumber [CustomerID] field

    Tom

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

    Re: Linking Records (A2K SP3)

    Thank you. Another question: you wrote that "What I need to do is match the most recent record for those guests that have an amount in the Security Deposit field". Which field should be used to determine the "most recent record"?

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

    Re: Linking Records (A2K SP3)

    Here an attempt to describe various combinations of stays.

    Case 1, guest staying more than 1 time with multible record per stay.
    You can have a guest who checks in say on 01/01/05, paying the Security deposit. for one week. (record 1). At the end of one week he decides to stay for another 7 days, 01/08/05 thru 01/14/05.(record 2), checking out on the morning of 01/15/05. Several days later, his Security deposit is refunded in whole or in part based on whether or not there was any damage to the room.

    Subsequently he returns to the property on 01/26/05 for 7 days (record 3). He pays a security deposit on 01/26/05 along with his rent. He renews for 2 more 7 days periods and checks out on 02/16/05 (records 4 and 5). . When he checks out his CheckoutDdate is posted in his last rental record (Record 5), the one from 02/09/05 - 02/15/05. After the room has been cleaned and inspected, a Security Deposit refund form is produced which posts the amount of the Security Depos Refund in the record for the rental period from 01/26/05-02/01/05 (Record 3).

    The record we are trying to find would be one that has has a dollar amount in the SecurityDeposit field and a dollar amount or zero in the DepositRefundAmt field and whose . Record 1 would have dollar amounts in both fields. Record 2 would have dollar amounts in neither field and a CheckDate. Record 3 would have a dollar amount in the Security filed and nothing in the DepositRefundAmt filed. Record 4 would have both fields blank. Record 5 would have a dollars amount is both fields and a CheckOutDate..

    When we run the report for the period 02/01/05-02/20/05, record 5 would be found because it CheckOutDate fall within the report period. We also need the data in record 3 which has the SecurityDepositAmount and DepositRefundAmt. We don't want the informations in Record 1.

    Thus, we are looking for Record 3 not Record 1. Both Records 3 and 1 have dollars amount in the SecurityDepositAmount and DepositRefundAmt fields but the RentBegDate of Record 3 is 01/26/05 which is after the RentBegDate of Record 1 which was 01/01/05

    Case 2 would be the situation where the guests has multible records but only 1 stay. This would be as described with records 3-5 where there was no previous stay (no record 1 and 2)

    Case 3 would be where the guest has only 1 rental record for the stay. In this case SecurityDepositAmount and DepositRefundAmt would both have dollar amounts and CheckOutDate would all be in the same record.

    Tom

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

    Re: Linking Records (A2K SP3)

    Additional information.

    Enclosed is a stripped down database

    1. Receipt 82255 is OK. A 1 record stay
    2. Receipt 82349 is the 1st record of a 2 record stay. Checked Out on 82372
    3. Customer knew he was coming back so his deposit was held.
    4. When he checked back in on 82408 the deposit was transferred. 999 was entered into 82349 in

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

    Re: Linking Records (A2K SP3)

    These two statements are contradictory, assuming that by "Security", you mean SecurityDepositAmount:

    "Record 3 would have a dollar amount in the Security filed and nothing in the DepositRefundAmt filed."

    "Both Records 3 and 1 have dollars amount in the SecurityDepositAmount and DepositRefundAmt fields."

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

    Re: Linking Records (A2K SP3)

    Can you indicate which records you would want to find? It is still very unclear to me. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

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

    Re: Linking Records (A2K SP3)

    I have added a few more records to the database. The records I am looking for are as follows

    In the 5 records that do not have values in the Deposit Refund Amount column
    CheckOut Receipt 82434 I am looking for 82408
    CheckOut Receipt 82372 I am looking for 82349
    CheckOut Receipt 8891 I am looking for 8633
    CheckOut Receipt 81179 I am looking for 81071
    CheckOut Receipt 81702 I am looking for 81472

    If you look at the records using frmCheckInEdit, the field captioned

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

    Re: Linking Records (A2K SP3)

    See if qryC in the attached database does what you want (I didn't bother giving it a meaningful name). If you open it, you'll get an error message because you didn't include qryPropertyNameSorted in the database, just click OK. qryC is based on two other queries, qryA and qryB. The SQL for the queries is:

    qryA:
    SELECT qryCustomerSorted.CustomerID, tblRental.InitialRentDate, tblRental.CheckOutDate
    FROM tblRental INNER JOIN (tblPropertyName INNER JOIN qryCustomerSorted ON tblPropertyName.PropertyID = qryCustomerSorted.PropertyID) ON tblRental.CustomerID = qryCustomerSorted.CustomerID
    WHERE (((tblRental.CheckOutDate) Between [ReportBeg] And [ReportEnd]));

    qryB:
    SELECT qryCustomerSorted.PropertyID, tblPropertyName.PCity, qryCustomerSorted.CustomerID, tblRental.CustomerID, qryCustomerSorted.CompanyOrDepartment, tblPropertyName!PropertyID & [OrderID] AS ReceiptNumber, tblRental.RoomNumber, tblPropertyName.ReportBeg, tblPropertyName.ReportEnd, tblRental.InitialRentDate, tblRental.DepositRefundAmt, tblRental.CheckOutDate, tblRental.SecurityDeposit
    FROM tblRental INNER JOIN (tblPropertyName INNER JOIN qryCustomerSorted ON tblPropertyName.PropertyID = qryCustomerSorted.PropertyID) ON tblRental.CustomerID = qryCustomerSorted.CustomerID;

    qryC:
    SELECT qryB.PropertyID, qryB.PCity, qryB.qryCustomerSorted.CustomerID, qryB.tblRental.CustomerID, qryB.CompanyOrDepartment, qryB.ReceiptNumber, qryB.RoomNumber, qryB.ReportBeg, qryB.ReportEnd, qryB.InitialRentDate, qryB.DepositRefundAmt, qryB.CheckOutDate, qryB.SecurityDeposit
    FROM qryA INNER JOIN qryB ON (qryA.InitialRentDate=qryB.InitialRentDate) AND (qryA.CustomerID=qryB.qryCustomerSorted.CustomerID )
    WHERE (((qryB.DepositRefundAmt) Is Not Null)) OR (((qryB.CheckOutDate) Is Not Null));

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

    Re: Linking Records (A2K SP3)

    Hans,

    Many thanks for pointing me in the right direction. We are getting close. I have revised the selection criteria in qryC and rptDepositRefundPaid. All of the required records are now being selected.

    The problem now is when the CheckOutDate and the DepositRefundAmt are in different records both records shown up on the report. What I would like is for the CheckOutDate of 01/06/2005 in 82434 should be with 82408 and the 82434 not listed.

    Similarly, 12/05/2004 in 82372 should be with 82349; 07/03/2003 in 8891 should be with 8633; 10/12/2003 in 81179 should be with 81071; and 05/02/04 in 81702 should be with 81472.

    Any suggestions?

    Tom

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

    Re: Linking Records (A2K SP3)

    It's not clear to me why you changed the criteria.

    Does qryE in the attached database do what you want this time?

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

    Re: Linking Records (A2K SP3)

    qry E is getting real close. I am in the process of testing qry E on a larger amount of data. There is a problem, but I haven't had time to isolate on what causing the problem. It looks like when someone moves to a new room during the same rental period (same InitialRentDate) that both rooms are being listed.

    I will be travelling most of this week and the early part of next week . Also have jury duty for most of March.

    When I determine exactly what the problem is, I will re-post.

    Thanks for all your help.

    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
  •