Sum of two Quantity fields (1 Viewer)

Morgana

New member
Local time
Today, 13:15
Joined
May 4, 2021
Messages
28
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.
1630366565719.png

I appreciate any help you can offer.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:15
Joined
Feb 19, 2013
Messages
16,553
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:15
Joined
May 7, 2009
Messages
19,169
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))
 

mike60smart

Registered User.
Local time
Today, 20:15
Joined
Aug 6, 2017
Messages
1,899
I would recommend you review the structure as suggested by June7.
 

Morgana

New member
Local time
Today, 13:15
Joined
May 4, 2021
Messages
28
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?


1630422845262.png
 

June7

AWF VIP
Local time
Today, 12:15
Joined
Mar 9, 2014
Messages
5,423
Function can be called from query or textbox or VBA.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:15
Joined
Feb 19, 2002
Messages
42,973
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
 

Users who are viewing this thread

Top Bottom