Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Table and field design for a 3 axis set of data

Table and field design for a 3 axis set of data

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 spreadsheet-type 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 spreadsheet-type 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.

RE: Table and field design for a 3 axis set of data

The r937 article is a good primer on relational database design.  But I am really looking for very specific advice, not the general discussion in that article.

I am still thinking that I need to just bit the bullet and add the extra field that indicates which version of the estimate each set of numbers is.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close