Summing data from multiple queries

[Don Seckler]

I have three queries one that shows the payments received from a
vendor, one that shows the value of product shipped to the vendor and
one that shows the value of product returned by the vendor. Each of
these queries will return multiple records for each vendor.

I am trying to calculate the balance owed by each vendor using the
data from these three queries. So I need to total the value of the
product shipped, then total the value of the returns and subtract it
from the shipments, and finally total the payments and subtract them
from the shipments as well.

I'd like to use this total in a report(statement) and also to write it
to a table for use as the starting balance for the next month's
statement.

I was thinking a query of the other three queries was the way to go.
Am I right? Anyone have any suggestions?

 

[chris]

This is a candidate for a step by step multiple query
solution.

First I would use a union query of the three source
queries to produce a master list of vendors

Select vendor from qrypayments
union
select vendor from qryshipped
union
select vendor from qryreturned

Then I would create three more queries based on the three
source queries which sums the values

Select vendor sum(payments) from qrypayments group by
vendor

....etc...

Then I would write a 5th query using the union query as a
base, and left joining the three new queries linked on
Vendor, including the three currency columns, and a summed
column to produce the net result you are looking for.

Hope this helps