Adding data to table that retreives data from another table

[BZeyger]

Hello,
I have a problem which I am trying to solve. I have an access database in
which I have a number of projects. Each project has various financial
information associated with it.

One of the projects has a table (Table1) that contains Actual Financial
information.
The same project also has a table (Table2) that contains Estimated Financial
information.

The various amounts are monthly.

An Example would be:

Table1: ID, Actual_Jan_09, Actual_Feb_09, Actual_March_09, etc.
Table2: ID, Estimate_Jan_09, Estimate _Feb_09, Estimate _March_09, etc.


I have these items on a form. The header of the form contains the project
name while the detail section contains 2 subforms. One subform showing the
Actual amounts and another subform showing the estimated amounts. There is
also a command button and a date fields in the detail section of the form.

This is where it gets tricky.
The Estimated amounts can have multiple records per project. The initial
estimated costs would display the different estimated costs. However, if the
user selects a certain date, then it would either display an estimated amount
or an actual amount. This sounds confusing. Here is an example

Jan09 Feb09 Mar09 April09
Actual amounts 1: $15 $20 $25 $30

Initial Estimated 1: $10 $11 $15 $20

If the user selects a month of Feb 09, the new estimated amounts would be:

Jan09 Feb09 Mar09 April09
$15 $11 $15 $20 $15 came from the actual table

If the user selects a month of March09, the new estimated amounts would be:

Jan09 Feb09 Mar09 April09
$15 $20 $15 $20 $15 and $20 came from the actual table


It should take the actual cost of the fields prior to the date selected. And
continue to use the estimated amount s to the dates after.

How would I go about doing this?

 

[Jeff Boyce]

I'm inferring your table structure from your description.

You don't have a relational Access database structure, you have a ...
spreadsheet!

Access expects and is optimized to work with well-normalized data. Both you
and Access have to work overtime to come up with work-arounds when you feed
it 'sheet data.

Before you work on "how", nail down the "what". Otherwise, why not just put
this stuff in a spreadsheet and use Excel?!

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

 

[GB]

Though I can not disagree that the what should be figured out first, I would
like to give the benefit of the doubt.

My guess is that you have many projects that you are addressing (therefore,
a database would be helpful to search/locate/average various numbers);
however, really should try to describe the desired result a little better.
In both examples that were provided, there was not enough unique information
to figure out what is really going on. In the first example, the number $15
shows up twice, in the second example there are two pairs of 15 and 20,
therefore not able to figure out what information is feeding what "function".


When you say take this value or that value, where is it going? Are you
looking to create a query? Do you want it in a table? Are your "dates"
actual date format and just presented in Month/year format?

Basically, in whatever application you decide to use, you can do exactly
what you say you want done. For all dates before the chosen date, use the
actual value, on and after the selected date use the estimated value. If you
are looking to use the data in a query, then you may have to either use two
or three queries that get joined together, or double up results in the field
that returns the "dollar amount" by using some form a of a test on the date
(again using the criteria above).

If this information is not being recalculated to refine estimates then I
would guess that Excel may be the better option. Additionally, is the month
of as much importance for the project as is how far along in the project the
work is? I.e., the first month of the project costs this much, the second
month costs that much?