query to give stock levels?

[edenforge]

Hi all,
I have the following query (Access 2010):

SELECT tblSales.ProductID, Sum(tblSales.QuantSold) AS SumOfQuantSold, Sum(tblSales.QuantBought) AS SumOfQuantBought
FROM tblSales
GROUP BY tblSales.ProductID;

This produces the following result:
ProductID SumOfQuantSold SumOfQuantBought
widget1 (QS)2 (QB)100
widget2 (QS)20 (QB)400
widget3 (QS)5 (QB)100
sorry about the formatting, there does not appear to be a TAB so I have used (QS) for SumOfQuantSold and (QB) for SumOfQuantBought.

What I would like to do is to get it to produce a stock level for each item as in
[SumOfQuantBought] - [SumOfQuantSold] to display the current stock levels on a form.

How can I do this?

Regards
Bob (Access Novice)

 

[pbaldy]

You can simply do that on the form. If you want it in the query:

Sum(tblSales.QuantBought) - Sum(tblSales.QuantSold) AS QuantOnHand

Hint, you can use Code tags to preserve formatting in your post:

ProductID SumOfQuantSold SumOfQuantBought
widget1               2    100
widget2              20    400
widget3               5    100

 

[edenforge]

You can simply do that on the form. If you want it in the query:

Sum(tblSales.QuantBought) - Sum(tblSales.QuantSold) AS QuantOnHand

Hi Paul
Thanks for the reply.
As I said, I am very much an Access NOVICE.
Can you expand on the above:
a: how to do it on the form
b: how to add the line above to a query
Many thanks
Bob

 

[pbaldy]

In a form:

=[SumOfQuantBought] - [SumOfQuantSold]

To add it to the query:

SELECT tblSales.ProductID, Sum(tblSales.QuantSold) AS SumOfQuantSold, Sum(tblSales.QuantBought) AS SumOfQuantBought, Sum(tblSales.QuantBought) - Sum(tblSales.QuantSold) AS QuantOnHand
FROM tblSales
GROUP BY tblSales.ProductID;

 

[edenforge]

In a form:

=[SumOfQuantBought] - [SumOfQuantSold]

To add it to the query:

SELECT tblSales.ProductID, Sum(tblSales.QuantSold) AS SumOfQuantSold, Sum(tblSales.QuantBought) AS SumOfQuantBought, Sum(tblSales.QuantBought) - Sum(tblSales.QuantSold) AS QuantOnHand
FROM tblSales
GROUP BY tblSales.ProductID;

Hi Paul,
Thanks for that.
is there a way to get somthing like this to display on a form?

ProductID   QuantOnHand       
widget1         98       
widget2         380       
widget3         95

Regards
Bob

 

[pbaldy]

You can base the form on that query and only display those 2 fields.