Subtraction

[Wantula]

I Have the following SQL ststements the first one is giving me the total
stock available and the next one is giving me the total stock sold out.
How can i subtract the results of the second SQL statement from the first
one, so that I know the Stock remaining.What SQL statement can I use?

SELECT Stock.Item, Sum(Stock.Quantity) AS SumOfQuantity
FROM Stock
GROUP BY Stock.Item;

SELECT SaleDetail.[Item Bought], Sum(SaleDetail.Quantity) AS SumOfQuantity
FROM SaleDetail
GROUP BY SaleDetail.[Item Bought];

 

[Alfred]

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs1 As ADODB.Recordset
Set cn = New ADODB.Connection
Set cn = CurrentProject.Connection

Set rs=cn.execute("SELECT Stock.Item, Sum(Stock.Quantity) AS SumOfQuantity
FROM Stock GROUP BY Stock.Item")

Set rs1 = cn.Execute("SELECT SaleDetail.[Item Bought],
Sum(SaleDetail.Quantity) AS SumOfQuantity

FROM SaleDetail
GROUP BY SaleDetail.[Item Bought]; ")

dim difference as double
difference = rs!SumOfQuantity - rs1!SumOfQuantity
Alfred

I Have the following SQL ststements the first one is giving me the total
stock available and the next one is giving me the total stock sold out.
How can i subtract the results of the second SQL statement from the first
one, so that I know the Stock remaining.What SQL statement can I use?

SELECT Stock.Item, Sum(Stock.Quantity) AS SumOfQuantity
FROM Stock
GROUP BY Stock.Item;

SELECT SaleDetail.[Item Bought], Sum(SaleDetail.Quantity) AS SumOfQuantity
FROM SaleDetail
GROUP BY SaleDetail.[Item Bought];

 

[David Seeto via AccessMonster.com]

If you want a SQL only solution, you could try saving each of these SELECT statements as queries and then joining them. If, for example,you save the former as StockSummary and the latter as SalesSummary, the query would be:

SELECT SalesSummary.[Item Bought], [StockSummary]![SumOfQuantity]-[SalesSummary]![SumOfQuantity] AS Difference
FROM SalesSummary INNER JOIN StockSummary ON SalesSummary.[Item Bought] = StockSummary.Item;

Of course, this is going to present problems if you have Items that are in Stock but haven't been sold, and vice versa. You can deal with one of these situations but not the other with the relevant Outer Join instead of an Inner Join, but if both situations are possible, you're going to have to join it up with the Item Master table:

SELECT [Item Master].Item, [StockSummary]![SumOfQuantity]-[SalesSummary]![SumOfQuantity] AS Difference
FROM ([Item Master] LEFT JOIN StockSummary ON [Item Master].Item = StockSummary.Item) LEFT JOIN SalesSummary ON [Item Master].Item = SalesSummary.[Item Bought];

*****************************************
* A copy of the whole thread can be found at:
* http://www.accessmonster.com/Uwe/Forum.aspx/access-tablesdbdesign/6361
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abuse.aspx?aid=463f3c0e3d8746f7b7be76ccd98cd99d
*****************************************