Dear Penny:
What seems likely to me is that there are rows in your tables that are
not related to rows in other tables. If your design is meant to
disallow rows in a dependent table where the related row in the parent
table does not exist (using a common set of key columns) then perhaps
you have not set up this relationship so it can be enforced.
Now, every row in a dependent table either has a related row in the
parent table or it does not. If you write a query that shows how many
rows in the dependent table ARE related to a single row in the parent
table, and another query that shows how many rows in the dependent
table DO NOT have a related row in the parent table, then the sum of
these two sets should match the number of total rows in the dependent
table.
But, there's another possiblility. Based on the set of common key
columns between the dependent table and the parent table, it may be
that you have made it possible to have more than one row in the parent
table for some of the rows in the dependent table. The usual design
is to make the set of key columns in the parent table a unique key to
that table, but it is not required that it be so. So, this would
further confuse the issue by resulting in a query that, when you
create a join, results in a cross-product of all the rows in the two
tables that share each common key value.
Without considerable detail about how you have designed this data, it
is impossible to say just what is causing your perplexity. But, for
experience in designing and implementing data, I would say that what
you are experiencing is likely to be some combination of the above.
Joins between tables will work in ways that match the constraints you
put on the data when it is entered into the database. Without the
constraints (relationships and unique keys) you really cannot expect
the data to work the way you are expecting. If you do have all the
necessary constraints but are still experiencing the kind of
difficulty you mention, then either your database is corrupted in some
way or your queries are not written correctly.
It's pretty hard to say much more than this.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
