Updating fields in one table based on another table

[Tabby]

PLEASE! Someone HELP!
I have 2 tables. One is the master MEMBERS table that
should display a running total of contributions by year,
(i.e. column 1: 1999; column 2: 2000...these columns are
updated from the CONTRIBUTIONS table)

Then, I have a table called CONTRIBUTIONS, where
contributions are entered with column fields such as:
ID#, DonationAmount, DepositDate, Check#, Appeal,
CalendarYear. This table is simply a database containing
all contributions that come in.

The tables are joined by their ID# as each members has a
unique ID.

The problem I am encountering is that the Columns in the
MEMBERS table, such as YR02 (which is supposed to sum all
contributions for that individual who made a contribution
in the year 2002.) This field updates for some
individuals and not for others.

Does anyone know how I can trouble shoot this problem? I
can't seem to refresh the data so that all columns are
updated. I have only portions of individuals whose
information is being update while the others are not?

 

[Van T. Dinh]

There are a few inherent problems with your current set up:

1. It sounds like every year, you add another Field in the MEMBERS Table
for contribution of the year. This is how Excel spreadsheet work but NOT
Access. In a properly structured database, you add data by adding Records,
NOT Fields since adding Fields requiring the re-design of all other Access
Objects (Queries, Forms, Reports, etc ...). Using an Access database like
an Excel spreadsheet certainly reduces the efficiency of Access AND leads
all sort of problems in the application later.

2. In a database, you store data in the values of the Field, NOT Field
Names since the JET Database Engine (Access MDB uses JET as the default
database engine) cannot process info. in Field Names. Field Names like
YR01, YR02, YR03 ... indicate that you store data in the names. In
addition, these indicate that you have repeating groups in your Table which
are not desirable in the Relational Database Design Theory.

3. Not only that, these are *Calculated* values from data in the Table
CONTRIBUTIONS and Calculated values should NOT be stored in Tables as they
simply reduce the efficiency of Access, waste storage space and lead to
inconsistency problems later.

4. The update you mentioned is probably done by your code so you will have
to find out which part of your code doing the update and post the details.

However, I feel that the most efficient and useful way to solve the problem
is that you do your own research on the Relational Database Design Theory
and the Database Normalisation techniques and apply these to your Table
Structure before you proceed further.