Roll up unique IDs and corresponding sums

  • Thread starter Thread starter Max
  • Start date Start date
M

Max

From Table1:

StaffID Prod1 Prod2
2222 200 120
1111 170 190
2222 130 180
1111 200 150

etc
etc

how do I query it to "roll up" like this:
(Unique StaffIDs with corresponding sums)

StaffID Prod1 Prod2
2222 330 300
1111 370 340


Thanks
 
Use an aggregate (Totals) query.

SELECT StaffID, Sum(Prod1) as Prod1Total, Sum(Prod2) as Prod2Total
FROM [YourTable]
GROUP BY StaffID

In design view (query grid)
-- Add your table
-- Add the three fields
-- Select View: Totals from the menu
-- Change GROUP BY to SUM under Prod1 and Prod2
-- Run the query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top