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:
UNION SELECT ProductID
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
Please post questions to the forums, where others may benefit.
I do not offer free assistance by e-mail. Thank you!
A subselect would work better.
SELECT ProductID, (Select Sum([order details].Quantity) FROM [order details] WHERE [order details].ProductID = Products.ProductID) AS Quantity
To get import and export data you need a way to identify the difference. Maybe that is what you are doing with the customer table (that is not good design).
Wouldn't you need a GROUP BY productid with that?
I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
Please use [Code]your code goes in here[/Code] tags when posting code.
Before posting your question, did you look here
Got a question on Linux? Visit our Linux sister site.
Modifications Required For VB6 Apps To Work On Vista
Productid should be unique in the products table.
By vaibhavpingle in forum Java
Last Post: 11-30-2006, 06:58 AM
By srinivasc_it in forum .NET
Last Post: 08-18-2006, 02:15 PM
By Jason Salas in forum ASP.NET
Last Post: 08-18-2003, 08:49 AM
By Nirit Touboul in forum Database
Last Post: 02-25-2001, 12:34 PM
By Charles in forum VB Classic
Last Post: 10-06-2000, 04:30 PM
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL