Combining Duplicates in Select qry

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,

On 26.04.2010 11:49, Jack Leach wrote:
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 --

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" dymondjack at hot mail dot com wrote in message
news
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)

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)



"Jack Leach" wrote:

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)