Merge 3 simple queries into 1 2

[Gazonice]

Hi,

Is it possible to take 3 simple queries which collectively show stock on hand and merge them into 1 query?

Query 1 sums stock added:
========================

SELECT tblAddProducts.ProductID, Sum(tblAddProducts.ProductQTYadded) AS SumOfProductQTYadded
FROM tblAddProducts
GROUP BY tblAddProducts.ProductID;

Query 2: sums items sold:
========================

SELECT tblOrders.OrdersProductID, Sum(tblOrders.OrdersQuantity) AS SumOfOrdersQuantity
FROM tblOrders
GROUP BY tblOrders.OrdersProductID;

Query 3 subtracts the values on Query 2 from Query 1:
========================

SELECT tblProducts.ProductID, tblProducts.ProductName, Query1.SumOfProductQTYadded, Query2.SumOfOrdersQuantity, NZ([SumOfProductQTYadded])-nz([SumOfOrdersQuantity]) AS Expr1
FROM (Query1 RIGHT JOIN tblProducts ON Query1.ProductID = tblProducts.ProductID) LEFT JOIN Query2 ON tblProducts.ProductID = Query2.OrdersProductID;

I have spent several hours working at trying to merge these into 1 and I haven't been able to do it, so any help would be very much appreciated, if it can be done.

Thanks,

Garry

 

[IT4EVR]

I believe you can use a UNION query for this.

First query
UNION ALL
Second query
UNION ALL
Third query

 

[Gazonice]

Hi,

Thank you IT4EVR, I tried your suggestion but got a message:

"The number of columns in the two selected tables or queries of a union query do not match".

Any other suggestions?

Thank you.

Garry

 

[lespaul]

can you show a sample dataset and your expected results from the data?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

Something like this ?
SELECT P.ProductID, P.ProductName, A.SumOfProductQTYadded, O.SumOfOrdersQuantity
, Nz(A.SumOfProductQTYadded,0)-Nz(O.SumOfOrdersQuantity,0) AS LeftOnHand
FROM ((SELECT ProductID, Sum(ProductQTYadded) AS SumOfProductQTYadded FROM tblAddProducts GROUP BY ProductID
) AS A RIGHT JOIN tblProducts AS P ON A.ProductID = P.ProductID)
LEFT JOIN (SELECT OrdersProductID, Sum(OrdersQuantity) AS SumOfOrdersQuantity FROM tblOrders GROUP BY OrdersProductID
) AS O ON P.ProductID = O.OrdersProductID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Gazonice]

Thanks PHV !!

That produces exactly the same result as the 3 separate queries and was what I have spent hours trying to do!

How did you achieve this and so quickly too?

@ lespaul, the dataset result looks like this (simplified labels):

ID | Product | bought | sold | On hand
--------------------------------------------
1 | Item 1 | 5 | 3 | 2

I wanted to merge the queries into 1 file so that I could use the SQL in the afterupdate event of a combo to give the user the current stock level.

Thank you everyone for your replies.

Garry

 

[IT4EVR]

I agree, this PHV guy is far away the brightest mind in this forum, he should be fooling around with more complex databases like Oracle.

Anyone who can come up with a query like that in 30 seconds is freaking brilliant.

 

[IT4EVR]

He puts me, and most other people, to shame....lol

 

[IT4EVR]

We may be better off letting PHV answer all the posts. There is NOTHING this guy doesn't know...lol

 

[Gazonice]

Hi,

I've hit a small problem:

If I close the database, then re-open it and run the query (above) I see a message:

"The Microsoft Jet database engine cannot find the input table or query 'SELECT ProductID, Sum(ProductQTYadded) AS SumOfProductQTYadded from tblAddProducts GROUP BY ProductID'. Make sure it exists and that its name is spelled correctly."

Curiously enough, if I copy and paste PHV's original code into the query - it works again until the database is shutdown.

Can anyone throw any suggestions my way?

Many thanks,

Garry

 

[IT4EVR]

You might want to make a copy of that sql statement, then delete that original query, compact/repair the database.

Then create a new query, paste the PHV code and then see what happens.

Sounds like your database is getting corrupted.