Concatenate into one field

Hello,

I'm hoping someone might be able to offer some guidance to my problem. I have one query in MS Access which consists of 2 tables joined by a SEQUENCE_ID. By joining the two tables I am able to produce a query that gives me two fields, Part_Number and Product_Type. Below you can see some sample data:

Part_Number Product_Type
10MC35231 XYZ1A
10MC35231 XYZ1B
23XB94142 XYZ1A
23XB94142 XYZ1B
23XB94142 XYZ1C
42PA76251 XYZ1D

Ideally, it would be nice for the query to have an additional field which would concatenate the Product_Type's:

10MC35231 XYZ1A, XYZ1B
23XB94142 XYZ1A, XYZ1B, XYZ1C
42PA76251 XYZ1D

I found a few concatenate functions but it seemed that the either it did not like that I was joining two tables in the query or that the data was in only one table. I would really appreciate if someone could help give me a direction with this.

Thanks in advance,
Aaron

I have a similar thing for a job tracking application report. In your situation I would creat an update query which links the 2 tables. The join would be on the Part Number columns. You then run the update query which update the PartNumber_ProductType column by concatenating the PartNumber_ProductType column (master table)and the Prodcut Type column in the child table.

Here is my SQL:
UPDATE PJT_PRODUCT INNER JOIN (PJT_PRODUCT_SUPPLIED INNER JOIN Temp_Staff_Reports ON PJT_PRODUCT_SUPPLIED.JOB_ID = Temp_Staff_Reports.JOB_ID) ON PJT_PRODUCT.EBIS_ID = PJT_PRODUCT_SUPPLIED.EBIS_ID SET Temp_Staff_Reports.ITEMS_PROVIDED = [ITEMS_PROVIDED] & " (" & [PRODUCT_CODE] & ") " & [PRODUCT_DESCRIPTION] & ", ";

Im my case I'm concatening the ITEMS_PROVIDED column in the Temp_Staff_reports table. The other 2 tables involved are PJT_PRODUCT_SUPPLIED and PJT_PRODUCT.

I really appreciate the suggestion. I'm not completely sure how to make this work in my situtation. Below you can see my current SQL Statement:

Code:
----------------------------------------------------------
SELECT tblPart.PART_NUMBER, tblPartModel. PRODUCT_TYPEFROM tblPartModel INNER JOIN tblPart ON tblPartModel.PART_SEQ_ID = tblPart.PART_SEQ_ID;GROUP BY tblPart.PART_NUMBER, tblPartModel.PRODUCT_TYPE;
----------------------------------------------------------

As you can see i'm joining these two tables by the PART_SEQ_ID and then using group by to get only the unique records.

I tried creating a Temp_Parts table to concatenate the PRODUCT_TYPE but did not have any success. The SQL for this is below:

Code:
----------------------------------------------------------
UPDATE Temp_Parts, tblPartModel INNER JOIN tblPart ON tblPartModel.PART_SEQ_ID = tblPart.PART_SEQ_ID SET Temp_Parts.PartNumber_ProductType = " ("[PART_NUMBER] & ") " & [PRODUCT_TYPE] & ", ";
----------------------------------------------------------
When I ran this query it did not return any results.

Any suggestions? I really appreciate the help.

Thanks,
Aaron

This is not a "normal" type of thing to do, and I'm thinking that you want to do this for some reason, or final outcome. If you would explain what the final result is that you are looking for, there might be a simple answer to get to the final result that is easier than doing what you have requested within a query, or set of queries.

I realize that this is not the "normal" kind of thing to do. I didn't think it was going to be boiling the ocean but now I'm beginning to wonder. The point of this...is that I have many parts (part numbers) which only work on certain product (product types). Some parts work on all products (6 in this case) but some may only be applicable to one or two. The hope was that if a part number is chosen then without having to do much work, I could see the product(s) associated with that part number. Hopefully this should make more sense. My appologies for not making that clear. If anyone can think of a better way to make this work it would be greatly appreciated.

Thanks,
Aaron

If you are going to use this on a form, you could create a list box that would use the product code to select which parts have the selected product code as a match. If you are dealing with a report, you might be able to use the detail section of the report to build the list of all parts that can be used on the selected (or printing) product.

On the example in your reply here is the SQL (this works);

UPDATE tblPartModel INNER JOIN tblPart ON tblPartModel.PART_NUMBER = tblPart.Part_Number SET tblPart.PartNumber_ProductType = [PartNumber_ProductType] & ", " & [Product_Type];

Notice where the inner join is. It needs a little work to get rid of the first ", ", i.e. use on IIF statement.