Help with a Property Management database

[B. Elam]

Hello -


The company I work for has about 50 leased offices across the country that we currently manage via an Excel spreadsheet. I am trying to migrate this to Access to add a bit more functionality.

I have all the basic property information working the way I want it, but am having difficulty figuring out what to do with the rent payments. As our leases are generally for 5 years, I would like to have a table showing the monthly rent every month (ie projected out all 60 months)for each property, so I could do a budget query or report for a given period (individual month or calendar year).

Any advice in setting up the rent tables is appreciated. In Excel, I would make month/year the column header then have 60 columns of scheduled rent payments. I don't know how to get the functionality I desire in Access, I want to be able to query by date ranges to return the numeric value for rent. Make sense?

Thanks.

[orange]

There is a free generic data model here that may be of some help to get you started.

I recommend you start by working through one (or more) of these tutorials.
You will learn how to identify and structure tables and relationships to support your business.

http://www.rogersaccesslibrary.com/T...lationship.zip
http://www.rogersaccesslibrary.com/T...nformation.zip
http://www.rogersaccesslibrary.com/T...getsDesign.zip

You really need to get a list of the business facts of your business.

Good luck.

[ranman256]

Good that youre switching to a database.
I have done this. (by the way, in access you dont make 60 columns of payments. you make 60 records in 1 column)

I have tables
tProperty
tClients
tPayments (what client paid)
tCliChgHist (client charge history, is a record of Rent for a month, and a payment for that month.)
tContracts (rent contract)

at the start of every month, all clients are charged rent, so a record is placed in tCliChgHist, which they must pay off, either by their existing balance, or a payment.

[B. Elam]

Thanks for the pointers. I did this once before, 20+ years ago, and much like the Spanish I took in school, those brain cells have since been re-purposed. To paraphrase from Star Trek, I'm a property manager, not a database developer.

[orange]

So, you have an appreciation of the effort to design, develop,adjust, maintain.....
Great, many posters feel --if i buy Access I can have a fully operational, perfect database..


Do you have all your requirements defined? Any need for multi user, online access, access via phone...? Now is the time to get your requirements and priorities captured/defined/vetted.

Why not look to a commercial package, or a commercial service?
I suspect it would be your best option.

[B. Elam]

Orange- I know I won't have a fully operational perfect database, just trying to improve upon what we are using now. And yes, there are plenty of good commercial packages out there for lease administration, but the company I work for is very thrifty.

I wish I could find the templates for the last one I did, it was on Access 95 or earlier but it might be helpful.

Ranman- do you have a separate tCliChgHist for each client? What I am thinking is that I will have a separate rent schedule set up for each property.