Table and field design for a 3 axis set of data
I am trying to set up an Access database in which I am not sure how to deal with one set of data and would really appreciate suggestions of how to structure the table(s).
The data in question is various expense items to repair/rehab condominiums. there will be other tables to do with tracking management companies and property managers for each set of data and the like. It is the manner of tracking actual estimates of future costs than has be troubled.
For a given property, we might have Decks at $100, Roof at $200 and Asphalt at $300. These are the estimate costs of maintenance and/or repair of these parts of the property (There is, of course, a very long list of such items, maybe 150 in total, some with positive values, some with no value. At first glance, that looks like a simple spreadsheettype layout of two columns and many rows. The first column would list the type of expense, the second column would have the amount.
The first of two complications is that these expenses are projected over a number of years (30 if it matters). So, again using a spreadsheettype visual, and adapting the first example above, it is again easy. Instead of only column 2 having values in it, columns 2 through 31 would have values, each column representing a year, with 30 years overall.
Then comes the third piece. There will be more than one estimate for these costs. Or more than one version of the estimate. And we need to track each version, not just replace the values in our cells as they are obsoleted by newer estimates. (Yes, there is a good reason for this, but not worth going into). And each property will have a different number of versions of the estimates. There is no way to know in advance if it will be just one version or 10 versions.
I don't really want a separate table for each estimate. But can I track all of these factors in one table? Do I really need to have multiple lines for each property that are:
Year 1, Estimate1; t
hen another line for year 1, estimate 2,
another line for year 1, estimate 3,
and so one with all the permutations of 30 years times however many versions of the estimate?
In terms taken form math classes I took decades ago (don't ask how many) this is a problem with 3 axes, X, Y and Z. But I only know how to define data on 2 axes.
