Calculations Between Tables

[Guest]

Table #1 contains records of publishing events. Each record will collect the
hours needed per month to accomplish each event. Table #2 contains several
variations of monthly hours for different scenarios.

I need a publishing date (ex. 6/15/07) from a field in table #1 to cause
data from several fields (1 to 12 fields with several variations on different
rows) in table #2 to be placed in fields that represent the same months
(Jan-Dec) on the same row of table #1. A selection will be made in a field on
the same row of table #1 to determine which row of data in table #2 to use.

Example: a publishing date of 6/15/07, scenario 5 is selected with values of
10, 10, 20, and 40 in the 9th thru 12th fields respectively. The 12th field
is always associated with the same month as the date; the 11th field is the
previous month and so on. The date corresponds to a June field for the same
record. The value in the 12th field (40) of scenario 5 in the other table
goes in the June field. The value of 20 goes in May, 10 in April, 10 in March.

It's a bit complicated for me so I'd appreciate some help. Any takers?

 

[Jeff Boyce]

Based on your description, your tables are designed to look like
spreadsheets (one column per month). Since Access is a relational database,
this is NOT desirable. If you want to make the best use of Access' features
and functions, you'll need to reconsider your data structure, with
"normalization" in mind.

While is it possible to find work arounds that make Access perform as you've
described, you will find you have much easier use of your data if you do the
normalization first. After all, it IS possible to drive nails with a chain
saw, ... ?!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Yes thanks Jeff! I do not know how structure a database that way yet so I set
up my example like a spreadsheet to explain the functionality. It sounds like
you've pointed me in the right direction. Can you or anyone else provide a
little more guidance?

 

[John W. Vinson]

Yes thanks Jeff! I do not know how structure a database that way yet so I set
up my example like a spreadsheet to explain the functionality. It sounds like
you've pointed me in the right direction. Can you or anyone else provide a
little more guidance?

Take a look at these, particularly the Database Design 101 links on
Jeff's site:

Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

John W. Vinson [MVP]