Queries - Sum of Calculated Fields

[PrinceHairy]

Hi all,

I have the following databases (a sample) and am trying to obtain a summary query from these:

Sales:
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Product
[/TD]
[TD]Colour
[/TD]
[TD]Qty. Sold
[/TD]
[/TR]
[TR]
[TD]16/03/14
[/TD]
[TD]Ball
[/TD]
[TD]Yellow
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]17/03/14
[/TD]
[TD]Racket
[/TD]
[TD]White
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]19/03/14
[/TD]
[TD]Racket
[/TD]
[TD]Blue
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]20/03/14
[/TD]
[TD]Ball
[/TD]
[TD]Red
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]20/03/14
[/TD]
[TD]Racket
[/TD]
[TD]White
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]22/03/14
[/TD]
[TD]Ball
[/TD]
[TD]Red
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

Inventory:
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Product
[/TD]
[TD]Colour
[/TD]
[TD]SOH as at end of trade 31/12/2013
[/TD]
[/TR]
[TR]
[TD]Ball
[/TD]
[TD]Yellow
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]Ball
[/TD]
[TD]Red
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]Racket
[/TD]
[TD]White
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Racket
[/TD]
[TD]Blue
[/TD]
[TD]8
[/TD]
[/TR]
</tbody>[/TABLE]

This is what my query should look like:
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Product
[/TD]
[TD]SOH at end of 2013
[/TD]
[TD]Quantity Sold
[/TD]
[TD]Current SOH
[/TD]
[/TR]
[TR]
[TD]Ball
[/TD]
[TD]13
[/TD]
[TD]10
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Racket
[/TD]
[TD]12
[/TD]
[TD]11
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

However, I suspect because I'm trying to obtain the sum of some calculated fields, it's outputting some astronomical values:
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Product
[/TD]
[TD]SOH at end of 2013
[/TD]
[TD]Quantity Sold
[/TD]
[TD]Current SOH
[/TD]
[/TR]
[TR]
[TD]Ball
[/TD]
[TD]78
[/TD]
[TD]42
[/TD]
[TD]36
[/TD]
[/TR]
[TR]
[TD]Racket
[/TD]
[TD]72
[/TD]
[TD]42
[/TD]
[TD]30
[/TD]
[/TR]
</tbody>[/TABLE]


I suspect the way to fix this would be via using a SQL query instead of the query design - any ideas?

 

[sheetspread]

Try this:

SELECT X.ProdID, Sum(X.SOH31122013) AS BI, ((select sum(QtySold) from Sales where ProdID = x.ProdID)) AS QS, [BI]-[QS] AS CurrentInv
FROM Inventory AS X
GROUP BY X.ProdID;

 

[PrinceHairy]

Try this:

SELECT X.ProdID, Sum(X.SOH31122013) AS BI, ((select sum(QtySold) from Sales where ProdID = x.ProdID)) AS QS, [BI]-[QS] AS CurrentInv
FROM Inventory AS X
GROUP BY X.ProdID;

The only thing is I would still like the output to be like below (by Product name and not colour-specific), hence why I tried keeping the X.ProductName to no avail.

[TABLE="class: cms_table_cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Product
[/TD]
[TD]BI
[/TD]
[TD]QS
[/TD]
[TD]CurrentInv
[/TD]
[/TR]
[TR]
[TD]Ball[/TD]
[TD]13[/TD]
[TD]10[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Racket[/TD]
[TD]12[/TD]
[TD]11[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

I posted this problem on UtterAccess and azolder replied with:

SELECT Products.Product, Sum(Inventory.SOH31122013) AS Inventory, Sum(qryTotSold.TS) AS QtySold, Sum([SOH31122013]-[ts]) AS Difference
FROM (Products INNER JOIN Inventory ON Products.ProdID = Inventory.ProdID) INNER JOIN [SELECT Sales.ProdID, Sum(Sales.QtySold) AS TS FROM Sales GROUP BY Sales.ProdID ]. AS qryTotSold ON Products.ProdID = qryTotSold.ProdID
GROUP BY Products.Product;

it worked on the sample

 

[PrinceHairy]

Oh wow, looking at that is mind-boggling itself haha. Managed to get it to work with my database - thanks a lot for that and all your help!