Combining Duplicates in Select qry

  • Thread starter Thread starter Jack Leach
  • Start date Start date
J

Jack Leach

Hi all, tia...

As of now I do this via DAO Recordsets, but I assume there must be a way to
handle it via query (also assuming that a query will always be faster than
working via Recordset). If I had a list of ten records consisting of a
PartNumber and Qty, and say three of those records are the same PartNumber
with various Qtys, how can I write a query that will return only one instance
of the PartNumber with a total Qty from all three records?

Ex.

PN | Qty
12345 | 2
12345 | 2
12345 | 2
12346 | 3
12347 | 10
12348 | 10

return:

12345 | 6
12346 | 3
12347 | 10
12348 | 10

Thanks for any insight,

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
 
hi Jack,

Ex.

PN | Qty
12345 | 2
12345 | 2
12345 | 2
12346 | 3
12347 | 10
12348 | 10

return:

12345 | 6
12346 | 3
12347 | 10
12348 | 10
Click to expand...

SELECT [PN], Sum([Qty])
FROM yourTable
GROUP BY [PN]


mfG
--> stefan <--
 
In the query design panel menu select "View", "Totals"
Then in the fields set PartNumber to "Group on" and set Qty to "Sum"

Regards

Kevin
 
Back
Top