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.