Concatenate multiple rows in one field in Access? [duplicate]

Question

Possible Duplicate:
Ms Access Query: Concatenating Rows through a query

I have a table that has many columns, but two of interest: Order Number and Product Type. Currently the table has multiple Product Types per Order. If the customer ordered phone service, TV service and Internet service, then there would be three records - one for each service but all having the same order number. I want to create a reference table to store a concatenated string with all of the services the customer ordered. This way I can summarize my data using this more logical method. I'm using a standard Access 2010 database.

**Current table:**

Order Number | Product Types

100001 | TV

100001 | Phone

100001 | Internet

100002 | Phone

100003 | TV

100003 | Internet

Desired reference table

100001 | TV/Phone/Internet

100002 | Phone

100003 | TV/Internet

Answer 1

Allen Browne provides a function which you may find useful for this: Concatenate values from related records. Save that function's code in a standard module.

SELECT DISTINCT
    [Order Number],
    ConcatRelated("[Product Types]",
        "YourTable",
        "[Order Number] = " & [Order Number],
        "[Product Types]",
        "/"
        ) AS All_Product_Types
FROM YourTable;

I tested that query in Access 2007 with your sample data saved in a table named "YourTable". It returned the results you asked for. However, this only works from within an Access session. If you wanted to run this query from outside Access (like from ASP), user-defined functions are not available, so you would get an error about ConcatRelated() not recognized.

So you can use a query to retrieve the concatenated values whenever you need them. However if you store those concatenated values, they can quickly be out of sync with changes to the base table's data.

Answer 2

If I understand the question, you're asking how to get the order numbers of just those orders who have TV, AND phone, AND internet. If you're just interested in those order numbers you could run a query like:

SELECT Distinct Table1.OrderNumber
FROM (Select OrderNumber from Table1 where [product types]= "Internet")  AS i
    INNER JOIN ((Select OrderNumber from Table1 where [product types]="Phone")  AS p 
    INNER JOIN ((Select OrderNumber from Table1 Where [product types]= "TV")  AS tv 
    INNER JOIN Table1 ON tv.OrderNumber = Table1.OrderNumber) ON p.OrderNumber = Table1.OrderNumber) ON i.OrderNumber = Table1.OrderNumber;