Rental Property Assistance
I am a newbie to FMP11A and am trying to set up a simple database for a rental property for university students. I have made a start, but there are some calculations which I am finding difficult to locate or find a way around.
A brief overview of what I'm working with:
To make things simpler, as rental rates may change from year to year, I thought it'd be best to have a separate file per year.
There are a total of 7 units in 1 apartment block: x6 2-bedroom units, and x1 1-bedroom unit.
Number of tenants occupying units would ideally be the number of bedrooms involved, however, this year for instance we have currently 1 person in a 2bed and also 3 persons in a 2bed. Therefore I am open to the possibility of having more tenants than bedrooms. (no current problem here)
Ideally the lease period is 9 months (Sept to May). Again however I have tenants who don't obey this structure and am therefore open to the possibility of having various lease beginning and ending dates.
We usually require monthly payment due at the beginning of the month (but it rarely works out this way as some tenants are slow on payment). The first payment would consist of the first and last months rent (e.g. rent for Sept and May). Also, a security deposit is required at the beginning which is returned at the end of the lease on inspection of the unit.
Also, information and stats about the utilities paid per month will come in handy. The rental rate is inclusive of everything, but tenants are encouraged to not abuse these (utilities). It would therefore make sense to monitor the utilities.
OK... now about what I have thus far in the database...
A portion which just requires fields to be typed in and recorded (stored) is the utilities section, where monthly bills are then punched in as they come to keep track.
I have 3 major tables: 1. Tenants (list of all the tenants and their info). 2. Monthly Rental Rate for the current year (per apartment per number of tenants occupying) - This is the rate for an individual tenant. 3. Payments (made by tenant and the date made)
I also have one minor table which includes the Number of tenants occupying each apartment in the year, and currently. This is used for calculation purposes.
In the Tenants table... Fields include: Name, Apartment # (to which tenant belongs to), Security Deposit (Yes.No Radio button), Start of Lease (date), End of Lease (date), Monthly Rental Rate*, Total Amount Paid up to current, Total Amount Due** up to current, Final Month's Rental Rate***.
Other fields used for calculations : Expired Lease (which results 1 if end of lease date is < current date and 0 if not), Number of Current Tenants (using a relationship of the same table (is this called "self-join" or something), Counts the Apartment # field and subtracts from it the expired lease field resulting in the number of current tenants)
*Monthly Rental Rate: Related to Monthly Rental Rates table. A case of if for example current number of tenants = 2 then the rate for 2 tenants in that apartment is produced (as an individual rate for that tenant). This is a current rate and is subject to change if there are less or more tenants in the said apartment.
Total Amount Paid relates to the Payments table which is pretty straight forward I think (sum of Amount Paid in payment record).
**Total Amount Due is calculated by using a formula found in this forum (i think) which calculates the elapsed months (from the 1st of the month) from the start of lease up to current (+1 to include the current month) times the monthly rate~ + monthly rate~~ (for the last months rent) + security deposit (caution fee below), and subtracting from all that the Total Amount Paid.
Here is the formula... (Bold is the formula found on a forum)
(((Year(Get (CurrentDate)) - Year(Start of Lease) ) * 12 + ( Month(Get(CurrentDate )) - Month(Start of Lease ) ) - If ( Day (Get(CurrentDate)) < Day (Start of Lease); 1; 0 ) + 1)* (Monthly Rent) + (Monthly Rent) + (Monthly Rent 10 11::Caution Fee)) - (Total Amount Paid)
~Monthly Rate is dynamic and would cause a problem when number of tenants change...I need to avoid that and somehow store the monthly rate in the condition that the tenants are of a certain number during a certain period of time.
~~Monthly Rate (should be final month's rental rate), as this may differ from the current monthly rate.
***Final Months Rate (which is where I am stuck now as I am kind of working backwards from what I have typed). This should be the rental rate for the final month of the lease (which may differ to current if other tenants are added or end their lease earlier)
...So I basically need a formula which will calculate the rental rate for the last month of the lease. This I can replace monthly rate~~ (for the last months rent) with in the above formula for payment due. I would also need a way to save the monthly rates as they may also change in the next month (depending on the number of tenants occupying the said unit), This would be incorporated in the above calculation I guess in order to get the exact payment due.
Other info concerning utilities can be worked on at a later date. I cannot recall at this point if there is anything else I require for the time being.
My apologies for this probably overly large post, but I would just like to be concise enough to make it easier for your responses to cover my requirements. And I am also open to any suggestions which may better my custom database.
Thank You in advance!