How to sum multiple occurences of same item number.

[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_Ordered
Order_Number
Unit_Number
Qty_of_Units

tbl_Inventory_Items_in_Unit
Unit_Number
Item_Number
Qty_Per_Unit

I have a query that uses tbl_Units_Ordered and tbl_Inventory_Items_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.)

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!

[PianoMan64]

@ajhayes

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.