Our company sublets rooms to clients. Sometimes clients move to different rooms in the same property. Other times clients relocate to another property. We have a Tenant Master Roster with info like name, current room, and current rental rate. We also have a Property Master Roster with the costs of the various rooms at the property. We also have a Rent Master Roster that shows rent on a per client, per month basis. What we now need, I’ll explain with an example:
In the Tenant Roster, we have Jane who started with us in January and we’ve assigned her to Property #1001 in a $600/mo room. Then, in May we reassign her to Property #3624 in a $700/mo room. Our Rent Roster needs to show she was at #1001 @ $600 from January until April, then from May on the Rent Roster reflects the new assignment location with the new rent without destroying data from the first assignment.
I hope I’m making that clear. Thanks for your help!
—–
Joey Kango
So you want to record every combination of tenant, property, price and date range. There are lots of ways to do it. I'll suggest one.
You are making a database of all of that, so each change needs to be a different record (row) of the database. So the columns would be: tenant, property, price, start date, end date. Every time there is a new situation, there is a new row added to the database (table). That way you have a complete record of every situation.
You could also do it where one row is: tenant, property, price, month. So then you'll have to copy and paste the rows from the current month to the next month, and then apply changes. But if a situation changes in the middle of the month, then you could have issues.
Your current situations would be all of the rows that do not have an end date. So you could filter by that to see what is happening right now, and then turn off that filter to make changes or see historical data.
This is definitely the sort of thing that spreadsheets can handle, but they are not ideal for. This is more of a custom database problem. So I wonder if there is already software, or perhaps an online service, that handles exactly this.