combination of queries , and showing results in one column

[noorani]

i try to explain my problem,

i have 4 tables , first two tables name as 1) InvoiceMaster
2) InvoiceDetail,

the other 2 name as 3) ReturnMaster 4) ReturnDetail

in first 2 tables i store the information about product sold from stock,

and in last 2 tables i store data about the products which have returned back to stock

i want to make a qery which give me the exact information about how many products have been sold to a particular client, and what is the price for it,

Note: i need to take in account the ClientNumber , Product, Price, And the total amount sold to client including return product,

here what i'm doing.


Query 1
=======
In this Query i'm finding products sold to the clients with price . for the particular Vender and between date period.


SELECT jrn.reestr_number, Products.Hp_ProductName, jrn.outcode, Vender.VenderID, Products.ProductID, Customers.CustomerID, Customers.CompanyName, jrn.outprice, jrn.outquantity, jrn.jrdate
FROM ((jrn INNER JOIN Products ON jrn.productid = Products.ProductID) INNER JOIN Vender ON Products.VenderID = Vender.VenderID) INNER JOIN Customers ON jrn.customer = Customers.CustomerID
WHERE (((Vender.VenderID)=10) AND ((jrn.outquantity)<>0)) AND ((jrn.jrdate) Between [Forms]![MonHPDate]![StartDate] And [Forms]![MonHPDate]![EndDate]));

Query 2
=======
In this Query i'm find all the Return Products to clients with the price for the particular Vander and Date period

SELECT Products.ProductID, Vender.VenderID, Customers.CustomerID, Invoice_main.Invoice_no, jrn.rquantity, jrn.jrdate
FROM ((Customers INNER JOIN (Vender INNER JOIN (jrn INNER JOIN Products ON jrn.productid = Products.ProductID) ON Vender.VenderID = Products.VenderID) ON Customers.CustomerID = jrn.customer) INNER JOIN (Return_main INNER JOIN Return_detail ON Return_main.Return_no = Return_detail.Return_no) ON jrn.returncode = Return_main.Return_no) INNER JOIN Invoice_main ON Return_main.Invoice_no = Invoice_main.Invoice_no
WHERE (((Vender.VenderID)=10) AND ((jrn.rquantity)<>0) AND ((Return_main.utv)=Yes)) AND ((jrn.jrdate) Between [Forms]![MonHPDate]![StartDate] And [Forms]![MonHPDate]![EndDate]));



Query 3
=======
In this query i'm Calling results of Query 1 and Query 2 and given the comparision, and using the join condition where i'm calling all the data from sold query and data which is same in the Returnd Query

SELECT Cus2APCOutFinder.reestr_number, Cus2APCOutFinder.jrdate, Products.ProductID, Products.Hp_ProductName, Customers.CustomerID, Customers.CompanyName, Customers.City, Cus2APCOutFinder.outprice, Cus2APCOutFinder.outquantity, NZ((Cus2APCReturnFinder.rquantity),0) AS treturn, [outquantity]-[treturn] AS CurrentQ, outprice*CurrentQ AS extendedprice
FROM ((Cus2APCOutFinder LEFT JOIN Cus2APCReturnFinder ON (Cus2APCOutFinder.outcode = Cus2APCReturnFinder.Invoice_no) AND (Cus2APCOutFinder.CustomerID = Cus2APCReturnFinder.CustomerID)) INNER JOIN Customers ON Cus2APCOutFinder.CustomerID = Customers.CustomerID) INNER JOIN Products ON Cus2APCOutFinder.ProductID = Products.ProductID;


Query 4
=======
In this Query i'm calling Query 3 and showing the results in my report, but of course i don't have correct results,


SELECT Cus2APCCurrentFinder.jrdate, Cus2APCCurrentFinder.ProductID, Cus2APCCurrentFinder.Hp_ProductName, Cus2APCCurrentFinder.CustomerID, Cus2APCCurrentFinder.CompanyName, Cus2APCCurrentFinder.City, Cus2APCCurrentFinder.outprice, Cus2APCCurrentFinder.CurrentQ, Cus2APCCurrentFinder.extendedprice
FROM Cus2APCCurrentFinder
WHERE (((Cus2APCCurrentFinder.CurrentQ)<>0));



i think may be my approach is not correct for solving this problem, may be i need to write some kind of function or may be some modified query , but please help me, if you understand my problem,