IPAddr

Reconciling Receipts from Unknown Tenants in Rent Master

If you receive money into your trust account with no reference so that you don't know who it is from then you need to post this to a landlord created with a name like UNKNOWN DEPOSITS or SUSPENSE ACCOUNT or some other name so that you know what the account is for. Then you post the receipt to this account as a SUNDRY RECEIPT using the LANDLORD ONLY RECEIPT option in receipt posting.

Then, when you find out who the funds came from you can reverse out the original sundry receipt and then post the receipt (using the same receipt number) to the correct tenant account. This way there are three receipts with the same number - two of which cancel each other out - and you have a complete audit trail of what happened.

The [Tenant Receipts] file in Rent Master actually keeps track of ALL receipts ever entered into the system (not just tenant receipts) - (well since the time that we added this file to the system anyway). So if you post a sundry receipt to landlord UNKNOW and then later you reverse this sundry receipt and post it back against the correct tenant then you'll have a +ve receipt and a -ve receipt with the same receipt number and the same amount so when you add them together they will add up to zero.

If you post a sundry receipt to UNKNOW and then transfer this out of the account using an expense instead of a negative receipt then the two transactions again add up to zero but the receipts file only stores the receipt and so there is no record (in the receipts file) of the other side of the transaction which is why the total of +ve and -ve receipts don't add up to the balance of the account.


If you wanted to see the information stored in the [Tenants Receipts] file then you can ...

  1. Click on the DATABASE menu
  2. Then SQL EXECUTE STATEMENTS
  3. Then enter UNKNOIWN RECEIPTS into the combo box on this form
  4. Click on the EDIT button
  5. Copy and paste the SQL statement below into the box
  6. Click on the SAVE button
  7. Click on the DO IT button
  8. When the results have been displayed click on the EXPORT button
  9. Change the name EXPORT.CSV to some other meaningful name (if you want to - you don't have to)
  10. When the data has been exported you can click on the EXCEL button to open it in Excel.
The SQL statement is shown below (you will need to change UNKNOW to SUSPEN or whatever the correct Landlord code is) ...

  Select Max([Audit Number]) As Auditno, [Transaction Number] As Recno, 
    Max([Transaction Date]) As [Date], Max([Landlord Code]) As [L/Lord], 
    Max([Disbursement Code]) As Disb, Sum([Amount]) As Amt
  From [Tenant Receipts]
  Where [Landlord Code] = 'UNKNOW'
  Group By [Transaction Number]
  Having Sum(Amount)<>0


If running the query above gives you the answers that you need then there's no need to do any more but if not then you can follow these notes to get more information.

The same information is stored in the [YTD TRANASACTIONS] file but this is cleared at the end of each financial year. (This is a historical hangover from when computers were much slower and had much smaller hard disks).

So, the way to determine this information (assuming that it's May-2014) would be to ...

  1. Open your current database

    1. Bring up landlord UNKNOW
    2. Click on the YTD TRANS button to display all of the transactions for this account for the current financial year.
    3. (If the YTD TRANS button is greyed out then there were no transactions for this landlord for the current financial year)
    4. If there are transactions then click on the EXPORT button and save the data as unkn1405.csv (instead of export.csv)
    5. When asked if you want to open the exported file click on the EXCEL button
    6. Save the Excel file as an XLS file type with the name UNKNOWN _1406.xls

  2. Now Open the backup file done at 30/06/2013 - the one done just BEFORE you finally close off the month and year (it should be called RENT1306.MDB)

    1. Bring up landlord UNKNOW
    2. Click on the YTD TRANS button to display all of the transactions for this account for that financial year.
    3. (If the YTD TRANS button is greyed out then there were no transactions for that landlord for that financial year)
    4. Click on the EXPORT button and save the data as unkn1306.csv (instead of export.csv)
    5. When asked if you want to open the exported file click on the EXCEL button
    6. Save the Excel file as an XLS file type with the name UNKNOWN _1306.xls

  3. Now Open the backup file done at 30/06/2012 - the one done just BEFORE you finally close off the month and year (it should be called RENT1206.MDB)

    1. Bring up landlord UNKNOW
    2. Click on the YTD TRANS button to display all of the transactions for this account for that financial year.
    3. Click on the EXPORT button and save the data as unkn1206.csv (instead of export.csv)
    4. When asked if you want to open the exported file click on the EXCEL button
    5. Save the Excel file as an XLS file type with the name UNKNOWN _1206.xls

  4. Now Open the backup file done at 30/06/2011 - the one done just BEFORE you finally close off the month and year (it should be called RENT1106.MDB)

    1. Bring up landlord UNKNOW
    2. Click on the YTD TRANS button to display all of the transactions for this account for that financial year.
    3. Click on the EXPORT button and save the data as unkn1106.csv (instead of export.csv)
    4. When asked if you want to open the exported file click on the EXCEL button
    5. Save the Excel file as an XLS file type with the name UNKNOWN _1106.xls

  5. Continue this on until you have exported from all of the year end backups that you have

Once you have all of these excel files then you have ALL of the transactions for this landlord (similar to the results from exporting the [Tenant Receipts] file). Now you need to

  1. Merge them all into one XLS file and then sort them by the transaction number (receipt number)
  2. Then save the new XLS file as UNKNOWN.XLS
  3. Then Save it again using SAVE AS and save it as UNKNOWN2.XLS
Now you have two copies of the file (so if you mess up the second one you can copy the original again).

If you total all of the amounts in this file they SHOULD add up to the current balance of that account. If they do then excellent but if they don't then there are transactions missing - perhaps from a missing backup file.

Because you've sorted them by receipt number you should have a +v e and a -ve transaction right next to each other for the same amount for most of the transactions. You can delete these lines from the XLS file and you SHOULD then end up with an XLS file which has NO negative amounts at all and the positive amounts should add up to the total balance.