Common Access Problem

[J Cermak]

A common problem I battle with in MS Access 97 is this:
Say you have 3 tables: Table 1, Table 2 and Table 3. Each
have 2 fields: ID and Number. Now say you would like to
add all records with matching IDs and come up with a sum
for each ID. You can easily set up a query and create a
relationship between all 3 table's ID fields and sum the
number fields from each. But here lies the problem: If
table 1 has ID 1 and Table 2 has ID 1 but table 3 does
not, say table 3 is empty, then thequery will not work.
If you try adding them by displaying each table in a
seperate form and adding them on a 4th form, you get an
#error in the sum field.

There must be a solution that will add only the fields of
the tables that exist. The only way I can get it to work
is to make sure the ID exists in all tables and is 0 for
ones with no value, then using Nz for each field, but
this makes the database large and wastes space.

Does anyone know code or commands that can work around
this?

Thanks!

 

[Heiko Liegmann]

I Think Your Database is not normalized. Do all Your Tables have 1:1
relationships?
You should put all data in one table with a new "Type"-Column (1 for Data
from Table 1, 2 for Data from Table 2 and so on).
Then You can sum up the Numbers and group by ID.

Heiko

 

[Van T. Dinh]

1. (work-arounds)
* Consider 1 Table as the "Main" Table and then LEFT Join
it to other Tables in the Query. Use Nz() function in the
Query to convert Null value (when there is no
corresponding Record in the "other" Table to zero so that
you can sum.

* Consider 1 Table as the "Main" Table. Simply have this
Table in the Query and use Nz() and DLookUp() function in
the Query to retrieve corresponding values from other
Tables. Since DLookup is not that efficient, this Query
will probably be slow.

Both above work-arounds won't give you the sum (assuming
Table1 is the "Main" Table) for the ID that doesn't exist
in Table1 regardless whether the ID exists in Table2
and/or Table3.

* You can try to simulate the full-outer join but it gets
complicated with 3 Tables.

2. (permanent solution)
Most likely, your Table Structure is not correctly
designed and implemented. In real life, 1-to-1
relationships rarely exist (well... personal relationships
excepted). We occasionally use 1-to-1 relationships in
database model to save storage space (which is not
entirely correct) but mostly we use 1-to-M(any) and M-to-M
relationships since they represent more true-to-life
relationships. Put it this way: I have done a fair few
databases and the number of 1-to-1 relationships I used
can be counted with the fingers (in one hand)!

Perhaps, it will be a useful exercise for you to learn the
Relational Database Design Principles and Database
Normalisation and then investigate whether your database
follows the RDDPs and the Normalisation with a view to
modify your Table Structure. Once the Table Structure is
properly designed, these things, e.g. adding Field values,
will become much easier since Access is designed to work
with properly-structured and normalised databases.

HTH
Van T. Dinh
MVP (Access)

 

[J Cermak]

Van T. Dinh, thanks for your help with the work-arounds.
The database I am applying it to has 3 tables, employee
time, equipment time, and third party time all related to
job number in a 1 to many relationship. I wanted to add
the totals of the equipment, employees and third party on
a report, but each job may or may not have all 3.

I will try your work-arounds.

Thanks again.