Rent Tracking

[Rod Lloyd]

I would like to create a database to track tenant rent payment.

I have no problem with the basic layout but how do I track payment history [so each month it keeps a history of payments.

If anyone knows of a template, that would work as well

:

[David McQueen]

I would like to create a database to track tenant rent payment.

I have no problem with the basic layout but how do I track payment history [so each month it keeps a history of payments.

:

Hi Lloyd,

You do this with a combination of architecture and reporting.

1. Architecture here is basic.

A table of tenants

A table of buildings if there are more than one

A table of payments which is a join table having both a tennant ID and a building ID.

2. Reporting.

Once you have this structure you should be able to rpeort on tenant payments per month, a tenant payments over time, rent payments per building per report period and all of the summary reporting that could go with these detail reports.

HTH

Dave McQueen

[Rod Lloyd]

Thanks for the reply, I am going to need a little more spacific's or pointing in the right direction

[David McQueen]

Thanks for the reply, I am going to need a little more spacific's or pointing in the right direction

Lets keep it simple and assume that there is only one building.

Tenant Table:

Tenant Name

Tenant particulars (rent/mo, lease expirey .....)

Apartment Identification ( Could get more complicated and have an apartment table also)

TenantPrimeID (Autoenter Serial number of field form Text)

What you want here is all of the information you need on this particular tenant so that if you search out their record, that info is available to you.

TenantPrimeID is important as it forms the basis of your relationship to the rent's table.

Rents collected Table:

TenantID (Text Field) - This field relates directly to TenantPrimeID in the tenant table

Date of Payment

Rent - Lookup the RentPerMonth field from the tenant table.

Any other tenant information you want to show from the tenant table can be looked up.

Creating a rental record.

Using a script - In the Tenant table select a tenant, grab his TenantPrimeID, create a new record in the rent table and plunk that value into the TenantID field. This will cause all other information with exception of the date to be brought into the record. Enter a date of payment (you can start with an Auto Enter for that field of the current date and then edit it if you wish).

Over time you will build up a bunch of rental records - one record per tenant per month typically. You would then want to report on this.

In the tenant table create subsummary fields for the total rent.

Create report layouts, depending on what you want to show with the total rent field in the various subsummary parts.

Do the search to find the applicable records, do the sort for that particular layout, go into Preview mode an view the report.

You should be able to report on each tenant over time, each apartment over time ( a tenant could change apartments), all apartments in a particular month etc. - again depending on the information you capture.

If you want to have multiple buildings - you could structure several ways - but one record for each apartment would work. Then when creating a new tenant, you would assign them an apartment and all the rental info could be sucked into his record using a lookup. From there you could proceed as above.

There are lots of more complicated ways to do this, but this will give each record of payment as an entitiy that does not change if background items in other tables are changed.

Hopefully this will help you out

Dave McQueen

[Fenton]

A further refinement (which I did for someone). Your most stable data entity is the property(s) and apartments, the Units. You could put the current tenant's ID into the Unit (simplest).

Since you expect to at least try and get the rent for each unit each month, what I did was import the Unit IDs (along with the current tenant) into a Rental_Payments table each month. That way they're all ready to go, with the expected rent looked up from a Rental_Rates table.

The advantage of pre-creating the monthly payment records is that it allows you to see not only what the expected rent is, but also see the gaps, who has not paid. It also serves as history of both.

Another simple table, with a record for each Year_Month, lets you view and enter data for the above. It also serves as a central point, so that another "monthy expenses" can be entered for the units.

[David McQueen]

A further refinement (which I did for someone). Your most stable data entity is the property(s) and apartments, the Units. You could put the current tenant's ID into the Unit (simplest).

Since you expect to at least try and get the rent for each unit each month, what I did was import the Unit IDs (along with the current tenant) into a Rental_Payments table each month. That way they're all ready to go, with the expected rent looked up from a Rental_Rates table.

The advantage of pre-creating the monthly payment records is that it allows you to see not only what the expected rent is, but also see the gaps, who has not paid. It also serves as history of both.

Another simple table, with a record for each Year_Month, lets you view and enter data for the above. It also serves as a central point, so that another "monthy expenses" can be entered for the units.

I like both the entity focus change and the pre loading data concept. I have done a lot of real estate centered things, but most were centred around sales or construction as opposed to property management. In these cases the total property and the purchaser become most important. Here I can see where building reporting would be prime.