Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old March 1st, 2012, 08:22 PM
Authorized User
Join Date: May 2009
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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.
Old August 23rd, 2013, 11:13 AM
Registered User
Join Date: Nov 2012
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default estimates over years

i think i have something similar which is really just a list of appointments

your table should be something like :
estimate number - autonumber
then need a key which is the condo or whatever.
then the year [chop out of the date]
then the date
then the item key
then the current cost

perhaps i've missed something, but this seems easy.
if you want to check the increase etc , just run sum queries for the total
per condo/date and compare them.

Similar Threads
Thread Thread Starter Forum Replies Last Post
copy set of data to another table with order by vk18 SQL Server 2000 2 August 23rd, 2013 11:21 AM
Table Design Limiting allowed values in a field John2112 SQL Server 2000 3 February 2nd, 2009 04:33 AM
How to set indexes on columns in a data table hdewees VB Databases Basics 1 June 5th, 2006 02:42 PM
Set font for different word inside a table field android66 Java GUI 0 March 7th, 2006 05:52 AM
Crystal - Set Y axis values dynamically vasanti Classic ASP Basics 0 February 14th, 2005 06:25 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.