Combining Duplicates in Select qry

[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)

 

[Stefan Hoffmann]

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

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


mfG
--> stefan <--

 

[kc-mass]

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

 

[Jack Leach]

Easy enough. Thanks guys.

--
Jack Leach
www.tristatemachine.com

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