P
peljo via AccessMonster.com
Will you help me built a union query ? I have 2 queries, qryinput and
qryoutput.Out of these query i have a third query called qryDiff
substracting these
queries. However qryDiff does not show all the goods sold, only those goods
that are substracted.Therefore i want to build an union query but somehow i
cannot do it. Will you help ?
My first query, qryinput is :
SELECT [order details].ProductID, Sum([order details].Quantity) AS Sum1,
orders.orderid
FROM (orders INNER JOIN [order details] ON orders.orderid = [order details].
OrderID) INNER JOIN products ON [order details].ProductID = products.
Productid
GROUP BY [order details].ProductID, orders.orderid;
My second query, qryoutput is :
SELECT [order details].ProductID, orders.orderid, [order details].Quantity AS
Sum2
FROM ((orders INNER JOIN [order details] ON orders.orderid = [order details].
OrderID) INNER JOIN Customers ON orders.customerid = Customers.Customerid)
INNER JOIN products ON [order details].ProductID = products.Productid;
My thirs query, qryDiff is :
SELECT qryProducts.ProductID, Sum(qryInput.Sum1) AS imported, Sum(qryOutput.
Sum2) AS exported
FROM (qryProducts LEFT JOIN qryInput ON qryProducts.ProductID = qryInput.
ProductID) LEFT JOIN qryOutput ON qryProducts.ProductID = qryOutput.ProductID
GROUP BY qryProducts.ProductID;
Now i want to convert the query qryDiff into an union query, but i cannot do
it.I somehow managed to build a simple query showing all the products:
SELECT ProductID
FROM qryInput
UNION SELECT ProductID
FROM qryOutput;
This query shows the productsid indeed, but i cannot add the other items form
the qryinput and qryoutput as the sum etc.
I also i cannot convert my query qryDiff into an uinion query, with fields
for the imported and the exported sums and also for the product names.
Where is my error and now could i achieve my aim? My aim is that in the query
qryDiff to show all the products, and not only the products sold.
I will be very grateful for any comments
qryoutput.Out of these query i have a third query called qryDiff
substracting these
queries. However qryDiff does not show all the goods sold, only those goods
that are substracted.Therefore i want to build an union query but somehow i
cannot do it. Will you help ?
My first query, qryinput is :
SELECT [order details].ProductID, Sum([order details].Quantity) AS Sum1,
orders.orderid
FROM (orders INNER JOIN [order details] ON orders.orderid = [order details].
OrderID) INNER JOIN products ON [order details].ProductID = products.
Productid
GROUP BY [order details].ProductID, orders.orderid;
My second query, qryoutput is :
SELECT [order details].ProductID, orders.orderid, [order details].Quantity AS
Sum2
FROM ((orders INNER JOIN [order details] ON orders.orderid = [order details].
OrderID) INNER JOIN Customers ON orders.customerid = Customers.Customerid)
INNER JOIN products ON [order details].ProductID = products.Productid;
My thirs query, qryDiff is :
SELECT qryProducts.ProductID, Sum(qryInput.Sum1) AS imported, Sum(qryOutput.
Sum2) AS exported
FROM (qryProducts LEFT JOIN qryInput ON qryProducts.ProductID = qryInput.
ProductID) LEFT JOIN qryOutput ON qryProducts.ProductID = qryOutput.ProductID
GROUP BY qryProducts.ProductID;
Now i want to convert the query qryDiff into an union query, but i cannot do
it.I somehow managed to build a simple query showing all the products:
SELECT ProductID
FROM qryInput
UNION SELECT ProductID
FROM qryOutput;
This query shows the productsid indeed, but i cannot add the other items form
the qryinput and qryoutput as the sum etc.
I also i cannot convert my query qryDiff into an uinion query, with fields
for the imported and the exported sums and also for the product names.
Where is my error and now could i achieve my aim? My aim is that in the query
qryDiff to show all the products, and not only the products sold.
I will be very grateful for any comments