Union Query

[dave1234]

I have managed to get this Union query to help in some calculations for a stock take but have no Sum of Quantity in it so I done another Query via the wizard and done a Sum of Query in that for quantity......its nearly perfect but it some times doesnt recognize a new stock ammount entred, any ideas.
Also can someone do a Sum of Quantity for me in the union query, I didnt create the one in the data base a friend of mine done it and is now away on holidays.



SELECT stockSold.[Stock Name],stocksold.[StockID], (stocksold.Qty * -1) AS [QTY] FROM stocksold
UNION SELECT stock.[Stock Name], stockadded.StockID, (stockadded.QtyAdded) AS QTY
FROM stock INNER JOIN stockadded ON stock.StockID = stockadded.StockID;

 

[namliam]

I think you want this query:
SELECT stockSold.[Stock Name],stocksold.[StockID], (stocksold.Qty * -1) AS [QTY] FROM stocksold
UNION All SELECT stock.[Stock Name], stockadded.StockID, (stockadded.QtyAdded) AS QTY
FROM stock INNER JOIN stockadded ON stock.StockID = stockadded.StockID;

For the sum:

SELECT [Stock Name],[StockID], sum([qty) from
(SELECT stockSold.[Stock Name],stocksold.[StockID], (stocksold.Qty * -1) AS [QTY] FROM stocksold
UNION All SELECT stock.[Stock Name], stockadded.StockID, (stockadded.QtyAdded) AS QTY
FROM stock INNER JOIN stockadded ON stock.StockID = stockadded.StockID) as ToSum
group by [Stock Name],[StockID]

it some times doesnt recognize a new stock ammount entred

Try running the 2 queries seperatly and fixing the problem there first.... I think you will find it has something to do with INNER JOIN and the 2 tables concerned.

Regards