MS Access add up same parts in column and give qty

[gotcha]

I have a ms access database that I need the part numbers that are the
same to be added up to give a quantity and the have the locations to
be put together in the same field. Here is an example of what I have:

Part Number Qty Location
162176-001 MAIN01C1
162176-001 MAIN01C1
162176-001 MAIN01C1
162176-001 MAIN01C1
162176-001 MAIN01C1
162176-001 MAIN01C3
148680-001 MAIN01B3
148680-001 MAIN01B4
148680-001 MAIN01B5

And this is what I need.

Part Number Qty Location
162176-001 6 MAIN01C1, MAIN01C3
148680-001 3 MAIN01B3, MAIN01B4, MAIN01B5

Thanks for any help in advance.
gi*@ambry.com

[Pieter Linden]

gi*@ambry.com (gotcha) wrote in message news:<73**************************@posting.google. com>...

I have a ms access database that I need the part numbers that are the
same to be added up to give a quantity and the have the locations to
be put together in the same field. Here is an example of what I have:

Part Number Qty Location
162176-001 MAIN01C1
162176-001 MAIN01C1
162176-001 MAIN01C1
162176-001 MAIN01C1
162176-001 MAIN01C1
162176-001 MAIN01C3
148680-001 MAIN01B3
148680-001 MAIN01B4
148680-001 MAIN01B5

And this is what I need.

Part Number Qty Location
162176-001 6 MAIN01C1, MAIN01C3
148680-001 3 MAIN01B3, MAIN01B4, MAIN01B5

The PartNumber Qty part is a snap...
qryPartCount:
SELECT tblPartNos.PartNo, Count(tblPartNos.Location) AS PartCount
FROM tblPartNos
GROUP BY tblPartNos.PartNo;

Final Query:
SELECT qryPartCount.PartNo, qryPartCount.PartCount,
fconcatchild("tblPartNos","PartNo","Location","Str ing",[PartNo]) AS
ListParts
FROM qryPartCount;

You need this function to do it...
fConcatChild...
http://www.mvps.org/access/modules/mdl0004.htm