tables' relationship problem

[Alex]

Hi everybody,

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.

Thanks in advance.

 

[Gary Walter]

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;

 

[Van T. Dinh]

Not sure from your description but it should be something like:

SELECT T1.ProductID, {Other Fields},
T1.SoldQty - Nz(T2.ReturnedQty, 0) AS Qty
FROM T1 LEFT JOIN T2
ON T1.ProductID = T2.ProductID

 

[Alex]

Thanks a lot.

Alex

-----Original Message-----

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;




.