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]

Are you using an inner or cross join? Both can be done in design view. A quick guaranteed fix is the make-table query (analogous to paste-values in Excel), but you should be able to get the proper sums without one.

 

[PrinceHairy]

Are you using an inner or cross join? Both can be done in design view. A quick guaranteed fix is the make-table query (analogous to paste-values in Excel), but you should be able to get the proper sums without one.

I'm actually not using any join at the moment. Can you run me through the steps for that? It's an area I don't know a lot about.

I did manage to fix it via the make-table query method, but because I'm dealing with a big database, I'm looking to make the database as small a filesize as possible.

 

[sheetspread]

If Sales and Inventory are separate tables then you would list them both via Show Table in the query design view, then drag the Product field name from one into the other. A line connecting them will be displayed, and INNER JOIN will be visible in the SQL view window. Then when you add fields only the records common to both tables will appear, corresponding to each product. Use a summary query of the Sales table of course (without the Date field) so you get distinct rows.

 

[PrinceHairy]

Awesome! That did the trick! Do you know of a way to perform this without having to use a summary query? Just trying to minimise the filesize.

 

[sheetspread]

You can write the join and distinctrow in one query, but I don't think a subquery, Dsum, report grouping, or any other method is more efficient.

 

[PrinceHairy]

You can write the join and distinctrow in one query, but I don't think a subquery, Dsum, report grouping, or any other method is more efficient.

I had a go, but it's not working (comes up with Syntax error). Any ideas?

SELECT Sales.Product,
           [Inventory.SOH as at end 2013],
           Sales.QtySold,
FROM Sales
           JOIN (SELECT DISTINCT Product, [SOH as at end of 2013] from Inventory) Inventory ON Sales.Product = ProductName
GROUP BY Sales.Product;

 

[sheetspread]

Yeah it's trickier than I thought, if even possible. Here's the subquery:

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

(I changed two field names in the tables: SOHDec312013 and QtySold because of the syntax with the spaces and the underscores didn't seem to work for some reason)

 

[PrinceHairy]

Yeah it's trickier than I thought, if even possible. Here's the subquery:

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

(I changed two field names in the tables: SOHDec312013 and QtySold because of the syntax with the spaces and the underscores didn't seem to work for some reason)

That worked perfectly! Just a quick question - how would I do it if there is a Product ID for each product? (I'm planning on normalising the database that I have)

Products:

ProdID	Product	Colour
B0001	Ball	Yellow
R0001	Racket	White
R0002	Racket	Blue
B0002	Ball	Red

<tbody>
</tbody>

Sales:

Date	ProdID	QtySold
16/03/14	B0001	4
17/03/14	R0001	3
19/03/14	R0002	7
20/03/14	B0002	5
20/03/14	R0001	1
22/03/14	B0002	1

<tbody>
</tbody>

Inventory:

ProdID	SOH31122013
B0001	7
B0002	6
R0001	4
R0002	8

<tbody>
</tbody>

 

[PrinceHairy]

I've tried the following to no avail:

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