Exclude Column from Total Sum

[ratcat]

Cross-posting. https://www.excelforum.com/access-ta...total-sum.html



Reason: No responds

G'day Everyone,

Uncle Google won't tell me how to exclude (ignore) a column from summing within a query.

I have 2 tables. One is 'Goods_In', other one is 'Goods_Out'. Through the Access wizard query I have used the 'Find Unmatched Query Wizard' to create a stock on hand queries. This column I'm trying to exclude is the primary key (customlised barcode number) of the query table. Now all I need to do is total the products. But how ?

Side note: All information is import via excel workbook as I'm dealing with third party inventory as well.

[pbaldy]

What is the SQL of your query? Offhand I'm thinking you either want to change Sum to Group By for that column or delete that column from the query.

[ratcat]

G'day Paul,

Thank you for your respond. I didn't think to have the product key as 'Sum'. I tried that on a test database and visual its looking like it should, now the fun of double checking.


I've added the SQL query as you or other forum members may suggest a better way of doing things. Thanks.

SELECT GoodsIn.[Product Key], GoodsIn.Warehouse, GoodsIn.Manufacturing_Date, GoodsIn.Expiration_Date, GoodsIn.Facility_Item_Number, GoodsIn.ThirdParty_Item_Number, GoodsIn.Facility_Item_Description, GoodsIn.Batch_Number, Sum(GoodsIn.Qty_Units) AS SumOfQty_Units, Sum(GoodsIn.Qty_Kgs) AS SumOfQty_Kgs, GoodsIn.StatusFROM GoodsIn LEFT JOIN GoodsOut ON GoodsIn.[Product Key] = GoodsOut.[Product Key]GROUP BY GoodsIn.[Product Key], GoodsIn.Warehouse, GoodsIn.Manufacturing_Date, GoodsIn.Expiration_Date, GoodsIn.Facility_Item_Number, GoodsIn.ThirdParty_Item_Number, GoodsIn.Facility_Item_Description, GoodsIn.Batch_Number, GoodsIn.Status, GoodsOut.[Product Key]
HAVING (((GoodsOut.[Product Key]) Is Null));

[pbaldy]

Glad you got it solved, though I'm confused by the use of the unmatched query. I would expect you to subtract goods out from goods in, but you know your situation best.

[pbaldy]

Though I suppose if it's a serialized item with a one-to-one relationship between in and out, your query would give you what came in without going out thus ending inventory. I was thinking about quantities not matching.