First, some quick background. I’m my company’s jack-of-all trades computer person. I mostly do light programming in Visual Foxpro 9, in which I am self taught. I’m also familiar with Access, VBA (though mostly in Excel, I have yet to use VBA in Access), and Crystal Reports. All of which I’m also self taught in.
I’m familiar with the concepts involved in relational databases, but have never mastered implementing them properly, so my databases tend to be hybrid flatfiles/relational. That’s not a huge concern for me right now, as I tend to get the job done, I just throw that out there to give you an idea where I’m coming from.
So here is my dilemma. I need to create a new Access database to track MLS (Multiple Listing Service, it’s where real estate agents list properties they want to sell) statistics. In the MLS there are essentially two types of properties I’ll be tracking:
Sold properties - Simple as that, these properties have been sold and that won’t change.
Active properties - These properties have not yet been sold and can change status at any time to Pending (there is an offer on the house, but it’s not final) to Expired or Canceled (either way they are off the MLS and I no loner care about them).
Each month I’ll go into the MLS and run a report on all listing activity for that month. That is: Number of sold properties, number of pending properties, and the total number of active properties. Where I am having trouble is in how to deal with the active properties. Let me explain. As I mentioned once a property is sold that’s that. I store it in my table and never have to worry about it reappearing (if it gets resold later it counts as a different transaction so no worries there). But with actives, as house can be on the market, for all intents and purposes, indefinitely. So each month a property is active, I need to report it as such. Yet I also need to be able to report on historical active properties. My initial thought was, well who cares if I have duplicates, each time a property is active I’ll add it to my table and note which month it was added. The problem, however, is that each month, in one MLS alone (and I could be doing this for 20 or more MLS’s in the future) there were 15,000 or so active listings, of which about 80% were active the month before. So as you can see, in no time this database could get very unwieldy. However, I only need to retain sold records after reporting on a given month.
If you’ve read this far, I’m already grateful. If you have any thoughts on how to setup a database to allow for “easy” reporting of historical sold properties as well as month by month active properties without having an insanely large database I’ll be eternally grateful! Or if you think this might be a bit much (though if I may brag I’m quick to learn) then I’d be interested to know that too.