Incorrect Values in Query

[Sailor1877 via AccessMonster.com]

I am trying to create a query with 4 tables to report sales out quantities
and I am getting incorrect quantities whenever I add the 4th table to the
query. Without the 4th table, the quantities come out correctly, but when I
try to add the 4th table, whether or not there is a join associated, the
quantities are multiplied.

The tables are:

Purchases (joined to company by the company name and joined to Transactions
by a Purchase Order Number)

Company

Transactions (a subset of purchases)

Parts (normally joined to Transactions through the part number and to the
company by the company name... but it doesn't matter if the join exists or
not.)

In the past I've resolved the issue by excluding the parts table but now
there is information on the parts table I need to include.

Anybody have any suggestions?

 

[Tom Ellison]

Dear Sailor:

This is commonly caused by the fact that a JOIN will create multiplication
of the number of rows when the table JOINed is on the Many side of a
One-to-Many relationship. It may be possible to use a subquery to obtain
the total value without the multiplication effect. However, the best thing
would be to step back and look at the problem from a different angle. If
this were a report, for example, I would think it best to show the total in
a more organized fashion. I would typically not show the same quantity many
times on a report just because there are multiple lines of detail about the
thing to which that single total applies. Indeed, this logic actually
explains the problem you've been having.

I surmise from your description that a Purchase has a single quantity
associated with it, but several Transactions associated with it. Perhaps
these transactions are payments and such.

If you were printing a report of this, you would not place the quantity on
every line where there is a transaction, and then add them up. Rather, you
would show the quantity only once, with a list of transactions above or
below that.

You do not say what the end product of this query is to be. If you
rationalize that product well, a query, or a set of queries, can be produced
to provide the product correctly. And, if you convey those details here, I
may be able to help you achieve that, if you need such help.

Tom Ellison