Sum of two Quantity fields

[Morgana]

I'm trying to make a simple On-hand query where I have two tables, Incoming and Distributed. Each table has a quantity. I would like the query to return a Sum of Add Incoming and Subtract Distributed. Here is what I have for the query so far. I'm not sure why the Incoming-tbl is not showing as One-to-Many, when I look at Join Properties, it looks the same as the Distributed table.

This is what I have for On-Hand: Sum ([Transactions-Incoming-tbl].[Quantity])-sum([Transactions-Distributed-tbl].[Quantity])

I'm getting an error saying "Your query does not include the specified expression 'ProductID' as part of an aggregate function."

Ultimately what I'm trying to make is a Report that will show an On-hand quantity for each product. I only have about 50 products, so I don't need a full inventory system.

I appreciate any help you can offer.

 

[CJ_London]

To sum you need to use an aggregate query and group on productid

I would lose the hyphens in your table names, should only use alpha numeric and underscore characters

but I suspect your query wont give you what you want anyway. If you have one incoming record for a product and two outgoing, your incoming will be included twice.

you need to use a union query to combine the two tables

 

[arnelgp]

remove Transactions-Incoming-Tbl and Transactions-Distributed-tbl from your Query.

1. create Total Query (qryTotalIncoming) from Transactions-Incoming-Tbl:
SELECT ProductID, Sum(Quantity) As Incoming FROM [Transactions-Incoming-Tbl] GROUP BY ProductID;

2.create Total Query (qryTotalDistributed) from Transactions-Distributed-Tbl:
SELECT ProductID, Sum(Quantity) As Distributed FROM [Transactions-Distributed-Tbl] GROUP BY ProductID;

2.Using Add the two New Queries to your Original Query.
Left Join Product_tbl.ProductID To qryTotalIncoming.ProductID
Left Join Product_tbl.ProductID to qryTotalDistributed.ProductID

3.Create the On-Hand Column:

On-Hand: CDbl(Nz([Incoming],0)-Nz([Distributed],0))

 

[June7]

Review http://allenbrowne.com/AppInventory.html

 

[mike60smart]

I would recommend you review the structure as suggested by June7.

 

[Morgana]

Review http://allenbrowne.com/AppInventory.html

Thanks, June7, this looks like what I'm after, but I'm a complete newbie to coding (my last DB I wrote was in Access 95 (?) and I did everything in queries). I started over and followed his structure, but not sure where to add the function he gives. Do I add a form or query with the fields I want and add the function to that somewhere?

 

[June7]

Function can be called from query or textbox or VBA.

 

[Pat Hartman]

Inventory should be managed by transactions. All the transactions REGARDLESS of type get added to the same table. Either keep the quantities with natural signs or make them all positive and keep a sign in tblTranType to multiple the quantity by. I use +1 or -1. Then the expression becomes
Sum(Nz(qty,0) * tblTranType.Sign) As QtyOnHand