Grouping Problem

[Chip]

I have got a db that tracks our inventory quantities lot
#s and locations. I need a query that groups my data
first by the product # then by location so that the output
groups the products then combines all the 'same' materials
from different lots in the same location together. Se
example below:

Part# Lot# Location quantity

part1 lot1 stor1 25
part1 lot2 stor1 30
part1 lot1 stor2 10
part1 lot1 stor3 15

To get this output:

Part# Location quantity

part1 stor1 55
part1 stor2 10
part1 stor3 15

Part1 in Stor1 had 2 lot#s but it combines them. I want
the lots to be combined to give totals of parts regardless
of lot#.

I have tried the Group by with no luck but i may be doing
something wrong...

 

[Tom Ellison]

Dear Chip:

A "totals query" should perform this simply and directly.

Select the three columns (Part#, Location, and quantity). Change to a
totals query clicking the "sigma" icon. Make Part# and Location "Group By"
and quantity "total".

In SQL it looks like:

SELECT [Part#], Location, SUM(quantity) AS quantity
FROM YourTable
GROUP BY [Part#], Location

Hope this is what you needed. If not, I hope we can use it to get a
discussion going.