the same query problem

[Alex]

Thanks a lot for the help.
To check totals I included SoldQty from qry1 and
ReturnedQty from qry2 to the final query. But, the total
for ReturnedQty in the final query is less than the total
in qry2. It means that we have different Products or
Distributors in qry2 from qry1 for the period. (I took off
Brokers to simplify it and to have just two relationships).

Could you advise how I could manage with this new chalange.

Thanks.

Subject: Re: tables' relationship problem
From: "Gary Walter" <[email protected]>
Sent: 6/24/2004 10:05:48 AM

I have Table1, which includes all [Products],
[Distributors], [Brokers], and [SoldQty] and Table2 with
some (matched with Table1) [Products], [Distributors],
[Brokers], and [ReturnedQty].

How could I create a query to get all data from Table1 but
[Qty] column should be Table1.[SoldQty]-Table2.
[ReturnedQty].
I'm stuck with possible relationships between the tables.

If you only have one record in a table for every
set of Products/Distibutors/Brokers, then

SELECT
t1.Products,
t1.Distributors,
t1.Brokers,
t1.SoldQty - Nz(t2.ReturnedQty,0) As Qty
FROM
Table1 As t1
LEFT JOIN
Table2 As t1
ON
t1.Products = t2.Products
AND
t1.Brokers = t2.Brokers
AND
t1.Distributors = t2.Distributors;

Otherwise, you will have to make
2 "prequeries", then join them as
above.

qry1

SELECT
Products,
Distributors,
Brokers,
Sum(SoldQty) As Sold
FROM
Table1
GROUP BY
Products,
Distributors,
Brokers;

qry2

SELECT
Products,
Distributors,
Brokers,
Sum(ReturnedQty) As Returned
FROM
Table2
GROUP BY
Products,
Distributors,
Brokers;

SELECT
t1.Products,
t1.Distributors,
t1.Brokers,
t1.Sold - Nz(t2.Returned,0) As Qty
FROM
qry1 As t1
LEFT JOIN
qry2 As t1
ON
t1.Products = t2.Products
AND
t1.Brokers = t2.Brokers
AND
t1.Distributors = t2.Distributors;