How to sum multiple occurences of same item number.

**PianoMan64** · Mar 5 '09, 02:07 AM

Originally posted by ajhayes

I posted a few weeks ago about trying to set up a way to order by unit number (http://bytes.com/topic/access/answer...-ordering-unit)

I'm trying to use a similar structure to what OldBirdman suggested in my previous post, but I'm a bit stuck.

Here is what I've set up:

tbl_Orders
Order_Number
Order_Date

tbl_Units_Order ed
Order_Number
Unit_Number
Qty_of_Units

tbl_Inventory_I tems_in_Unit
Unit_Number
Item_Number
Qty_Per_Unit

I have a query that uses tbl_Units_Order ed and tbl_Inventory_I tems_in_Unit:
Unit_Number
Qty_of_Units
Item_Number
Qty_per_Unit
= [qty_of_units]*[qty_per_unit]

My problem now is that most of the poletop units are made up of some combination of the same parts. So, my query is getting a total of each item per unit number, but I ultimately need to sum the total number of each item across all unit numbers. For example, as you can see below, there is a part number 285-073-39292 in an A1 as well as in an A3.)

Code:

Unit_Number	Qty_of_Units	Item_Number	Qty_per_Unit	Total_Qty
a1	1	285-073-39292	1	1
a1	1	285-073-32359	1	1
a1	1	285-073-32369	1	1
a3	3	285-073-39292	1	3
a3	3	285-073-32359	1	3
a3	3	285-073-32369	1	3
a3	3	285-073-43378	1	3
a3	3	285-073-16377	1	3
a3	3	285-073-32678	1	3
a3	3	285-073-15659	1	3

How do I go about summing the quantity for all occurences of the same part number? Or is this plan completely flawed to begin with? I am very much a novice at this, and I'm beginning to feel like I've bitten off more than I can chew with this project!

My simple suggestion would be to simply go into a blank query. Select the main table that your data is coming from.

at the top of the query designer you'll see a greek letter for Sum (it is a backwards 3.) When you point to it it will say total.

When you click that, it will put a new line in the query builder that says GROUP BY.

The Group BY option allows you to group items when that particular value changes. If you want to SUM a value, then simply change the GROUP BY option to SUM. Then those value will the be summed for all items that are grouped.

and so on.

If you have a calculation that you want to do on these groups, then you would change the GROUP BY to EXPRESSION. then that allows you to put a formula as a calculated field.

I hope that helps,

Joe P.

How to sum multiple occurences of same item number.

How to sum multiple occurences of same item number.

Comment