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:

Date	Product	Colour	Qty. Sold
16/03/14	Ball	Yellow	4
17/03/14	Racket	White	3
19/03/14	Racket	Blue	7
20/03/14	Ball	Red	5
20/03/14	Racket	White	1
22/03/14	Ball	Red	1

<tbody>
</tbody>

Inventory:

Product	Colour	SOH as at end of trade 31/12/2013
Ball	Yellow	7
Ball	Red	6
Racket	White	4
Racket	Blue	8

<tbody>
</tbody>

This is what my query should look like:

Product	SOH at end of 2013	Quantity Sold	Current SOH
Ball	13	10	3
Racket	12	11	1

<tbody>
</tbody>

However, I suspect because I'm trying to obtain the sum of some calculated fields, it's outputting some astronomical values:

Product	SOH at end of 2013	Quantity Sold	Current SOH
Ball	78	42	36
Racket	72	42	30

<tbody>
</tbody>

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.

Product	BI	QS	CurrentInv
Ball	13	10	3
Racket	12	11	1

<tbody>
</tbody>

 

[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!